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:
- user account is the superuser postgres
- the host is the UNIX SOCKET DOMAIN /tmp
- the database is postgres
- the password is 123
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
- Chapter 17 : Server Configuration
- Chapter 37.7.5 : Trapping Errors
- Chapter 37.9 : Errors And Messages
- Appendix A : PostgreSQL Error Codes
