1#@!#!123s

: / usr / share / doc / postgresql-9.2.24 / html /
Filename : plpgsql-errors-and-messages.html
back
<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN" "http://www.w3.org/TR/html4/loose.dtd">
<HTML
><HEAD
><TITLE
>Errors and Messages</TITLE
><META
NAME="GENERATOR"
CONTENT="Modular DocBook HTML Stylesheet Version 1.79"><LINK
REV="MADE"
HREF="mailto:pgsql-docs@postgresql.org"><LINK
REL="HOME"
TITLE="PostgreSQL 9.2.24 Documentation"
HREF="index.html"><LINK
REL="UP"
TITLE="PL/pgSQL - SQL Procedural Language"
HREF="plpgsql.html"><LINK
REL="PREVIOUS"
TITLE="Cursors"
HREF="plpgsql-cursors.html"><LINK
REL="NEXT"
TITLE="Trigger Procedures"
HREF="plpgsql-trigger.html"><LINK
REL="STYLESHEET"
TYPE="text/css"
HREF="stylesheet.css"><META
HTTP-EQUIV="Content-Type"
CONTENT="text/html; charset=ISO-8859-1"><META
NAME="creation"
CONTENT="2017-11-06T22:43:11"></HEAD
><BODY
CLASS="SECT1"
><DIV
CLASS="NAVHEADER"
><TABLE
SUMMARY="Header navigation table"
WIDTH="100%"
BORDER="0"
CELLPADDING="0"
CELLSPACING="0"
><TR
><TH
COLSPAN="5"
ALIGN="center"
VALIGN="bottom"
><A
HREF="index.html"
>PostgreSQL 9.2.24 Documentation</A
></TH
></TR
><TR
><TD
WIDTH="10%"
ALIGN="left"
VALIGN="top"
><A
TITLE="Cursors"
HREF="plpgsql-cursors.html"
ACCESSKEY="P"
>Prev</A
></TD
><TD
WIDTH="10%"
ALIGN="left"
VALIGN="top"
><A
HREF="plpgsql.html"
ACCESSKEY="U"
>Up</A
></TD
><TD
WIDTH="60%"
ALIGN="center"
VALIGN="bottom"
>Chapter 39. <SPAN
CLASS="APPLICATION"
>PL/pgSQL</SPAN
> - <ACRONYM
CLASS="ACRONYM"
>SQL</ACRONYM
> Procedural Language</TD
><TD
WIDTH="20%"
ALIGN="right"
VALIGN="top"
><A
TITLE="Trigger Procedures"
HREF="plpgsql-trigger.html"
ACCESSKEY="N"
>Next</A
></TD
></TR
></TABLE
><HR
ALIGN="LEFT"
WIDTH="100%"></DIV
><DIV
CLASS="SECT1"
><H1
CLASS="SECT1"
><A
NAME="PLPGSQL-ERRORS-AND-MESSAGES"
>39.8. Errors and Messages</A
></H1
><P
>    Use the <TT
CLASS="COMMAND"
>RAISE</TT
> statement to report messages and
    raise errors.

</P><PRE
CLASS="SYNOPSIS"
>RAISE [<SPAN
CLASS="OPTIONAL"
> <TT
CLASS="REPLACEABLE"
><I
>level</I
></TT
> </SPAN
>] '<TT
CLASS="REPLACEABLE"
><I
>format</I
></TT
>' [<SPAN
CLASS="OPTIONAL"
>, <TT
CLASS="REPLACEABLE"
><I
>expression</I
></TT
> [<SPAN
CLASS="OPTIONAL"
>, ... </SPAN
>]</SPAN
>] [<SPAN
CLASS="OPTIONAL"
> USING <TT
CLASS="REPLACEABLE"
><I
>option</I
></TT
> = <TT
CLASS="REPLACEABLE"
><I
>expression</I
></TT
> [<SPAN
CLASS="OPTIONAL"
>, ... </SPAN
>] </SPAN
>];
RAISE [<SPAN
CLASS="OPTIONAL"
> <TT
CLASS="REPLACEABLE"
><I
>level</I
></TT
> </SPAN
>] <TT
CLASS="REPLACEABLE"
><I
>condition_name</I
></TT
> [<SPAN
CLASS="OPTIONAL"
> USING <TT
CLASS="REPLACEABLE"
><I
>option</I
></TT
> = <TT
CLASS="REPLACEABLE"
><I
>expression</I
></TT
> [<SPAN
CLASS="OPTIONAL"
>, ... </SPAN
>] </SPAN
>];
RAISE [<SPAN
CLASS="OPTIONAL"
> <TT
CLASS="REPLACEABLE"
><I
>level</I
></TT
> </SPAN
>] SQLSTATE '<TT
CLASS="REPLACEABLE"
><I
>sqlstate</I
></TT
>' [<SPAN
CLASS="OPTIONAL"
> USING <TT
CLASS="REPLACEABLE"
><I
>option</I
></TT
> = <TT
CLASS="REPLACEABLE"
><I
>expression</I
></TT
> [<SPAN
CLASS="OPTIONAL"
>, ... </SPAN
>] </SPAN
>];
RAISE [<SPAN
CLASS="OPTIONAL"
> <TT
CLASS="REPLACEABLE"
><I
>level</I
></TT
> </SPAN
>] USING <TT
CLASS="REPLACEABLE"
><I
>option</I
></TT
> = <TT
CLASS="REPLACEABLE"
><I
>expression</I
></TT
> [<SPAN
CLASS="OPTIONAL"
>, ... </SPAN
>];
RAISE ;</PRE
><P>

    The <TT
