Last week we left off with an example where notice, exception and log levels are sent to the client. This week we present more POSTGRES exception examples, and continue with our article on Postgres exception handling.
It’s important to note that, even if not reported i.e. client_min_messages=panic, raising an EXCEPTION still aborts the functions’ transaction.
pg_ctl -D ~/cluster_phpbuilder/ -o '-c client_min_messages=panic' restart
Notice that no messaging of any kind is sent to the client.
psql -t -c "select f_msg('this message is sent to the client');" [email protected]:~/tmp$ psql -t -c "select f_msg('this message is sent to the client');" [email protected]:~/tmp$
Postgres Exception Examples
The following examples demonstrate how you can use exceptions.
EXAMPLE: this function is used to identify the trapped eror code and constant. The special condition OTHERS matches all error conditions that are not trapped.
CREATE OR REPLACE FUNCTION f_ex1 ( INOUT my_query text ) AS $body$ BEGIN EXECUTE my_query INTO my_query; EXCEPTION WHEN OTHERS THEN RAISE NOTICE 'an EXCEPTION is about to be raised'; RAISE EXCEPTION 'NUM:%, DETAILS:%', SQLSTATE, SQLERRM; END; $body$ LANGUAGE PLPGSQL;
EXAMPLE: Function f_ex2() traps four distinct conditions. Note that the SQLERRM messages were derived by using f_ex1():
- a creation attempt of an existing table t1
- a creation attempt of an existing table t2
- a creation attempt of any existing table
- trap anything that the above traps miss
CREATE OR REPLACE FUNCTION f_ex2 ( in my_query text ) RETURNS VOID AS $body$ BEGIN EXECUTE my_query; EXCEPTION WHEN duplicate_table THEN IF SQLERRM ='relation "t1" already exists' THEN RAISE NOTICE 'trapped table creation for t1'; RAISE NOTICE 'function exits gracefully'; ELSIF SQLERRM = 'relation "t2" already exists' THEN RAISE NOTICE 'trapped table creation for t2'; RAISE NOTICE 'function exits gracefully'; ELSE RAISE NOTICE 'another table is being affected: %', SQLERRM; END IF; WHEN unique_violation THEN RAISE NOTICE 'continuing the trap'; RAISE NOTICE 'trapped a unique value violation attempt'; WHEN OTHERS THEN RAISE NOTICE 'last trap'; RAISE EXCEPTION 'NUM:%, DETAILS:%', SQLSTATE, SQLERRM; RAISE NOTICE 'this commented is not seen because an EXECEPTION has been raised'; END; $body$ LANGUAGE PLPGSQL;
Raising a NOTICE still doesn’t interfere with the function’s operation but raising an EXCEPTION will. Notice that the last RAISE NOTICE isn’t invoked because the function’s transaction will have been aborted in the previous line by RAISE EXCEPTION.
EXAMPLE: The error messages NO_DATA_FOUND and TOO_MANY_ROWS are returned when the query either when it doesn’t contain any data or there is more than one row in table t1.
CREATE OR REPLACE FUNCTION f_ex3 () RETURNS VOID AS $body$ DECLARE myrec record; BEGIN SELECT * INTO STRICT myrec FROM t1; RAISE NOTICE 'query completed'; EXCEPTION WHEN NO_DATA_FOUND THEN RAISE EXCEPTION 'no records found'; WHEN TOO_MANY_ROWS THEN RAISE EXCEPTION 'too many rows'; END; $body$ LANGUAGE PLPGSQL;