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 --> %', 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>
|
