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(): 
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;
EXAMPLE: This function throws the exception in a trigger, making it behave like a table constraint (not really usefull but interesting nonetheless): 
CREATE TABLE t4(x int);

CREATE OR REPLACE FUNCTION f_ex4 ()
 RETURNS TRIGGER AS
$body$
BEGIN
    IF NEW.X>10 THEN
        RAISE EXCEPTION 'transaction interupted, the value is greater than 10, give me another value';
    END IF;

    RETURN NEW;
END;
$body$
LANGUAGE plpgsql;

DROP TRIGGER IF EXISTS ex_trigger ON t4;
CREATE TRIGGER ex_trigger BEFORE INSERT ON t4 FOR EACH ROW EXECUTE PROCEDURE f_ex4();
This query works just fine: 
robert=# INSERT INTO t4 VALUES(1);
INSERT 0 1
This one won't work: 
robert=# INSERT INTO t4 VALUES(11);
ERROR:  transaction interupted, the value is greater than 10, give me another value
EXAMPLE: This function demonstrates nested exceptions 
CREATE OR REPLACE FUNCTION f_ex5 ()
RETURNS VOID AS
$body$
BEGIN
    CREATE TABLE t5(x int unique);
EXCEPTION
    WHEN OTHERS THEN
        RAISE NOTICE 'sorry, but table t5 has already been created';

        BEGIN
            INSERT INTO t5 VALUES(1);
        EXCEPTION
            WHEN OTHERS THEN
                RAISE NOTICE 'sorry, but the value 1 has already been inserted';
        END;
END;
$body$
LANGUAGE PLPGSQL;
Executing f_ex5() the first time creates table t5: 
robert=# select f_ex5();
NOTICE:  CREATE TABLE / UNIQUE will create implicit index "t5_x_key" for table "t5"
CONTEXT:  SQL statement "CREATE TABLE t5(x int unique)"
PL/pgSQL function "f_ex5" line 2 at SQL statement
Executing f_ex5() the second time raises a NOTICE and inserts one record into table t5: 
robert=# select f_ex5();
NOTICE:  sorry, but table t5 has already been created
Executing f_ex5() a third time raises the same NOTICE as before and another NOTICE advising the inability of adding another value to the table: 
robert=# select f_ex5();
NOTICE:  sorry, but table t5 has already been created
NOTICE:  sorry, but the value 1 has already been inserted
EXAMPLE: RAISE EXCEPTION presents a dilemma. It always generates an SQLSTATE code of P0001. As well, the SQLERRM is not standard because it depends upon the query. This example demonstrates one way you can process a raised EXCEPTION. 
CREATE OR REPLACE FUNCTION f_ex6 (
    INOUT my_str text
) AS
$body$
DECLARE
BEGIN
-- raising an exception always generates SQLSTATE=P0001
    RAISE EXCEPTION '%',my_str;
EXCEPTION
    WHEN OTHERS THEN
        IF  substr(my_str,0,4)='001' THEN
            my_str = 'alternate processing for raised EXCEPTION with a custom error code of 001';
        ELSIF
            substr(my_str,0,4)='002' THEN
            my_str = 'alternate processing for raised EXCEPTION with a custom error code of 002';
        ELSE
            my_str='ERROR CODE: ' || SQLSTATE || ', RETURNED CONDITION (MESSAGE): ' || SQLERRM;
        END IF;
END;
$body$
LANGUAGE plpgsql;
The following queries executes different error messages: 
robert=# select * from f_ex6('001: my first theoretical error message');
                                  my_str
---------------------------------------------------------------------------
 alternate processing for raised EXCEPTION with a custom error code of 001
(1 row)

robert=# select * from f_ex6('002: my second theoretical error message');
                                  my_str
---------------------------------------------------------------------------
 alternate processing for raised EXCEPTION with a custom error code of 002
(1 row)

robert=# select * from f_ex6('untrapped error message');
                                  my_str
--------------------------------------------------------------------------
 ERROR CODE: P0001, RETURNED CONDITION (MESSAGE): untrapped error message
(1 row)
Integrating Postgres Exception Handling With a PHP Server Page
The following PHP examples demonstrates postgres exception handling afer it has been propagated to the webserver. Note: The two scripts assume the following:
EXAMPLE: This simple PHP script returns five different messages to the client's web browser using messages generated by a different function. 
<?php
$conn = pg_pconnect("host=/tmp user=www password=123 dbname=robert");
if (!$conn) {
   echo "connection attempt failed";
   exit;
 }

$query = "SELECT * FROM f_ex1('select * from xxx')";

$result = pg_query($conn, $query);
if (!$result) {
    print "<hr>MSG 2: ".pg_last_notice($conn)."<hr>";
    print "<hr>MSG 3: ".pg_last_error($conn)."<hr>";
    print "MSG 4: ".pg_result_error($conn);
    print "<hr>MSG 5: An exception from postgres has been thrown<hr>";
    exit;
}

$row = pg_fetch_row($result);
if (!$result) exit;
print $row[0];
?>
The first error message is generated by invoking the pg_query function. Prepending the '@' character will remove this message.
The second error message (MSG 2), which is generated by the function pg_last_notice(), returns the most recently generated NOTICE which may. Function f_ex1() does RAISE NOTICE and a message is returned with this function call.
The third error message (MSG 3) is generated by pg_last_error() and returns the exact exception error message that was generated by the query.
The fourth error message (MSG 4), which is generated by the function pg_result_error(), is different from pg_last_error in that it may not return the most recently generated error message.
The fifth and last error message is of course an ordinary print statement saying that an error, relating to the execution of the query, has occurred.
EXAMPLE: this example generates all the query results on a single HTML page where user-defined functions throw exceptions upon failure. This example uses the previously created table "t1" and the postgres function f_ex1(). Notice that processing continues after exceptions have been thrown. 
<?php

function exec_qry ($c,$q) {
    // the '@' symbol supresses the PHP default error messaging
    $result = @pg_query($c,$q);
    if (!$result) {
        throw new Exception(pg_last_error($c));
    } else return $result;
}

function get_qry_row($r) {
    $row = pg_fetch_row($r);
    if (!$r) {
        throw new Exception("can't get a row for the query");
    } else return $row[0];
}

$conn = pg_pconnect("host=/tmp user=postgres password=123 dbname=postgres");
if (!$conn) {
   echo "connection attempt failed";
   exit;
 }

$query = array
    (   "select now()",
        "select ''hello world'' ",
        "select * from nonexistant_table",
        "select ''hello world again'' ",
        // this next query fails because t1 has a trigger limiting the max value at 10
        "insert into t1 values(123)", 
        "select ''processing complete'' "
    );

foreach ($query as $q) {
    try { 
        print "<hr>".get_qry_row(exec_qry($conn,"select f_ex1('$q')"))."<hr>";
    } catch (Exception $e) {
        print $e->getMessage();
    }
}
print "<br><br>.... <b>place post error trapping processing HERE!</b>";
?>
Conclusion
Postgres exception handling is a little known feature. With it you can create the right business rules in the right place. Now that you know about it you can use to make your system fly. With it you can better separate the logic of working with the data and that of formating it on the webpage. You'll find your business rules easy to implement and easier still to maintain.
Postgres Chapter References