HomeAbout

Stored Procedures

Stored Procedures

Stored procedures are a way to encapsulate business logic in the database. They can be used to perform complex operations that involve multiple steps, such as inserting data into multiple tables, validating input, and generating output.

Case against stored procedures

  • tangling business logic with data access is bad.

Case for stored procedures

  • When using ORM, same would be a lot of writes.

Illustration of user registration:

  • The user record is written
  • A log entry is created
  • A note is added describing when/how/where
  • A role is assigned
  • A mailer is prepared and the mailer record attached to the user (a Welcome! email)
  • A validation token is created and two separate logins (auth token and local user/password)
CREATE OR REPLACE FUNCTION register(login varchar(50), email varchar(50), password varchar(50), ip inet)
returns TABLE (
  new_id bigint,
  message varchar(255),
  email varchar(255),
  email_validation_token varchar(36)
)
AS
$$
DECLARE
  new_id bigint;
  message varchar(255);
  hashedpw varchar(255);
  validation_token varchar(36);
BEGIN

  --hash the password using pgcrypto
  SELECT crypt(password, gen_salt('bf', 10)) into hashedpw;

  --create a random string for the
  select substring(md5(random()::text),0, 36) into validation_token;

  --create the member. Email has a unique constraint so this will
  --throw. You could wrap this in an IF if you like too
  insert into members(email, created_at, email_validation_token)
  VALUES(email, now(), validation_token) returning id into new_id;

  --set the return message
  select 'Successfully registered' into message;

  --add login bits to logins
  insert into logins(member_id, provider, provider_key, provider_token)
  values(new_id, 'local',email,hashedpw);

  --add auth token to logins
  insert into logins(member_id, provider, provider_key, provider_token)
  values(new_id, 'token',null,validation_token);

  -- add them to the members role which is 99
  insert into members_roles(member_id, role_id)
  VALUES(new_id, 99);

  --add log entry
  insert into logs(subject,entry,member_id, ip, created_at)
  values('registration','Added to system, set role to User',new_id, ip, now());

  --return out what happened here with relevant data
  return query
  select new_id, message, new_email, success, validation_token;

END
$$ LANGUAGE plpgsql;

Source: https://bigmachine.io/postgres/its-time-to-get-over-that-stored-procedure-aversion-you-have

AboutContact