0

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
  • 1
    "Does not work". How exactly does it "not work" ? In what sense do you want to raise "multiple exceptions"? – Craig Ringer May 24 '16 at 2:39
  • I would like to raise an exception if any of the three insert statements failed, cease the stored procedure from functioning, rollback anything that worked, and return the error message from the insert that failed. If there was one error (not null constraint), then return that error message. If it was a different error( length constraint), return the appropriate error. If it was neither, then return a different error. Based on Erwin's answer, it seems that there is a better way than what I proposed above. – unseen_damage May 24 '16 at 13:33
2

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:

  • When I didn't have any exceptions in the stored procedure, the transaction still went through. For example, if there was a null value in the first insert, the row was not inserted into users, but a new row was created and that ID was passed onto the next two Inserts and they continued to work.Is there a way to do a try catch, or will the "WITH...AS" statements handle the errors? I don't see how the example above will catch errors is my concern. – unseen_damage May 24 '16 at 13:29
  • 1
    @unseen_damage: if you don't want NULL values in the columns declare them as NOT NULL and the insert will throw an exception. – a_horse_with_no_name Nov 4 '16 at 13:29

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.