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>> ]
[ DECLARE
declarations ]
BEGIN
statements
EXCEPTION
WHEN condition [ OR condition ... ] THEN
handler_statements
[ WHEN condition [ OR condition ... ] THEN
handler_statements
... ]
END;
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;