CLASS="REPLACEABLE"
><I
>level</I
></TT
> option specifies
    the error severity.  Allowed levels are <TT
CLASS="LITERAL"
>DEBUG</TT
>,
    <TT
CLASS="LITERAL"
>LOG</TT
>, <TT
CLASS="LITERAL"
>INFO</TT
>,
    <TT
CLASS="LITERAL"
>NOTICE</TT
>, <TT
CLASS="LITERAL"
>WARNING</TT
>,
    and <TT
CLASS="LITERAL"
>EXCEPTION</TT
>, with <TT
CLASS="LITERAL"
>EXCEPTION</TT
>
    being the default.
    <TT
CLASS="LITERAL"
>EXCEPTION</TT
> 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
    <A
HREF="runtime-config-logging.html#GUC-LOG-MIN-MESSAGES"
>log_min_messages</A
> and
    <A
HREF="runtime-config-logging.html#GUC-CLIENT-MIN-MESSAGES"
>client_min_messages</A
> configuration
    variables. See <A
HREF="runtime-config.html"
>Chapter 18</A
> for more
    information.
   </P
><P
>    After <TT
CLASS="REPLACEABLE"
><I
>level</I
></TT
> if any,
    you can write a <TT
CLASS="REPLACEABLE"
><I
>format</I
></TT
>
    (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, <TT
CLASS="LITERAL"
>%</TT
> is replaced by the
    string representation of the next optional argument's value. Write
    <TT
CLASS="LITERAL"
>%%</TT
> to emit a literal <TT
CLASS="LITERAL"
>%</TT
>.
   </P
><P
>    In this example, the value of <TT
CLASS="LITERAL"
>v_job_id</TT
> will replace the
    <TT
CLASS="LITERAL"
>%</TT
> in the string:
</P><PRE
CLASS="PROGRAMLISTING"
>RAISE NOTICE 'Calling cs_create_job(%)', v_job_id;</PRE
><P>
   </P
><P
>    You can attach additional information to the error report by writing
    <TT
CLASS="LITERAL"
>USING</TT
> followed by <TT
CLASS="REPLACEABLE"
><I
>option</I
></TT
> = <TT
CLASS="REPLACEABLE"
><I
>expression</I
></TT
> items.  The allowed
    <TT
CLASS="REPLACEABLE"
><I
>option</I
></TT
> keywords are
    <TT
CLASS="LITERAL"
>MESSAGE</TT
>, <TT
CLASS="LITERAL"
>DETAIL</TT
>, <TT
CLASS="LITERAL"
>HINT</TT
>, and
    <TT
CLASS="LITERAL"
>ERRCODE</TT
>, while each <TT
CLASS="REPLACEABLE"
><I
>expression</I
></TT
> can be any string-valued
    expression.
    <TT
CLASS="LITERAL"
>MESSAGE</TT
> sets the error message text (this option can't
    be used in the form of <TT
CLASS="COMMAND"
>RAISE</TT
> that includes a format
    string before <TT
CLASS="LITERAL"
>USING</TT
>).
    <TT
CLASS="LITERAL"
>DETAIL</TT
> supplies an error detail message, while
    <TT
CLASS="LITERAL"
>HINT</TT
> supplies a hint message.
    <TT
CLASS="LITERAL"
>ERRCODE</TT
> specifies the error code (SQLSTATE) to report,
    either by condition name as shown in <A
HREF="errcodes-appendix.html"
>Appendix A</A
>,
    or directly as a five-character SQLSTATE code.
   </P
><P
>    This example will abort the transaction with the given error message
    and hint:
</P><PRE
CLASS="PROGRAMLISTING"
>RAISE EXCEPTION 'Nonexistent ID --&#62; %', user_id
      USING HINT = 'Please check your user ID';</PRE
><P>
   </P
><P
>    These two examples show equivalent ways of setting the SQLSTATE:
</P><PRE
CLASS="PROGRAMLISTING"
>RAISE 'Duplicate user ID: %', user_id USING ERRCODE = 'unique_violation';
RAISE 'Duplicate user ID: %', user_id USING ERRCODE = '23505';</PRE
><P>
   </P
><P
>    There is a second <TT
CLASS="COMMAND"
>RAISE</TT
> syntax in which the main argument
    is the condition name or SQLSTATE to be reported, for example:
</P><PRE
CLASS="PROGRAMLISTING"
>RAISE division_by_zero;
RAISE SQLSTATE '22012';</PRE
><P>
    In this syntax, <TT
CLASS="LITERAL"
>USING</TT
> can be used to supply a custom
    error message, detail, or hint.  Another way to do the earlier
    example is
</P><PRE
CLASS="PROGRAMLISTING"
>RAISE unique_violation USING MESSAGE = 'Duplicate user ID: ' || user_id;</PRE
><P>
   </P
><P
>    Still another variant is to write <TT
CLASS="LITERAL"
>RAISE USING</TT
> or <TT
CLASS="LITERAL"
>RAISE
    <TT
CLASS="REPLACEABLE"
><I
>level</I
></TT
> USING</TT
> and put
    everything else into the <TT
CLASS="LITERAL"
>USING</TT
> list.
   </P
><P
>    The last variant of <TT
CLASS="COMMAND"
>RAISE</TT
> has no parameters at all.
    This form can only be used inside a <TT
CLASS="LITERAL"
>BEGIN</TT
> block's
    <TT
CLASS="LITERAL"
>EXCEPTION</TT
> clause;
    it causes the error currently being handled to be re-thrown.
   </P
><DIV
CLASS="NOTE"
><BLOCKQUOTE
CLASS="NOTE"
><P
><B
>Note: </B
>     Before <SPAN
CLASS="PRODUCTNAME"
>PostgreSQL</SPAN
> 9.1, <TT
CLASS="COMMAND"
>RAISE</TT
> without
     parameters was interpreted as re-throwing the error from the block
     containing the active exception handler.  Thus an <TT
CLASS="LITERAL"
>EXCEPTION</TT
>
     clause nested within that handler could not catch it, even if the
     <TT
CLASS="COMMAND"
>RAISE</TT
> was within the nested <TT
CLASS="LITERAL"
>EXCEPTION</TT
> clause's
     block. This was deemed surprising as well as being incompatible with
     Oracle's PL/SQL.
    </P
></BLOCKQUOTE
></DIV
><P
>    If no condition name nor SQLSTATE is specified in a
    <TT
CLASS="COMMAND"
>RAISE EXCEPTION</TT
> command, the default is to use
    <TT
CLASS="LITERAL"
>RAISE_EXCEPTION</TT
> (<TT
CLASS="LITERAL"
>P0001</TT
>).  If no message
    text is specified, the default is to use the condition name or
    SQLSTATE as message text.
   </P
><DIV
CLASS="NOTE"
><BLOCKQUOTE
CLASS="NOTE"
><P
><B
>Note: </B
>     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 <TT
CLASS="LITERAL"
>00000</TT
>.  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.
    </P
></BLOCKQUOTE
></DIV
></DIV
><DIV
CLASS="NAVFOOTER"
><HR
ALIGN="LEFT"
WIDTH="100%"><TABLE
SUMMARY="Footer navigation table"
WIDTH="100%"
BORDER="0"
CELLPADDING="0"
CELLSPACING="0"
><TR
><TD
WIDTH="33%"
ALIGN="left"
VALIGN="top"
><A
HREF="plpgsql-cursors.html"
ACCESSKEY="P"
>Prev</A
></TD
><TD
WIDTH="34%"
ALIGN="center"
VALIGN="top"
><A
HREF="index.html"
ACCESSKEY="H"
>Home</A
></TD
><TD
WIDTH="33%"
ALIGN="right"
VALIGN="top"
><A
HREF="plpgsql-trigger.html"
ACCESSKEY="N"
>Next</A
></TD
></TR
><TR
><TD
WIDTH="33%"
ALIGN="left"
VALIGN="top"
>Cursors</TD
><TD
WIDTH="34%"
ALIGN="center"
VALIGN="top"
><A
HREF="plpgsql.html"
ACCESSKEY="U"
>Up</A
></TD
><TD
WIDTH="33%"
ALIGN="right"
VALIGN="top"
>Trigger Procedures</TD
></TR
></TABLE
></DIV
></BODY
></HTML
>