- publishing free software manuals
PostgreSQL Reference Manual - Volume 2 - Programming Guide
by The PostgreSQL Global Development Group
Paperback (6"x9"), 408 pages
ISBN 0954612035
RRP £19.95 ($34.95)

Sales of this book support the PostgreSQL project! Get a printed copy>>>

3.11.1 Setting Callbacks

One simple method to catch errors and warnings is to set a specific action to be executed whenever a particular condition occurs. In general:

EXEC SQL WHENEVER condition action;

condition can be one of the following:

SQLERROR
The specified action is called whenever an error occurs during the execution of an SQL statement.
SQLWARNING
The specified action is called whenever a warning occurs during the execution of an SQL statement.
NOT FOUND
The specified action is called whenever an SQL statement retrieves or affects zero rows. (This condition is not an error, but you might be interested in handling it specially.)

action can be one of the following:

CONTINUE
This effectively means that the condition is ignored. This is the default.
GOTO label
GO TO label
Jump to the specified label (using a C goto statement).
SQLPRINT
Print a message to standard error. This is useful for simple programs or during prototyping. The details of the message cannot be configured.
STOP
Call exit(1), which will terminate the program.
DO BREAK
Execute the C statement break. This should only be used in loops or switch statements.
CALL name (args)
DO name (args)
Call the specified C functions with the specified arguments.

The SQL standard only provides for the actions CONTINUE and GOTO (and GO TO).

Here is an example that you might want to use in a simple program. It prints a simple message when a warning occurs and aborts the program when an error happens.

EXEC SQL WHENEVER SQLWARNING SQLPRINT;
EXEC SQL WHENEVER SQLERROR STOP;

The statement EXEC SQL WHENEVER is a directive of the SQL preprocessor, not a C statement. The error or warning actions that it sets apply to all embedded SQL statements that appear below the point where the handler is set, unless a different action was set for the same condition between the first EXEC SQL WHENEVER and the SQL statement causing the condition, regardless of the flow of control in the C program. So neither of the two following C program excerpts will have the desired effect.

/*WRONG
 */
int main(int argc, char *argv[])
{
    ...
    if (verbose) {
        EXEC SQL WHENEVER SQLWARNING SQLPRINT;
    }
    ...
    EXEC SQL SELECT ...;
    ...
}
/*WRONG
 */
int main(int argc, char *argv[])
{
    ...
    set_error_handler();
    ...
    EXEC SQL SELECT ...;
    ...
}

static void set_error_handler(void)
{
    EXEC SQL WHENEVER SQLERROR STOP;
}
ISBN 0954612035PostgreSQL Reference Manual - Volume 2 - Programming GuideSee the print edition