A PLPGSQL Register Function

register.sql

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;