Scaling Your Database with PL/Proxy - PostgreSQL Server Programming - page 253

i_email);
status = 200;
message = 'OK';
EXCEPTION WHEN unique_violation THEN
status = 500;
message = 'USER EXISTS';
END;
$$ LANGUAGE plpgsql SECURITY DEFINER;
This method just fails when the user is already defined. A more "real-life" function
would propose a list of available usernames in this case.
The method for login returns status 500 for failure and 200 or 201 for success. 201
means that there are unread messages for this user:
CREATE OR REPLACE FUNCTION login(
IN i_username text, IN i_pwdhash text,
OUT status int, OUT message text )
AS $$
BEGIN
PERFORM 1 FROM user_info
WHERE ( username, pwdhash) = ( i_username,
i_pwdhash);
IF NOT FOUND THEN
status = 500;
message = 'NOT FOUND';
END IF;
PERFORM 1 FROM message
WHERE to_user = i_username
AND read_at IS NULL;
IF FOUND THEN
status = 201;
message = 'OK. NEW MESSAGES';
ELSE
status = 200;
message = 'OK. NO MESSAGES';
END IF;