Home • Explore • Blog • CI
☰



postgresql
39th chunk of `doc/src/sgml/plpgsql.sgml`
1b1755e71883b5a3739431f557390a71b14c6045a4a312200000000100000fa2
 <entry><literal>CONSTRAINT_NAME</literal></entry>
         <entry><type>text</type></entry>
         <entry>the name of the constraint related to exception</entry>
        </row>
        <row>
         <entry><literal>PG_DATATYPE_NAME</literal></entry>
         <entry><type>text</type></entry>
         <entry>the name of the data type related to exception</entry>
        </row>
        <row>
         <entry><literal>MESSAGE_TEXT</literal></entry>
         <entry><type>text</type></entry>
         <entry>the text of the exception's primary message</entry>
        </row>
        <row>
         <entry><literal>TABLE_NAME</literal></entry>
         <entry><type>text</type></entry>
         <entry>the name of the table related to exception</entry>
        </row>
        <row>
         <entry><literal>SCHEMA_NAME</literal></entry>
         <entry><type>text</type></entry>
         <entry>the name of the schema related to exception</entry>
        </row>
        <row>
         <entry><literal>PG_EXCEPTION_DETAIL</literal></entry>
         <entry><type>text</type></entry>
         <entry>the text of the exception's detail message, if any</entry>
        </row>
        <row>
         <entry><literal>PG_EXCEPTION_HINT</literal></entry>
         <entry><type>text</type></entry>
         <entry>the text of the exception's hint message, if any</entry>
        </row>
        <row>
         <entry><literal>PG_EXCEPTION_CONTEXT</literal></entry>
         <entry><type>text</type></entry>
         <entry>line(s) of text describing the call stack at the time of the
          exception (see <xref linkend="plpgsql-call-stack"/>)</entry>
        </row>
       </tbody>
      </tgroup>
     </table>

    <para>
     If the exception did not set a value for an item, an empty string
     will be returned.
    </para>

    <para>
     Here is an example:
<programlisting>
DECLARE
  text_var1 text;
  text_var2 text;
  text_var3 text;
BEGIN
  -- some processing which might cause an exception
  ...
EXCEPTION WHEN OTHERS THEN
  GET STACKED DIAGNOSTICS text_var1 = MESSAGE_TEXT,
                          text_var2 = PG_EXCEPTION_DETAIL,
                          text_var3 = PG_EXCEPTION_HINT;
END;
</programlisting>
    </para>
   </sect3>
  </sect2>

  <sect2 id="plpgsql-call-stack">
   <title>Obtaining Execution Location Information</title>

   <para>
    The <command>GET DIAGNOSTICS</command> command, previously described
    in <xref linkend="plpgsql-statements-diagnostics"/>, retrieves information
    about current execution state (whereas the <command>GET STACKED
    DIAGNOSTICS</command> command discussed above reports information about
    the execution state as of a previous error).  Its <literal>PG_CONTEXT</literal>
    status item is useful for identifying the current execution
    location.  <literal>PG_CONTEXT</literal> returns a text string with line(s)
    of text describing the call stack.  The first line refers to the current
    function and currently executing <command>GET DIAGNOSTICS</command>
    command.  The second and any subsequent lines refer to calling functions
    further up the call stack.  For example:

<programlisting>
CREATE OR REPLACE FUNCTION outer_func() RETURNS integer AS $$
BEGIN
  RETURN inner_func();
END;
$$ LANGUAGE plpgsql;

CREATE OR REPLACE FUNCTION inner_func() RETURNS integer AS $$
DECLARE
  stack text;
BEGIN
  GET DIAGNOSTICS stack = PG_CONTEXT;
  RAISE NOTICE E'--- Call Stack ---\n%', stack;
  RETURN 1;
END;
$$ LANGUAGE plpgsql;

SELECT outer_func();

NOTICE:  --- Call Stack ---
PL/pgSQL function inner_func() line 5 at GET DIAGNOSTICS
PL/pgSQL function outer_func() line 3 at RETURN
CONTEXT:  PL/pgSQL function outer_func() line 3 at RETURN
 outer_func
 ------------
           1
(1 row)
</programlisting>

   </para>

   <para>
    <literal>GET STACKED DIAGNOSTICS ... PG_EXCEPTION_CONTEXT</literal>
    returns the same sort of stack trace, but describing the location
    at which an error was detected, rather than the
Title: Error Diagnostics Items and Obtaining Execution Location Information in PL/pgSQL
Summary
The section lists error diagnostics items that can be retrieved using GET STACKED DIAGNOSTICS, including constraint name, data type name, message text, table name, schema name, exception detail, hint, and context. An example shows how to retrieve the message text, detail, and hint. It then discusses obtaining execution location information using GET DIAGNOSTICS with the PG_CONTEXT status item, which returns a call stack. GET STACKED DIAGNOSTICS ... PG_EXCEPTION_CONTEXT returns a similar stack trace for the location where an error was detected.