summaryrefslogtreecommitdiffstats
path: root/doc/src/sgml/html/plpgsql-errors-and-messages.html
blob: 3fec9cf8bec408e8f49b6877f4f8e53cded5b638 (plain)
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
<?xml version="1.0" encoding="UTF-8" standalone="no"?>
<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd"><html xmlns="http://www.w3.org/1999/xhtml"><head><meta http-equiv="Content-Type" content="text/html; charset=UTF-8" /><title>43.9. Errors and Messages</title><link rel="stylesheet" type="text/css" href="stylesheet.css" /><link rev="made" href="pgsql-docs@lists.postgresql.org" /><meta name="generator" content="DocBook XSL Stylesheets Vsnapshot" /><link rel="prev" href="plpgsql-transactions.html" title="43.8. Transaction Management" /><link rel="next" href="plpgsql-trigger.html" title="43.10. Trigger Functions" /></head><body id="docContent" class="container-fluid col-10"><div class="navheader"><table width="100%" summary="Navigation header"><tr><th colspan="5" align="center">43.9. Errors and Messages</th></tr><tr><td width="10%" align="left"><a accesskey="p" href="plpgsql-transactions.html" title="43.8. Transaction Management">Prev</a> </td><td width="10%" align="left"><a accesskey="u" href="plpgsql.html" title="Chapter 43. PL/pgSQL — SQL Procedural Language">Up</a></td><th width="60%" align="center">Chapter 43. <span class="application">PL/pgSQL</span><acronym class="acronym">SQL</acronym> Procedural Language</th><td width="10%" align="right"><a accesskey="h" href="index.html" title="PostgreSQL 15.7 Documentation">Home</a></td><td width="10%" align="right"> <a accesskey="n" href="plpgsql-trigger.html" title="43.10. Trigger Functions">Next</a></td></tr></table><hr /></div><div class="sect1" id="PLPGSQL-ERRORS-AND-MESSAGES"><div class="titlepage"><div><div><h2 class="title" style="clear: both">43.9. Errors and Messages</h2></div></div></div><div class="toc"><dl class="toc"><dt><span class="sect2"><a href="plpgsql-errors-and-messages.html#PLPGSQL-STATEMENTS-RAISE">43.9.1. Reporting Errors and Messages</a></span></dt><dt><span class="sect2"><a href="plpgsql-errors-and-messages.html#PLPGSQL-STATEMENTS-ASSERT">43.9.2. Checking Assertions</a></span></dt></dl></div><div class="sect2" id="PLPGSQL-STATEMENTS-RAISE"><div class="titlepage"><div><div><h3 class="title">43.9.1. Reporting Errors and Messages</h3></div></div></div><a id="id-1.8.8.11.2.2" class="indexterm"></a><a id="id-1.8.8.11.2.3" class="indexterm"></a><p>
    Use the <code class="command">RAISE</code> statement to report messages and
    raise errors.

