codekingpro/portable-devtools
115k
1<?xml version="1.0" encoding="UTF-8" standalone="no"?>2<!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 16.3 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 <a href="#PLPGSQL-ERRORS-AND-MESSAGES" class="id_link">#</a></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 <a href="#PLPGSQL-STATEMENTS-RAISE" class="id_link">#</a></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>3 Use the <code class="command">RAISE</code> statement to report messages and4 raise errors.5 6</p><pre class="synopsis">7RAISE [<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>];8RAISE [<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>];9RAISE [<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>];10RAISE [<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>];11RAISE ;12</pre><p>13 14 The <em class="replaceable"><code>level</code></em> option specifies15 the error severity. Allowed levels are <code class="literal">DEBUG</code>,16 <code class="literal">LOG</code>, <code class="literal">INFO</code>,17 <code class="literal">NOTICE</code>, <code class="literal">WARNING</code>,18 and <code class="literal">EXCEPTION</code>, with <code class="literal">EXCEPTION</code>19 being the default.20 <code class="literal">EXCEPTION</code> raises an error (which normally aborts the21 current transaction); the other levels only generate messages of different22 priority levels.23 Whether messages of a particular priority are reported to the client,24 written to the server log, or both is controlled by the25 <a class="xref" href="runtime-config-logging.html#GUC-LOG-MIN-MESSAGES">log_min_messages</a> and26 <a class="xref" href="runtime-config-client.html#GUC-CLIENT-MIN-MESSAGES">client_min_messages</a> configuration27 variables. See <a class="xref" href="runtime-config.html" title="Chapter 20. Server Configuration">Chapter 20</a> for more28 information.29 </p><p>30 After <em class="replaceable"><code>level</code></em> if any,31 you can specify a <em class="replaceable"><code>format</code></em> string32 (which must be a simple string literal, not an expression). The33 format string specifies the error message text to be reported.34 The format string can be followed35 by optional argument expressions to be inserted into the message.36 Inside the format string, <code class="literal">%</code> is replaced by the37 string representation of the next optional argument's value. Write38 <code class="literal">%%</code> to emit a literal <code class="literal">%</code>.39 The number of arguments must match the number of <code class="literal">%</code>40 placeholders in the format string, or an error is raised during41 the compilation of the function.42 </p><p>43 In this example, the value of <code class="literal">v_job_id</code> will replace the44 <code class="literal">%</code> in the string:45</p><pre class="programlisting">46RAISE NOTICE 'Calling cs_create_job(%)', v_job_id;47</pre><p>48 </p><p>49 You can attach additional information to the error report by writing50 <code class="literal">USING</code> followed by <em class="replaceable"><code>option</code></em> = <em class="replaceable"><code>expression</code></em> items. Each51 <em class="replaceable"><code>expression</code></em> can be any52 string-valued expression. The allowed <em class="replaceable"><code>option</code></em> key words are:53 54 </p><div class="variablelist" id="RAISE-USING-OPTIONS"><dl class="variablelist"><dt id="RAISE-USING-OPTION-MESSAGE"><span class="term"><code class="literal">MESSAGE</code></span> <a href="#RAISE-USING-OPTION-MESSAGE" class="id_link">#</a></dt><dd><p>Sets the error message text. This option can't be used in the55 form of <code class="command">RAISE</code> that includes a format string56 before <code class="literal">USING</code>.</p></dd><dt id="RAISE-USING-OPTION-DETAIL"><span class="term"><code class="literal">DETAIL</code></span> <a href="#RAISE-USING-OPTION-DETAIL" class="id_link">#</a></dt><dd><p>Supplies an error detail message.</p></dd><dt id="RAISE-USING-OPTION-HINT"><span class="term"><code class="literal">HINT</code></span> <a href="#RAISE-USING-OPTION-HINT" class="id_link">#</a></dt><dd><p>Supplies a hint message.</p></dd><dt id="RAISE-USING-OPTION-ERRCODE"><span class="term"><code class="literal">ERRCODE</code></span> <a href="#RAISE-USING-OPTION-ERRCODE" class="id_link">#</a></dt><dd><p>Specifies the error code (SQLSTATE) to report, either by condition57 name, as shown in <a class="xref" href="errcodes-appendix.html" title="Appendix A. PostgreSQL Error Codes">Appendix A</a>, or directly as a58 five-character SQLSTATE code.</p></dd><dt id="RAISE-USING-OPTION-COLUMN"><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> <a href="#RAISE-USING-OPTION-COLUMN" class="id_link">#</a></dt><dd><p>Supplies the name of a related object.</p></dd></dl></div><p>59 </p><p>60 This example will abort the transaction with the given error message61 and hint:62</p><pre class="programlisting">63RAISE EXCEPTION 'Nonexistent ID --> %', user_id64 USING HINT = 'Please check your user ID';65</pre><p>66 </p><p>67 These two examples show equivalent ways of setting the SQLSTATE:68</p><pre class="programlisting">69RAISE 'Duplicate user ID: %', user_id USING ERRCODE = 'unique_violation';70RAISE 'Duplicate user ID: %', user_id USING ERRCODE = '23505';71</pre><p>72 </p><p>73 There is a second <code class="command">RAISE</code> syntax in which the main argument74 is the condition name or SQLSTATE to be reported, for example:75</p><pre class="programlisting">76RAISE division_by_zero;77RAISE SQLSTATE '22012';78</pre><p>79 In this syntax, <code class="literal">USING</code> can be used to supply a custom80 error message, detail, or hint. Another way to do the earlier81 example is82</p><pre class="programlisting">83RAISE unique_violation USING MESSAGE = 'Duplicate user ID: ' || user_id;84</pre><p>85 </p><p>86 Still another variant is to write <code class="literal">RAISE USING</code> or <code class="literal">RAISE87 <em class="replaceable"><code>level</code></em> USING</code> and put88 everything else into the <code class="literal">USING</code> list.89 </p><p>90 The last variant of <code class="command">RAISE</code> has no parameters at all.91 This form can only be used inside a <code class="literal">BEGIN</code> block's92 <code class="literal">EXCEPTION</code> clause;93 it causes the error currently being handled to be re-thrown.94 </p><div class="note"><h3 class="title">Note</h3><p>95 Before <span class="productname">PostgreSQL</span> 9.1, <code class="command">RAISE</code> without96 parameters was interpreted as re-throwing the error from the block97 containing the active exception handler. Thus an <code class="literal">EXCEPTION</code>98 clause nested within that handler could not catch it, even if the99 <code class="command">RAISE</code> was within the nested <code class="literal">EXCEPTION</code> clause's100 block. This was deemed surprising as well as being incompatible with101 Oracle's PL/SQL.102 </p></div><p>103 If no condition name nor SQLSTATE is specified in a104 <code class="command">RAISE EXCEPTION</code> command, the default is to use105 <code class="literal">raise_exception</code> (<code class="literal">P0001</code>).106 If no message text is specified, the default is to use the condition107 name or SQLSTATE as message text.108 </p><div class="note"><h3 class="title">Note</h3><p>109 When specifying an error code by SQLSTATE code, you are not110 limited to the predefined error codes, but can select any111 error code consisting of five digits and/or upper-case ASCII112 letters, other than <code class="literal">00000</code>. It is recommended that113 you avoid throwing error codes that end in three zeroes, because114 these are category codes and can only be trapped by trapping115 the whole category.116 </p></div></div><div class="sect2" id="PLPGSQL-STATEMENTS-ASSERT"><div class="titlepage"><div><div><h3 class="title">43.9.2. Checking Assertions <a href="#PLPGSQL-STATEMENTS-ASSERT" class="id_link">#</a></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>117 The <code class="command">ASSERT</code> statement is a convenient shorthand for118 inserting debugging checks into <span class="application">PL/pgSQL</span>119 functions.120 121</p><pre class="synopsis">122ASSERT <em class="replaceable"><code>condition</code></em> [<span class="optional"> , <em class="replaceable"><code>message</code></em> </span>];123</pre><p>124 125 The <em class="replaceable"><code>condition</code></em> is a Boolean126 expression that is expected to always evaluate to true; if it does,127 the <code class="command">ASSERT</code> statement does nothing further. If the128 result is false or null, then an <code class="literal">ASSERT_FAILURE</code> exception129 is raised. (If an error occurs while evaluating130 the <em class="replaceable"><code>condition</code></em>, it is131 reported as a normal error.)132 </p><p>133 If the optional <em class="replaceable"><code>message</code></em> is134 provided, it is an expression whose result (if not null) replaces the135 default error message text <span class="quote">“<span class="quote">assertion failed</span>”</span>, should136 the <em class="replaceable"><code>condition</code></em> fail.137 The <em class="replaceable"><code>message</code></em> expression is138 not evaluated in the normal case where the assertion succeeds.139 </p><p>140 Testing of assertions can be enabled or disabled via the configuration141 parameter <code class="literal">plpgsql.check_asserts</code>, which takes a Boolean142 value; the default is <code class="literal">on</code>. If this parameter143 is <code class="literal">off</code> then <code class="command">ASSERT</code> statements do nothing.144 </p><p>145 Note that <code class="command">ASSERT</code> is meant for detecting program146 bugs, not for reporting ordinary error conditions. Use147 the <code class="command">RAISE</code> statement, described above, for that.148 </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 16.3 Documentation">Home</a></td><td width="40%" align="right" valign="top"> 43.10. Trigger Functions</td></tr></table></div></body></html>