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.

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.

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.