Use the RAISE statement to report messages and raise errors.
RAISE [level
] 'format
' [,expression
[, ... ]] [ USINGoption
=expression
[, ... ] ]; RAISE [level
]condition_name
[ USINGoption
=expression
[, ... ] ]; RAISE [level
] SQLSTATE 'sqlstate
' [ USINGoption
=expression
[, ... ] ]; RAISE [level
] USINGoption
=expression
[, ... ]; RAISE ;
The level
option specifies
the error severity. Allowed levels are DEBUG
,
LOG
, INFO
,
NOTICE
, WARNING
,
and EXCEPTION
, with EXCEPTION
being the default.
EXCEPTION
raises an error (which normally aborts the
current transaction); the other levels only generate messages of different
priority levels.
Whether messages of a particular priority are reported to the client,
written to the server log, or both is controlled by the
log_min_messages and
client_min_messages configuration
variables. See Chapter 18, Server Configuration for more
information.
After level
if any,
you can write a format
(which must be a simple string literal, not an expression). The
format string specifies the error message text to be reported.
The format string can be followed
by optional argument expressions to be inserted into the message.
Inside the format string, %
is replaced by the
string representation of the next optional argument's value. Write
%%
to emit a literal %
.
In this example, the value of v_job_id
will replace the
%
in the string:
RAISE NOTICE 'Calling cs_create_job(%)', v_job_id;
You can attach additional information to the error report by writing
USING
followed by option
= expression
items. Each
expression
can be any
string-valued expression. The allowed option
key words are:
MESSAGE
Sets the error message text. This option can't be used in the
form of RAISE that includes a format string
before USING
.
DETAIL
Supplies an error detail message.
HINT
Supplies a hint message.
ERRCODE
Specifies the error code (SQLSTATE) to report, either by condition name, as shown in Appendix A, PostgreSQL™ Error Codes, or directly as a five-character SQLSTATE code.
COLUMN
, CONSTRAINT
, DATATYPE
, TABLE
, SCHEMA
Supplies the name of a related object.
This example will abort the transaction with the given error message and hint:
RAISE EXCEPTION 'Nonexistent ID --> %', user_id USING HINT = 'Please check your user ID';
These two examples show equivalent ways of setting the SQLSTATE:
RAISE 'Duplicate user ID: %', user_id USING ERRCODE = 'unique_violation'; RAISE 'Duplicate user ID: %', user_id USING ERRCODE = '23505';
There is a second RAISE syntax in which the main argument is the condition name or SQLSTATE to be reported, for example:
RAISE division_by_zero; RAISE SQLSTATE '22012';
In this syntax, USING
can be used to supply a custom
error message, detail, or hint. Another way to do the earlier
example is
RAISE unique_violation USING MESSAGE = 'Duplicate user ID: ' || user_id;
Still another variant is to write RAISE USING
or RAISE
and put
everything else into the level
USINGUSING
list.
The last variant of RAISE has no parameters at all.
This form can only be used inside a BEGIN
block's
EXCEPTION
clause;
it causes the error currently being handled to be re-thrown.
Before PostgreSQL™ 9.1, RAISE without
parameters was interpreted as re-throwing the error from the block
containing the active exception handler. Thus an EXCEPTION
clause nested within that handler could not catch it, even if the
RAISE was within the nested EXCEPTION
clause's
block. This was deemed surprising as well as being incompatible with
Oracle's PL/SQL.
If no condition name nor SQLSTATE is specified in a
RAISE EXCEPTION command, the default is to use
RAISE_EXCEPTION
(P0001
). If no message
text is specified, the default is to use the condition name or
SQLSTATE as message text.
When specifying an error code by SQLSTATE code, you are not
limited to the predefined error codes, but can select any
error code consisting of five digits and/or upper-case ASCII
letters, other than 00000
. It is recommended that
you avoid throwing error codes that end in three zeroes, because
these are category codes and can only be trapped by trapping
the whole category.