<!-- $PostgreSQL: pgsql/doc/src/sgml/plpgsql.sgml,v 1.129 2008/05/13 22:10:29 tgl Exp $ -->

not case-sensitive. Also, an error condition can be specified

by <literal>SQLSTATE</> code; for example these are equivalent:

<programlisting>

WHEN division_by_zero THEN ...

WHEN SQLSTATE '22012' THEN ...

</programlisting>

RAISE <optional> <replaceable class="parameter">level</replaceable> </optional> '<replaceable class="parameter">format</replaceable>' <optional>, <replaceable class="parameter">expression</replaceable> <optional>, ...</optional></optional> <optional> USING <replaceable class="parameter">option</replaceable> = <replaceable class="parameter">expression</replaceable> <optional>, ... </optional> </optional>;

RAISE <optional> <replaceable class="parameter">level</replaceable> </optional> <replaceable class="parameter">condition_name</> <optional> USING <replaceable class="parameter">option</replaceable> = <replaceable class="parameter">expression</replaceable> <optional>, ... </optional> </optional>;

RAISE <optional> <replaceable class="parameter">level</replaceable> </optional> SQLSTATE '<replaceable class="parameter">sqlstate</>' <optional> USING <replaceable class="parameter">option</replaceable> = <replaceable class="parameter">expression</replaceable> <optional>, ... </optional> </optional>;

RAISE <optional> <replaceable class="parameter">level</replaceable> </optional> USING <replaceable class="parameter">option</replaceable> = <replaceable class="parameter">expression</replaceable> <optional>, ... </optional>;

RAISE ;

The <replaceable class="parameter">level</replaceable> option specifies

the error severity. Allowed levels are <literal>DEBUG</literal>,

and <literal>EXCEPTION</literal>, with <literal>EXCEPTION</literal>

being the default.

After <replaceable class="parameter">level</replaceable> if any,

you can write a <replaceable class="parameter">format</replaceable>

(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.

string representation of the next optional argument's value. Write

You can attach additional information to the error report by writing

<literal>USING</> followed by <replaceable

class="parameter">option</replaceable> = <replaceable

class="parameter">expression</replaceable> items. The allowed

<replaceable class="parameter">option</replaceable> keywords are

<literal>MESSAGE</>, <literal>DETAIL</>, <literal>HINT</>, and

<literal>ERRCODE</>, while each <replaceable

class="parameter">expression</replaceable> can be any string-valued

expression.

<literal>MESSAGE</> sets the error message text (this option can't

be used in the form of <command>RAISE</> that includes a format

string before <literal>USING</>).

<literal>DETAIL</> supplies an error detail message, while

<literal>HINT</> supplies a hint message.

<literal>ERRCODE</> specifies the error code (SQLSTATE) to report,

either by condition name as shown in <xref linkend="errcodes-appendix">,

or directly as a five-character SQLSTATE code.

</para>

<para>

This example will abort the transaction with the given error message

and hint:

<programlisting>

RAISE EXCEPTION 'Nonexistent ID --> %', user_id USING HINT = 'Please check your user id';

</programlisting>

</para>

<para>

These two examples show equivalent ways of setting the SQLSTATE:

<programlisting>

RAISE 'Duplicate user ID: %', user_id USING ERRCODE = 'unique_violation';

RAISE 'Duplicate user ID: %', user_id USING ERRCODE = '23505';

</programlisting>

</para>

<para>

There is a second <command>RAISE</> syntax in which the main argument

is the condition name or SQLSTATE to be reported, for example:

<programlisting>

RAISE division_by_zero;

RAISE SQLSTATE '22012';

</programlisting>

In this syntax, <literal>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;

<para>

Still another variant is to write <literal>RAISE USING</> or <literal>RAISE

<replaceable class="parameter">level</replaceable> USING</> and put

everything else into the <literal>USING</> list.

</para>

<para>

The last variant of <command>RAISE</> has no parameters at all.

This form can only be used inside a <literal>BEGIN</> block's

<literal>EXCEPTION</> clause;

it causes the error currently being handled to be re-thrown to the

next enclosing block.

</para>

<para>

If no condition name nor SQLSTATE is specified in a

<command>RAISE EXCEPTION</command> command, the default is to use

<literal>RAISE_EXCEPTION</> (<literal>P0001</>). If no message

text is specified, the default is to use the condition name or

SQLSTATE as message text.

</para>

<note>

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 <literal>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.

</note>

Oracle's statement, although the basic case <literal>RAISE</>

<replaceable class="parameter">exception_name</replaceable> works

similarly.