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