Improve plpgsql's RAISE command. It is now possible to attach DETAIL and

HINT fields to a user-thrown error message, and to specify the SQLSTATE
error code to use.  The syntax has also been tweaked so that the
Oracle-compatible case "RAISE exception_name" works (though you won't get a
very nice error message if you just write that much).  Lastly, support
the Oracle-compatible syntax "RAISE" with no parameters to re-throw
the current error from within an EXCEPTION block.

In passing, allow the syntax SQLSTATE 'nnnnn' within EXCEPTION lists,
so that there is a way to trap errors with custom SQLSTATE codes.

Pavel Stehule and Tom Lane

1 files changed, 104 insertions, 22 deletions

diff --git a/doc/src/sgml/plpgsql.sgml b/doc/src/sgml/plpgsql.sgml
index 1065eddc74e..09ad6944dba 100644
--- a/doc/src/sgml/plpgsql.sgml
+++ b/doc/src/sgml/plpgsql.sgml
@@ -1,4 +1,4 @@
-<!-- $PostgreSQL: pgsql/doc/src/sgml/plpgsql.sgml,v 1.128 2008/05/03 00:11:36 tgl Exp $ -->
+<!-- $PostgreSQL: pgsql/doc/src/sgml/plpgsql.sgml,v 1.129 2008/05/13 22:10:29 tgl Exp $ -->
 
 <chapter id="plpgsql">
   <title><application>PL/pgSQL</application> - <acronym>SQL</acronym> Procedural Language</title>
@@ -2133,7 +2133,12 @@ END;
      condition name <literal>OTHERS</> matches every error type except
      <literal>QUERY_CANCELED</>.  (It is possible, but often unwise,
      to trap <literal>QUERY_CANCELED</> by name.)  Condition names are
-     not case-sensitive.
+     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>
     </para>
 
     <para>
@@ -2750,13 +2755,19 @@ END LOOP <optional> <replaceable>label</replaceable> </optional>;
     raise errors.
 
 <synopsis>
-RAISE <replaceable class="parameter">level</replaceable> '<replaceable class="parameter">format</replaceable>' <optional>, <replaceable class="parameter">expression</replaceable> <optional>, ...</optional></optional>;
+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 ;
 </synopsis>
 
-    Possible levels are <literal>DEBUG</literal>,
+    The <replaceable class="parameter">level</replaceable> option specifies
+    the error severity.  Allowed levels are <literal>DEBUG</literal>,
     <literal>LOG</literal>, <literal>INFO</literal>,
     <literal>NOTICE</literal>, <literal>WARNING</literal>,
-    and <literal>EXCEPTION</literal>.
+    and <literal>EXCEPTION</literal>, with <literal>EXCEPTION</literal>
+    being the default.
     <literal>EXCEPTION</literal> raises an error (which normally aborts the
     current transaction); the other levels only generate messages of different
     priority levels.
@@ -2769,19 +2780,17 @@ RAISE <replaceable class="parameter">level</replaceable> '<replaceable class="pa
    </para>
 
    <para>
+    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.
     Inside the format string, <literal>%</literal> is replaced by the
-    next optional argument's string representation. Write
+    string representation of the next optional argument's value. Write
     <literal>%%</literal> to emit a literal <literal>%</literal>.
-    Arguments can be simple variables or expressions,
-    but the format must be a simple string literal.
    </para>
 
-   <!--
-   This example should work, but does not:
-        RAISE NOTICE 'Id number ' || key || ' not found!';
-   Put it back when we allow non-string-literal formats.
-    -->
-
    <para>
     In this example, the value of <literal>v_job_id</> will replace the
     <literal>%</literal> in the string:
@@ -2791,19 +2800,90 @@ RAISE NOTICE 'Calling cs_create_job(%)', v_job_id;
    </para>
 
    <para>
-    This example will abort the transaction with the given error message:
+    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
 <programlisting>
-RAISE EXCEPTION 'Nonexistent ID --> %', user_id;
+RAISE unique_violation USING MESSAGE = 'Duplicate user ID: ' || user_id;
 </programlisting>
    </para>
 
+   <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>
     <para>
-     <command>RAISE EXCEPTION</command> presently always generates
-     the same <varname>SQLSTATE</varname> code, <literal>P0001</>, no matter what message
-     it is invoked with.  It is possible to trap this exception with
-     <literal>EXCEPTION ... WHEN RAISE_EXCEPTION THEN ...</> but there
-     is no way to tell one <command>RAISE</> from another.
+     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.
     </para>
+   </note>
+
  </sect1>
 
  <sect1 id="plpgsql-trigger">
@@ -4307,7 +4387,9 @@ $$ LANGUAGE plpgsql;
      <callout arearefs="co.plpgsql-porting-raise">
       <para>
        The syntax of <literal>RAISE</> is considerably different from
-       Oracle's similar statement.
+       Oracle's statement, although the basic case <literal>RAISE</>
+       <replaceable class="parameter">exception_name</replaceable> works
+       similarly.
       </para>
      </callout>
      <callout arearefs="co.plpgsql-porting-exception">