40.8. Errors and Messages

Use theRAISE 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 ;

Thelevel option specifies the error severity. Allowed levels areDEBUG,LOG,INFO,NOTICE,WARNING, andEXCEPTION, withEXCEPTION 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 thelog_min_messages andclient_min_messages configuration variables. SeeChapter 18 for more information.

Afterlevel if any, you can write aformat (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 ofv_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 writingUSING followed byoption =expression items. Eachexpression can be any string-valued expression. The allowedoption key words are:

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 secondRAISE 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 writeRAISE USING orRAISElevel USING and put everything else into theUSING list.

The last variant ofRAISE has no parameters at all. This form can only be used inside aBEGIN block'sEXCEPTION clause; it causes the error currently being handled to be re-thrown.

If no condition name nor SQLSTATE is specified in aRAISE EXCEPTION command, the default is to useRAISE_EXCEPTION (P0001). If no message text is specified, the default is to use the condition name or SQLSTATE as message text.