A long time ago in a place far far away, at least from my house, object oriented programming was invented. Included in its paradigm is EXCEPTION HANDLING which guides how one should think and process undesirable/unexpected events/inputs. The idea being that any code processing an undesired event should be easy to implement and understood by others.
The objective of this article is to show how you can leverage Postgres exception handling as the backend of your web-enabled application. For the purposes of this article, it's understood that PHP 5 on Apache 2 and Postgres 8.2 are used.
About Exception Handling
For those who may not be familiar with exceptions; failed function calls normally return an error condition that eventually propagates back to the invoking process, i.e. the web server, which must provide a response to that failure. The beauty of exception handling is that it can intercept the error condition and provide alternate instructions in an elegant manner without the client's web browser getting that ugly error message that includes a cryptic error code and requesting that they tell somebody real quick.
About Postgres Exception Handling
Postgres exceptions are quite different from PHP exceptions; whereas PHP 5 uses the traditional "Try And Catch" format, Postgres exceptions can only be defined, executed and processed inside the body of the function that triggered the exception.
Notice below how an additional EXCEPTION block is added to the standard plpgsql function:
[ <<label>> ]
WHEN condition [ OR condition ... ] THEN
[ WHEN condition [ OR condition ... ] THEN
All statements between the words EXCEPTION and END make up the exception instruction block. The 'condition' is one of many predefined text string constants. The hander_statements consist of user-defined plpgsql code that is invoked when an error condition occurs. The EXCEPTION block will not be executed until an error condition occurs in the 'statements' portion of the function.
Two environment variables become available during the execution of an exception i.e. SQLSTATE and SQLERRM. SQLSTATE is an SQL compliant unique 5 character code identifying the raised exception; the first two digits corresponds to the error's class while the three remaining characters define the specific error within that class. The SQLERRM variable, which is used as a test condition, returns a constant string detailing the error message. Refer to Appendix A, Table A-1, PostgreSQL Error Codes, for the entire list of supported SQLSTATE and SQLERRM.
About Raising Messages
Raising messages not only triggers exceptions but, during the execution of an exception, they are an excellent way to record and report details about the error condition itself. Here's the general form of the RAISE statement where the 'expression' is a variable expressed as a percentage symbol in the string, 'format':
RAISE level 'format' [, expression [, ...]];
RAISE NOTICE 'this is my message: %', msg;
The Postgres parameter, client_min_messages, is set in postgresql.conf. It controls the client (web server) message levels and includes: DEBUG5, DEBUG4, DEBUG3, DEBUG2, DEBUG1, LOG, NOTICE, WARNING, ERROR, FATAL, and PANIC. For example, the default value for client_min_messages of NOTICE incompasses the messages for WARNING, ERROR, FATAL AND PANIC. Resetting the parameter for example to FATAL means no messages will be sent the client unless they are of a rank FATAL and PANIC. Setting the parameter at PANIC will interdict all messages to the client, which may be an issue if your PHP depends on any error notification from the server.
EXAMPLE: The following example demonstrates 3 levels of client messaging. Create a function that raises messages at different levels:
CREATE OR REPLACE FUNCTION f_msg (
IN msg text
) RETURNS VOID AS
RAISE NOTICE '%, via NOTICE',msg;
RAISE LOG '%, via LOG',msg;
RAISE EXCEPTION '%, via EXCEPTION',msg;
The server is started, level NOTICE and EXCEPTION messages are sent but not the level LOG.
The client executes a query against the function with the parameter client_min_messages=log.
robert@laptop:~/tmp$ psql -t -c "select f_msg('this message is sent to the client');"
NOTICE: this message is sent to the client, via NOTICE
LOG: this message is sent to the client, via LOG
ERROR: this message is sent to the client, via EXCEPTION
Come back next week when we present lots more POSTGRES exception examples, and continue with our article on Postgres exception handling.