</p><pre class="synopsis">
RAISE [<span class="optional"> <em class="replaceable"><code>level</code></em> </span>] '<em class="replaceable"><code>format</code></em>' [<span class="optional">, <em class="replaceable"><code>expression</code></em> [<span class="optional">, ... </span>]</span>] [<span class="optional"> USING <em class="replaceable"><code>option</code></em> = <em class="replaceable"><code>expression</code></em> [<span class="optional">, ... </span>] </span>];
RAISE [<span class="optional"> <em class="replaceable"><code>level</code></em> </span>] <em class="replaceable"><code>condition_name</code></em> [<span class="optional"> USING <em class="replaceable"><code>option</code></em> = <em class="replaceable"><code>expression</code></em> [<span class="optional">, ... </span>] </span>];
RAISE [<span class="optional"> <em class="replaceable"><code>level</code></em> </span>] SQLSTATE '<em class="replaceable"><code>sqlstate</code></em>' [<span class="optional"> USING <em class="replaceable"><code>option</code></em> = <em class="replaceable"><code>expression</code></em> [<span class="optional">, ... </span>] </span>];
RAISE [<span class="optional"> <em class="replaceable"><code>level</code></em> </span>] USING <em class="replaceable"><code>option</code></em> = <em class="replaceable"><code>expression</code></em> [<span class="optional">, ... </span>];
RAISE ;
</pre><p>

    The <em class="replaceable"><code>level</code></em> option specifies
    the error severity.  Allowed levels are <code class="literal">DEBUG</code>,
    <code class="literal">LOG</code>, <code class="literal">INFO</code>,
    <code class="literal">NOTICE</code>, <code class="literal">WARNING</code>,
    and <code class="literal">EXCEPTION</code>, with <code class="literal">EXCEPTION</code>
    being the default.
    <code class="literal">EXCEPTION</code> 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 class="xref" href="runtime-config-logging.html#GUC-LOG-MIN-MESSAGES">log_min_messages</a> and
    <a class="xref" href="runtime-config-client.html#GUC-CLIENT-MIN-MESSAGES">client_min_messages</a> configuration
    variables. See <a class="xref" href="runtime-config.html" title="Chapter 20. Server Configuration">Chapter 20</a> for more
    information.
   </p><p>
    After <em class="replaceable"><code>level</code></em> if any,
    you can specify a <em class="replaceable"><code>format</code></em> string
    (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, <code class="literal">%</code> is replaced by the
    string representation of the next optional argument's value. Write
    <code class="literal">%%</code> to emit a literal <code class="literal">%</code>.
    The number of arguments must match the number of <code class="literal">%</code>
    placeholders in the format string, or an error is raised during
    the compilation of the function.
   </p><p>
    In this example, the value of <code class="literal">v_job_id</code> will replace the
    <code class="literal">%</code> 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
    <code class="literal">USING</code> followed by <em class="replaceable"><code>option</code></em> = <em class="replaceable"><code>expression</code></em> items.  Each
    <em class="replaceable"><code>expression</code></em> can be any
    string-valued expression.  The allowed <em class="replaceable"><code>option</code></em> key words are:

    </p><div class="variablelist" id="RAISE-USING-OPTIONS"><dl class="variablelist"><dt><span class="term"><code class="literal">MESSAGE</code></span></dt><dd><p>Sets the error message text.  This option can't be used in the
        form of <code class="command">RAISE</code> that includes a format string
        before <code class="literal">USING</code>.</p></dd><dt><span class="term"><code class="literal">DETAIL</code></span></dt><dd><p>Supplies an error detail message.</p></dd><dt><span class="term"><code class="literal">HINT</code></span></dt><dd><p>Supplies a hint message.</p></dd><dt><span class="term"><code class="literal">ERRCODE</code></span></dt><dd><p>Specifies the error code (SQLSTATE) to report, either by condition
        name, as shown in <a class="xref" href="errcodes-appendix.html" title="Appendix A. PostgreSQL Error Codes">Appendix A</a>, or directly as a
        five-character SQLSTATE code.</p></dd><dt><span class="term"><code class="literal">COLUMN</code><br /></span><span class="term"><code class="literal">CONSTRAINT</code><br /></span><span class="term"><code class="literal">DATATYPE</code><br /></span><span class="term"><code class="literal">TABLE</code><br /></span><span class="term"><code class="literal">SCHEMA</code></span></dt><dd><p>Supplies the name of a related object.</p></dd></dl></div><p>
   </p><p>
    This example will abort the transaction with the given error message
    and hint:
</p><pre class="programlisting">
RAISE EXCEPTION 'Nonexistent ID --&gt; %', 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 <code class="command">RAISE</code> 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, <code class="literal">USING</code> 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 <code class="literal">RAISE USING</code> or <code class="literal">RAISE
    <em class="replaceable"><code>level</code></em> USING</code> and put
    everything else into the <code class="literal">USING</code> list.
   </p><p>
    The last variant of <code class="command">RAISE</code> has no parameters at all.
    This form can only be used inside a <code class="literal">BEGIN</code> block's
    <code class="literal">EXCEPTION</code> clause;
    it causes the error currently being handled to be re-thrown.
   </p><div class="note"><h3 class="title">Note</h3><p>
     Before <span class="productname">PostgreSQL</span> 9.1, <code class="command">RAISE</code> without
     parameters was interpreted as re-throwing the error from the block
     containing the active exception handler.  Thus an <code class="literal">EXCEPTION</code>
     clause nested within that handler could not catch it, even if the
     <code class="command">RAISE</code> was within the nested <code class="literal">EXCEPTION</code> clause's
     block. This was deemed surprising as well as being incompatible with
     Oracle's PL/SQL.
    </p></div><p>
    If no condition name nor SQLSTATE is specified in a
    <code class="command">RAISE EXCEPTION</code> command, the default is to use
    <code class="literal">raise_exception</code> (<code class="literal">P0001</code>).
    If no message text is specified, the default is to use the condition
    name or SQLSTATE as message text.
   </p><div class="note"><h3 class="title">Note</h3><p>
     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 <code class="literal">00000</code>.  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></div></div><div class="sect2" id="PLPGSQL-STATEMENTS-ASSERT"><div class="titlepage"><div><div><h3 class="title">43.9.2. Checking Assertions</h3></div></div></div><a id="id-1.8.8.11.3.2" class="indexterm"></a><a id="id-1.8.8.11.3.3" class="indexterm"></a><a id="id-1.8.8.11.3.4" class="indexterm"></a><p>
    The <code class="command">ASSERT</code> statement is a convenient shorthand for
    inserting debugging checks into <span class="application">PL/pgSQL</span>
    functions.

</p><pre class="synopsis">
ASSERT <em class="replaceable"><code>condition</code></em> [<span class="optional"> , <em class="replaceable"><code>message</code></em> </span>];
</pre><p>

    The <em class="replaceable"><code>condition</code></em> is a Boolean
    expression that is expected to always evaluate to true; if it does,
    the <code class="command">ASSERT</code> statement does nothing further.  If the
    result is false or null, then an <code class="literal">ASSERT_FAILURE</code> exception
    is raised.  (If an error occurs while evaluating
    the <em class="replaceable"><code>condition</code></em>, it is
    reported as a normal error.)
   </p><p>
    If the optional <em class="replaceable"><code>message</code></em> is
    provided, it is an expression whose result (if not null) replaces the
    default error message text <span class="quote"><span class="quote">assertion failed</span></span>, should
    the <em class="replaceable"><code>condition</code></em> fail.
    The <em class="replaceable"><code>message</code></em> expression is
    not evaluated in the normal case where the assertion succeeds.
   </p><p>
    Testing of assertions can be enabled or disabled via the configuration
    parameter <code class="literal">plpgsql.check_asserts</code>, which takes a Boolean
    value; the default is <code class="literal">on</code>.  If this parameter
    is <code class="literal">off</code> then <code class="command">ASSERT</code> statements do nothing.
   </p><p>
    Note that <code class="command">ASSERT</code> is meant for detecting program
    bugs, not for reporting ordinary error conditions.  Use
    the <code class="command">RAISE</code> statement, described above, for that.
   </p></div></div><div class="navfooter"><hr /><table width="100%" summary="Navigation footer"><tr><td width="40%" align="left"><a accesskey="p" href="plpgsql-transactions.html" title="43.8. Transaction Management">Prev</a> </td><td width="20%" align="center"><a accesskey="u" href="plpgsql.html" title="Chapter 43. PL/pgSQL — SQL Procedural Language">Up</a></td><td width="40%" align="right"> <a accesskey="n" href="plpgsql-trigger.html" title="43.10. Trigger Functions">Next</a></td></tr><tr><td width="40%" align="left" valign="top">43.8. Transaction Management </td><td width="20%" align="center"><a accesskey="h" href="index.html" title="PostgreSQL 15.7 Documentation">Home</a></td><td width="40%" align="right" valign="top"> 43.10. Trigger Functions</td></tr></table></div></body></html>