Postgresql – Raise Multiple Exceptions Postgres

database-designpostgresqlstored-procedures

I am using Postgres 9.5, and I am trying to create exception handling in a stored procedure/function.

I can't quite figure out how to raise the error should any of the 'INSERT' statements fail. If there is a failure, I want to 'ROLLBACK' the transaction so nothing is inserted, return the user friendly error to the user, and log the sql server error message somewhere. Is that possible to do inside a stored procedure?

Here is what I have so far, but it does not work:

CREATE FUNCTION CREATE_USER_FUNC (
role VARCHAR,
firstname VARCHAR,
lastname VARCHAR,
email VARCHAR,
username VARCHAR,
is_enabled BOOLEAN,
city VARCHAR,
state CHAR,
zipcode INTEGER,
password VARCHAR,
team VARCHAR,
OUT new_user_id INTEGER
) 
RETURNS integer AS $$
BEGIN
INSERT INTO users (firstname, lastname, email, username, is_user_enabled, city, us_state, zipcode, user_password) VALUES($2, $3 ,$4, $5, $6, $7, $8, $9, $10) RETURNING user_id INTO new_user_id;
    RAISE EXCEPTION SQLSTATE '23502' USING MESSAGE = 'Values can not be null';
    RAISE EXCEPTION SQLSTATE '23514' USING MESSAGE = 'Password not the proper length';

INSERT INTO role_members (user_id, role_name) VALUES(new_user_id, $1);
    RAISE EXCEPTION SQLSTATE '23502' USING MESSAGE = 'Values not present in the table';

INSERT INTO team_members (user_id, dealership) VALUES(new_user_id, $11);
    RAISE EXCEPTION SQLSTATE '23502' USING MESSAGE = 'Values not present in the table';
EXCEPTION WHEN OTHERS THEN
    RAISE EXCEPTION
END;
$$ LANGUAGE plpgsql

Best Answer

If there is a failure, I want to 'ROLLBACK' the transaction so nothing is inserted

That happens automatically if any exception is raised inside a function (by you or by Postgres). A function is always atomic and can only be run within a transaction context - which is the main distinction from real stored procedures (currently implemented in Postgres). There are a few exceptions: things that cannot be rolled back, like incremented serials or dblink calls.

... return the user friendly error to the user

You might just go with what Postgres returns by default. If you insist on custom error messages, be careful not to catch the wrong exception. You want to return two different messages for the same error code 23502. You would need separate blocks to achieve this in plpgsql.

and log the sql server error message somewhere

If you are happy with the standard log output in the database log, that's plain and simple (and highly configurable). The manual:

Whether messages of a particular priority are reported to the client, written to the server log, or both is controlled by the log_min_messages and client_min_messages configuration variables. See Chapter 18 for more information.

If you think of writing to a table, that would be the tricky part since everything is rolled back in case of an exception. That would include everything written to a log table. You'd have to fake an autonomous transaction to persist the log entries even though the transaction is rolled back. Detailed instructions:

To ensure passwords with proper length, simply create a CHECK constraint with a descriptive name. Like password_needs_8_char_or_more. Then you don't have to do anything extra. Postgres will raise an exception including the name of the constraint in case of a check_violation (error code 23514).

I would make it a simple SQL function with data-modifying CTEs. Fastest, safest, simplest - and go with the built-in error messages and built-in error logging:

CREATE OR REPLACE FUNCTION create_user_func(
   role text, firstname text, lastname text, email text, username text
 , is_enabled boolean, city text, state text  -- char is a dubious data tpye
 , zipcode int, password text, team text
 , OUT new_user_id int
   ) RETURNS integer AS
$func$
   WITH i1 AS (
      INSERT INTO users (firstname, lastname, email, username, is_user_enabled
                       , city, us_state, zipcode, user_password)
      VALUES($2, $3 ,$4, $5, $6, $7, $8, $9, $10)
      RETURNING user_id
      )
   , i2 AS (
      INSERT INTO role_members (user_id, role_name)
      SELECT i1.user_id, $1
      )
   INSERT INTO team_members (user_id, dealership)
   SELECT i1.user_id, $11;
$func$ LANGUAGE sql;

And you probably do not want to use the data type char. Just use text. Or varchar(1) if you need the length restriction. Or possibly the special type "char" (with double quotes) if it's just a single ASCII character:

Related Question