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.
Case 3:
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');"
robert@laptop:~/tmp$ psql -t -c "select f_msg('this message is sent to the client');"
robert@laptop:~/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;
