PHPBuilder - A Primer on Postgres Exception Handling for the PHP Developer, Part 2



RSS Twitter
Articles Databases

A Primer on Postgres Exception Handling for the PHP Developer, Part 2

by: Robert Bernier
|
November 15, 2007

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;

1
|
2
Next Page »

Comment and Contribute

Your comment has been submitted and is pending approval.

Author:
Robert Bernier

Comment:



Comment:

(Maximum characters: 1200). You have characters left.