RAISE statements raise errors and exceptions during a PL/pgSQL function's operation.
A RAISE statement sends specified information to the PostgreSQL
elog mechanism (the standard PostgreSQL error logging utility, which typically logs data
either to
/var/log/messages
, or to
$PGDATA/serverlog
, as well as displaying to
stderr
).
A RAISE statement is also given the level of error it should raise, and the string
it should send to PostgreSQL. Additionally, you can list variables and expressions whose values you wish to have placed
into the string. Use percent signs (%) to mark the locations in the string at which you
want those values inserted. The syntax of the RAISE statement is as follows:
RAISE
level
''
message string
'' [,
identifier
[...] ];
Table 11-1 lists the three possible values for the RAISE
statement's level and their meanings.
Table 11-1. Possible level values
| Value |
Explanation |
|
DEBUG
|
DEBUG level statements send the specified text as a DEBUG: message to the PostgreSQL log and the client program if the client is connected to a database cluster running in debug mode. DEBUG level RAISE statements will be ignored by a database running in production mode.
|
|
NOTICE
|
NOTICE level statements send the specified text as a NOTICE: message to the PostgreSQL log and the client program in any PostgreSQL operation mode.
|
|
EXCEPTION
|
EXCEPTION level statements send the specified text as an ERROR: message to the client program and the PostgreSQL database log. The EXCEPTION level also causes the current transaction to be aborted.
|
In Example 11-48, the first RAISE statement raises a debug
level message. The second and third RAISE statements send a notice to the user. Notice
the use of the percent-sign (%) in the third RAISE statement to mark the location in the
string at which the value of an integer is to be inserted. Finally, the fourth RAISE
statement displays an error and throws an exception, causing the function to end and the transaction to be aborted.
Example 11-48. Using the RAISE statement
CREATE FUNCTION raise_test () RETURNS integer AS '
DECLARE
-- Declare an integer variable for testing.
an_integer INTEGER = 1;
BEGIN
-- Raise a debug level message.
RAISE DEBUG ''The raise_test() function began.'';
an_integer = an_integer + 1;
-- Raise a notice stating that the an_integer variable was changed,
-- then raise another notice stating its new value.
RAISE NOTICE ''Variable an_integer was changed.'';
RAISE NOTICE ''Variable an_integer's value is now %.'',an_integer;
-- Raise an exception.
RAISE EXCEPTION ''Variable % changed. Transaction aborted.'',an_integer;
RETURN 1;
END;
' LANGUAGE 'plpgsql';
Example 11-49 shows the results of the raise_test() function
when called from our booktown database. The DEBUG output
does not show, because our database is not running in debug mode.
Example 11-49. Results of the raise_test( ) function
booktown=#
SELECT raise_test();
NOTICE: Variable an_integer was changed.
NOTICE: Variable an_integer's value is now 2.
ERROR: Variable 2 changed. Aborting transaction.
