Team Ai
Datasetpublic

codekingpro/portable-devtools

sourceHugging Faceupdated 5mo agoView on Hugging Face
1likes15kdownloads
plpgsql-control-structures.html943 linesDownload Raw Back to html
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.6. Control Structures</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-statements.html" title="43.5. Basic Statements" /><link rel="next" href="plpgsql-cursors.html" title="43.7. Cursors" /></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.6. Control Structures</th></tr><tr><td width="10%" align="left"><a accesskey="p" href="plpgsql-statements.html" title="43.5. Basic Statements">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-cursors.html" title="43.7. Cursors">Next</a></td></tr></table><hr /></div><div class="sect1" id="PLPGSQL-CONTROL-STRUCTURES"><div class="titlepage"><div><div><h2 class="title" style="clear: both">43.6. Control Structures <a href="#PLPGSQL-CONTROL-STRUCTURES" class="id_link">#</a></h2></div></div></div><div class="toc"><dl class="toc"><dt><span class="sect2"><a href="plpgsql-control-structures.html#PLPGSQL-STATEMENTS-RETURNING">43.6.1. Returning from a Function</a></span></dt><dt><span class="sect2"><a href="plpgsql-control-structures.html#PLPGSQL-STATEMENTS-RETURNING-PROCEDURE">43.6.2. Returning from a Procedure</a></span></dt><dt><span class="sect2"><a href="plpgsql-control-structures.html#PLPGSQL-STATEMENTS-CALLING-PROCEDURE">43.6.3. Calling a Procedure</a></span></dt><dt><span class="sect2"><a href="plpgsql-control-structures.html#PLPGSQL-CONDITIONALS">43.6.4. Conditionals</a></span></dt><dt><span class="sect2"><a href="plpgsql-control-structures.html#PLPGSQL-CONTROL-STRUCTURES-LOOPS">43.6.5. Simple Loops</a></span></dt><dt><span class="sect2"><a href="plpgsql-control-structures.html#PLPGSQL-RECORDS-ITERATING">43.6.6. Looping through Query Results</a></span></dt><dt><span class="sect2"><a href="plpgsql-control-structures.html#PLPGSQL-FOREACH-ARRAY">43.6.7. Looping through Arrays</a></span></dt><dt><span class="sect2"><a href="plpgsql-control-structures.html#PLPGSQL-ERROR-TRAPPING">43.6.8. Trapping Errors</a></span></dt><dt><span class="sect2"><a href="plpgsql-control-structures.html#PLPGSQL-CALL-STACK">43.6.9. Obtaining Execution Location Information</a></span></dt></dl></div><p>3    Control structures are probably the most useful (and4    important) part of <span class="application">PL/pgSQL</span>. With5    <span class="application">PL/pgSQL</span>'s control structures,6    you can manipulate <span class="productname">PostgreSQL</span> data in a very7    flexible and powerful way.8   </p><div class="sect2" id="PLPGSQL-STATEMENTS-RETURNING"><div class="titlepage"><div><div><h3 class="title">43.6.1. Returning from a Function <a href="#PLPGSQL-STATEMENTS-RETURNING" class="id_link">#</a></h3></div></div></div><p>9     There are two commands available that allow you to return data10     from a function: <code class="command">RETURN</code> and <code class="command">RETURN11     NEXT</code>.12    </p><div class="sect3" id="PLPGSQL-STATEMENTS-RETURNING-RETURN"><div class="titlepage"><div><div><h4 class="title">43.6.1.1. <code class="command">RETURN</code> <a href="#PLPGSQL-STATEMENTS-RETURNING-RETURN" class="id_link">#</a></h4></div></div></div><pre class="synopsis">13RETURN <em class="replaceable"><code>expression</code></em>;14</pre><p>15      <code class="command">RETURN</code> with an expression terminates the16      function and returns the value of17      <em class="replaceable"><code>expression</code></em> to the caller.  This form18      is used for <span class="application">PL/pgSQL</span> functions that do19      not return a set.20     </p><p>21      In a function that returns a scalar type, the expression's result will22      automatically be cast into the function's return type as described for23      assignments.  But to return a composite (row) value, you must write an24      expression delivering exactly the requested column set.  This may25      require use of explicit casting.26     </p><p>27      If you declared the function with output parameters, write just28      <code class="command">RETURN</code> with no expression.  The current values29      of the output parameter variables will be returned.30     </p><p>31      If you declared the function to return <code class="type">void</code>, a32      <code class="command">RETURN</code> statement can be used to exit the function33      early; but do not write an expression following34      <code class="command">RETURN</code>.35     </p><p>36      The return value of a function cannot be left undefined. If37      control reaches the end of the top-level block of the function38      without hitting a <code class="command">RETURN</code> statement, a run-time39      error will occur.  This restriction does not apply to functions40      with output parameters and functions returning <code class="type">void</code>,41      however.  In those cases a <code class="command">RETURN</code> statement is42      automatically executed if the top-level block finishes.43     </p><p>44      Some examples:45 46</p><pre class="programlisting">47-- functions returning a scalar type48RETURN 1 + 2;49RETURN scalar_var;50 51-- functions returning a composite type52RETURN composite_type_var;53RETURN (1, 2, 'three'::text);  -- must cast columns to correct types54</pre><p>55     </p></div><div class="sect3" id="PLPGSQL-STATEMENTS-RETURNING-RETURN-NEXT"><div class="titlepage"><div><div><h4 class="title">43.6.1.2. <code class="command">RETURN NEXT</code> and <code class="command">RETURN QUERY</code> <a href="#PLPGSQL-STATEMENTS-RETURNING-RETURN-NEXT" class="id_link">#</a></h4></div></div></div><a id="id-1.8.8.8.3.4.2" class="indexterm"></a><a id="id-1.8.8.8.3.4.3" class="indexterm"></a><pre class="synopsis">56RETURN NEXT <em class="replaceable"><code>expression</code></em>;57RETURN QUERY <em class="replaceable"><code>query</code></em>;58RETURN QUERY EXECUTE <em class="replaceable"><code>command-string</code></em> [<span class="optional"> USING <em class="replaceable"><code>expression</code></em> [<span class="optional">, ... </span>] </span>];59</pre><p>60      When a <span class="application">PL/pgSQL</span> function is declared to return61      <code class="literal">SETOF <em class="replaceable"><code>sometype</code></em></code>, the procedure62      to follow is slightly different.  In that case, the individual63      items to return are specified by a sequence of <code class="command">RETURN64      NEXT</code> or <code class="command">RETURN QUERY</code> commands, and65      then a final <code class="command">RETURN</code> command with no argument66      is used to indicate that the function has finished executing.67      <code class="command">RETURN NEXT</code> can be used with both scalar and68      composite data types; with a composite result type, an entire69      <span class="quote">“<span class="quote">table</span>”</span> of results will be returned.70      <code class="command">RETURN QUERY</code> appends the results of executing71      a query to the function's result set. <code class="command">RETURN72      NEXT</code> and <code class="command">RETURN QUERY</code> can be freely73      intermixed in a single set-returning function, in which case74      their results will be concatenated.75     </p><p>76      <code class="command">RETURN NEXT</code> and <code class="command">RETURN77      QUERY</code> do not actually return from the function —78      they simply append zero or more rows to the function's result79      set.  Execution then continues with the next statement in the80      <span class="application">PL/pgSQL</span> function.  As successive81      <code class="command">RETURN NEXT</code> or <code class="command">RETURN82      QUERY</code> commands are executed, the result set is built83      up.  A final <code class="command">RETURN</code>, which should have no84      argument, causes control to exit the function (or you can just85      let control reach the end of the function).86     </p><p>87      <code class="command">RETURN QUERY</code> has a variant88      <code class="command">RETURN QUERY EXECUTE</code>, which specifies the89      query to be executed dynamically.  Parameter expressions can90      be inserted into the computed query string via <code class="literal">USING</code>,91      in just the same way as in the <code class="command">EXECUTE</code> command.92     </p><p>93      If you declared the function with output parameters, write just94      <code class="command">RETURN NEXT</code> with no expression.  On each95      execution, the current values of the output parameter96      variable(s) will be saved for eventual return as a row of the97      result.  Note that you must declare the function as returning98      <code class="literal">SETOF record</code> when there are multiple output99      parameters, or <code class="literal">SETOF <em class="replaceable"><code>sometype</code></em></code>100      when there is just one output parameter of type101      <em class="replaceable"><code>sometype</code></em>, in order to create a set-returning102      function with output parameters.103     </p><p>104      Here is an example of a function using <code class="command">RETURN105      NEXT</code>:106 107</p><pre class="programlisting">108CREATE TABLE foo (fooid INT, foosubid INT, fooname TEXT);109INSERT INTO foo VALUES (1, 2, 'three');110INSERT INTO foo VALUES (4, 5, 'six');111 112CREATE OR REPLACE FUNCTION get_all_foo() RETURNS SETOF foo AS113$BODY$114DECLARE115    r foo%rowtype;116BEGIN117    FOR r IN118        SELECT * FROM foo WHERE fooid &gt; 0119    LOOP120        -- can do some processing here121        RETURN NEXT r; -- return current row of SELECT122    END LOOP;123    RETURN;124END;125$BODY$126LANGUAGE plpgsql;127 128SELECT * FROM get_all_foo();129</pre><p>130     </p><p>131      Here is an example of a function using <code class="command">RETURN132      QUERY</code>:133 134</p><pre class="programlisting">135CREATE FUNCTION get_available_flightid(date) RETURNS SETOF integer AS136$BODY$137BEGIN138    RETURN QUERY SELECT flightid139                   FROM flight140                  WHERE flightdate &gt;= $1141                    AND flightdate &lt; ($1 + 1);142 143    -- Since execution is not finished, we can check whether rows were returned144    -- and raise exception if not.145    IF NOT FOUND THEN146        RAISE EXCEPTION 'No flight at %.', $1;147    END IF;148 149    RETURN;150 END;151$BODY$152LANGUAGE plpgsql;153 154-- Returns available flights or raises exception if there are no155-- available flights.156SELECT * FROM get_available_flightid(CURRENT_DATE);157</pre><p>158     </p><div class="note"><h3 class="title">Note</h3><p>159       The current implementation of <code class="command">RETURN NEXT</code>160       and <code class="command">RETURN QUERY</code> stores the entire result set161       before returning from the function, as discussed above.  That162       means that if a <span class="application">PL/pgSQL</span> function produces a163       very large result set, performance might be poor: data will be164       written to disk to avoid memory exhaustion, but the function165       itself will not return until the entire result set has been166       generated.  A future version of <span class="application">PL/pgSQL</span> might167       allow users to define set-returning functions168       that do not have this limitation.  Currently, the point at169       which data begins being written to disk is controlled by the170       <a class="xref" href="runtime-config-resource.html#GUC-WORK-MEM">work_mem</a>171       configuration variable.  Administrators who have sufficient172       memory to store larger result sets in memory should consider173       increasing this parameter.174      </p></div></div></div><div class="sect2" id="PLPGSQL-STATEMENTS-RETURNING-PROCEDURE"><div class="titlepage"><div><div><h3 class="title">43.6.2. Returning from a Procedure <a href="#PLPGSQL-STATEMENTS-RETURNING-PROCEDURE" class="id_link">#</a></h3></div></div></div><p>175     A procedure does not have a return value.  A procedure can therefore end176     without a <code class="command">RETURN</code> statement.  If you wish to use177     a <code class="command">RETURN</code> statement to exit the code early, write178     just <code class="command">RETURN</code> with no expression.179    </p><p>180     If the procedure has output parameters, the final values of the output181     parameter variables will be returned to the caller.182    </p></div><div class="sect2" id="PLPGSQL-STATEMENTS-CALLING-PROCEDURE"><div class="titlepage"><div><div><h3 class="title">43.6.3. Calling a Procedure <a href="#PLPGSQL-STATEMENTS-CALLING-PROCEDURE" class="id_link">#</a></h3></div></div></div><p>183     A <span class="application">PL/pgSQL</span> function, procedure,184     or <code class="command">DO</code> block can call a procedure185     using <code class="command">CALL</code>.  Output parameters are handled186     differently from the way that <code class="command">CALL</code> works in plain187     SQL.  Each <code class="literal">OUT</code> or <code class="literal">INOUT</code>188     parameter of the procedure must189     correspond to a variable in the <code class="command">CALL</code> statement, and190     whatever the procedure returns is assigned back to that variable after191     it returns.  For example:192</p><pre class="programlisting">193CREATE PROCEDURE triple(INOUT x int)194LANGUAGE plpgsql195AS $$196BEGIN197    x := x * 3;198END;199$$;200 201DO $$202DECLARE myvar int := 5;203BEGIN204  CALL triple(myvar);205  RAISE NOTICE 'myvar = %', myvar;  -- prints 15206END;207$$;208</pre><p>209     The variable corresponding to an output parameter can be a simple210     variable or a field of a composite-type variable.  Currently,211     it cannot be an element of an array.212    </p></div><div class="sect2" id="PLPGSQL-CONDITIONALS"><div class="titlepage"><div><div><h3 class="title">43.6.4. Conditionals <a href="#PLPGSQL-CONDITIONALS" class="id_link">#</a></h3></div></div></div><p>213     <code class="command">IF</code> and <code class="command">CASE</code> statements let you execute214     alternative commands based on certain conditions.215     <span class="application">PL/pgSQL</span> has three forms of <code class="command">IF</code>:216    </p><div class="itemizedlist"><ul class="itemizedlist" style="list-style-type: disc; "><li class="listitem"><p><code class="literal">IF ... THEN ... END IF</code></p></li><li class="listitem"><p><code class="literal">IF ... THEN ... ELSE ... END IF</code></p></li><li class="listitem"><p><code class="literal">IF ... THEN ... ELSIF ... THEN ... ELSE ... END IF</code></p></li></ul></div><p>217 218    and two forms of <code class="command">CASE</code>:219    </p><div class="itemizedlist"><ul class="itemizedlist" style="list-style-type: disc; "><li class="listitem"><p><code class="literal">CASE ... WHEN ... THEN ... ELSE ... END CASE</code></p></li><li class="listitem"><p><code class="literal">CASE WHEN ... THEN ... ELSE ... END CASE</code></p></li></ul></div><p>220    </p><div class="sect3" id="PLPGSQL-CONDITIONALS-IF-THEN"><div class="titlepage"><div><div><h4 class="title">43.6.4.1. <code class="literal">IF-THEN</code> <a href="#PLPGSQL-CONDITIONALS-IF-THEN" class="id_link">#</a></h4></div></div></div><pre class="synopsis">221IF <em class="replaceable"><code>boolean-expression</code></em> THEN222    <em class="replaceable"><code>statements</code></em>223END IF;224</pre><p>225        <code class="literal">IF-THEN</code> statements are the simplest form of226        <code class="literal">IF</code>. The statements between227        <code class="literal">THEN</code> and <code class="literal">END IF</code> will be228        executed if the condition is true. Otherwise, they are229        skipped.230       </p><p>231        Example:232</p><pre class="programlisting">233IF v_user_id &lt;&gt; 0 THEN234    UPDATE users SET email = v_email WHERE user_id = v_user_id;235END IF;236</pre><p>237       </p></div><div class="sect3" id="PLPGSQL-CONDITIONALS-IF-THEN-ELSE"><div class="titlepage"><div><div><h4 class="title">43.6.4.2. <code class="literal">IF-THEN-ELSE</code> <a href="#PLPGSQL-CONDITIONALS-IF-THEN-ELSE" class="id_link">#</a></h4></div></div></div><pre class="synopsis">238IF <em class="replaceable"><code>boolean-expression</code></em> THEN239    <em class="replaceable"><code>statements</code></em>240ELSE241    <em class="replaceable"><code>statements</code></em>242END IF;243</pre><p>244        <code class="literal">IF-THEN-ELSE</code> statements add to245        <code class="literal">IF-THEN</code> by letting you specify an246        alternative set of statements that should be executed if the247        condition is not true.  (Note this includes the case where the248        condition evaluates to NULL.)249       </p><p>250        Examples:251</p><pre class="programlisting">252IF parentid IS NULL OR parentid = ''253THEN254    RETURN fullname;255ELSE256    RETURN hp_true_filename(parentid) || '/' || fullname;257END IF;258</pre><p>259 260</p><pre class="programlisting">261IF v_count &gt; 0 THEN262    INSERT INTO users_count (count) VALUES (v_count);263    RETURN 't';264ELSE265    RETURN 'f';266END IF;267</pre><p>268     </p></div><div class="sect3" id="PLPGSQL-CONDITIONALS-IF-THEN-ELSIF"><div class="titlepage"><div><div><h4 class="title">43.6.4.3. <code class="literal">IF-THEN-ELSIF</code> <a href="#PLPGSQL-CONDITIONALS-IF-THEN-ELSIF" class="id_link">#</a></h4></div></div></div><pre class="synopsis">269IF <em class="replaceable"><code>boolean-expression</code></em> THEN270    <em class="replaceable"><code>statements</code></em>271[<span class="optional"> ELSIF <em class="replaceable"><code>boolean-expression</code></em> THEN272    <em class="replaceable"><code>statements</code></em>273[<span class="optional"> ELSIF <em class="replaceable"><code>boolean-expression</code></em> THEN274    <em class="replaceable"><code>statements</code></em>275    ...276</span>]277</span>]278[<span class="optional"> ELSE279    <em class="replaceable"><code>statements</code></em> </span>]280END IF;281</pre><p>282        Sometimes there are more than just two alternatives.283        <code class="literal">IF-THEN-ELSIF</code> provides a convenient284        method of checking several alternatives in turn.285        The <code class="literal">IF</code> conditions are tested successively286        until the first one that is true is found.  Then the287        associated statement(s) are executed, after which control288        passes to the next statement after <code class="literal">END IF</code>.289        (Any subsequent <code class="literal">IF</code> conditions are <span class="emphasis"><em>not</em></span>290        tested.)  If none of the <code class="literal">IF</code> conditions is true,291        then the <code class="literal">ELSE</code> block (if any) is executed.292       </p><p>293        Here is an example:294 295</p><pre class="programlisting">296IF number = 0 THEN297    result := 'zero';298ELSIF number &gt; 0 THEN299    result := 'positive';300ELSIF number &lt; 0 THEN301    result := 'negative';302ELSE303    -- hmm, the only other possibility is that number is null304    result := 'NULL';305END IF;306</pre><p>307       </p><p>308        The key word <code class="literal">ELSIF</code> can also be spelled309        <code class="literal">ELSEIF</code>.310       </p><p>311        An alternative way of accomplishing the same task is to nest312        <code class="literal">IF-THEN-ELSE</code> statements, as in the313        following example:314 315</p><pre class="programlisting">316IF demo_row.sex = 'm' THEN317    pretty_sex := 'man';318ELSE319    IF demo_row.sex = 'f' THEN320        pretty_sex := 'woman';321    END IF;322END IF;323</pre><p>324       </p><p>325        However, this method requires writing a matching <code class="literal">END IF</code>326        for each <code class="literal">IF</code>, so it is much more cumbersome than327        using <code class="literal">ELSIF</code> when there are many alternatives.328       </p></div><div class="sect3" id="PLPGSQL-CONDITIONALS-SIMPLE-CASE"><div class="titlepage"><div><div><h4 class="title">43.6.4.4. Simple <code class="literal">CASE</code> <a href="#PLPGSQL-CONDITIONALS-SIMPLE-CASE" class="id_link">#</a></h4></div></div></div><pre class="synopsis">329CASE <em class="replaceable"><code>search-expression</code></em>330    WHEN <em class="replaceable"><code>expression</code></em> [<span class="optional">, <em class="replaceable"><code>expression</code></em> [<span class="optional"> ... </span>]</span>] THEN331      <em class="replaceable"><code>statements</code></em>332  [<span class="optional"> WHEN <em class="replaceable"><code>expression</code></em> [<span class="optional">, <em class="replaceable"><code>expression</code></em> [<span class="optional"> ... </span>]</span>] THEN333      <em class="replaceable"><code>statements</code></em>334    ... </span>]335  [<span class="optional"> ELSE336      <em class="replaceable"><code>statements</code></em> </span>]337END CASE;338</pre><p>339       The simple form of <code class="command">CASE</code> provides conditional execution340       based on equality of operands.  The <em class="replaceable"><code>search-expression</code></em>341       is evaluated (once) and successively compared to each342       <em class="replaceable"><code>expression</code></em> in the <code class="literal">WHEN</code> clauses.343       If a match is found, then the corresponding344       <em class="replaceable"><code>statements</code></em> are executed, and then control345       passes to the next statement after <code class="literal">END CASE</code>.  (Subsequent346       <code class="literal">WHEN</code> expressions are not evaluated.)  If no match is347       found, the <code class="literal">ELSE</code> <em class="replaceable"><code>statements</code></em> are348       executed; but if <code class="literal">ELSE</code> is not present, then a349       <code class="literal">CASE_NOT_FOUND</code> exception is raised.350      </p><p>351       Here is a simple example:352 353</p><pre class="programlisting">354CASE x355    WHEN 1, 2 THEN356        msg := 'one or two';357    ELSE358        msg := 'other value than one or two';359END CASE;360</pre><p>361      </p></div><div class="sect3" id="PLPGSQL-CONDITIONALS-SEARCHED-CASE"><div class="titlepage"><div><div><h4 class="title">43.6.4.5. Searched <code class="literal">CASE</code> <a href="#PLPGSQL-CONDITIONALS-SEARCHED-CASE" class="id_link">#</a></h4></div></div></div><pre class="synopsis">362CASE363    WHEN <em class="replaceable"><code>boolean-expression</code></em> THEN364      <em class="replaceable"><code>statements</code></em>365  [<span class="optional"> WHEN <em class="replaceable"><code>boolean-expression</code></em> THEN366      <em class="replaceable"><code>statements</code></em>367    ... </span>]368  [<span class="optional"> ELSE369      <em class="replaceable"><code>statements</code></em> </span>]370END CASE;371</pre><p>372       The searched form of <code class="command">CASE</code> provides conditional execution373       based on truth of Boolean expressions.  Each <code class="literal">WHEN</code> clause's374       <em class="replaceable"><code>boolean-expression</code></em> is evaluated in turn,375       until one is found that yields <code class="literal">true</code>.  Then the376       corresponding <em class="replaceable"><code>statements</code></em> are executed, and377       then control passes to the next statement after <code class="literal">END CASE</code>.378       (Subsequent <code class="literal">WHEN</code> expressions are not evaluated.)379       If no true result is found, the <code class="literal">ELSE</code>380       <em class="replaceable"><code>statements</code></em> are executed;381       but if <code class="literal">ELSE</code> is not present, then a382       <code class="literal">CASE_NOT_FOUND</code> exception is raised.383      </p><p>384       Here is an example:385 386</p><pre class="programlisting">387CASE388    WHEN x BETWEEN 0 AND 10 THEN389        msg := 'value is between zero and ten';390    WHEN x BETWEEN 11 AND 20 THEN391        msg := 'value is between eleven and twenty';392END CASE;393</pre><p>394      </p><p>395       This form of <code class="command">CASE</code> is entirely equivalent to396       <code class="literal">IF-THEN-ELSIF</code>, except for the rule that reaching397       an omitted <code class="literal">ELSE</code> clause results in an error rather398       than doing nothing.399      </p></div></div><div class="sect2" id="PLPGSQL-CONTROL-STRUCTURES-LOOPS"><div class="titlepage"><div><div><h3 class="title">43.6.5. Simple Loops <a href="#PLPGSQL-CONTROL-STRUCTURES-LOOPS" class="id_link">#</a></h3></div></div></div><a id="id-1.8.8.8.7.2" class="indexterm"></a><p>400     With the <code class="literal">LOOP</code>, <code class="literal">EXIT</code>,401     <code class="literal">CONTINUE</code>, <code class="literal">WHILE</code>, <code class="literal">FOR</code>,402     and <code class="literal">FOREACH</code> statements, you can arrange for your403     <span class="application">PL/pgSQL</span> function to repeat a series of commands.404    </p><div class="sect3" id="PLPGSQL-CONTROL-STRUCTURES-LOOPS-LOOP"><div class="titlepage"><div><div><h4 class="title">43.6.5.1. <code class="literal">LOOP</code> <a href="#PLPGSQL-CONTROL-STRUCTURES-LOOPS-LOOP" class="id_link">#</a></h4></div></div></div><pre class="synopsis">405[<span class="optional"> &lt;&lt;<em class="replaceable"><code>label</code></em>&gt;&gt; </span>]406LOOP407    <em class="replaceable"><code>statements</code></em>408END LOOP [<span class="optional"> <em class="replaceable"><code>label</code></em> </span>];409</pre><p>410      <code class="literal">LOOP</code> defines an unconditional loop that is repeated411      indefinitely until terminated by an <code class="literal">EXIT</code> or412      <code class="command">RETURN</code> statement.  The optional413      <em class="replaceable"><code>label</code></em> can be used by <code class="literal">EXIT</code>414      and <code class="literal">CONTINUE</code> statements within nested loops to415      specify which loop those statements refer to.416     </p></div><div class="sect3" id="PLPGSQL-CONTROL-STRUCTURES-LOOPS-EXIT"><div class="titlepage"><div><div><h4 class="title">43.6.5.2. <code class="literal">EXIT</code> <a href="#PLPGSQL-CONTROL-STRUCTURES-LOOPS-EXIT" class="id_link">#</a></h4></div></div></div><a id="id-1.8.8.8.7.5.2" class="indexterm"></a><pre class="synopsis">417EXIT [<span class="optional"> <em class="replaceable"><code>label</code></em> </span>] [<span class="optional"> WHEN <em class="replaceable"><code>boolean-expression</code></em> </span>];418</pre><p>419        If no <em class="replaceable"><code>label</code></em> is given, the innermost420        loop is terminated and the statement following <code class="literal">END421        LOOP</code> is executed next.  If <em class="replaceable"><code>label</code></em>422        is given, it must be the label of the current or some outer423        level of nested loop or block. Then the named loop or block is424        terminated and control continues with the statement after the425        loop's/block's corresponding <code class="literal">END</code>.426       </p><p>427        If <code class="literal">WHEN</code> is specified, the loop exit occurs only if428        <em class="replaceable"><code>boolean-expression</code></em> is true. Otherwise, control passes429        to the statement after <code class="literal">EXIT</code>.430       </p><p>431        <code class="literal">EXIT</code> can be used with all types of loops; it is432        not limited to use with unconditional loops.433       </p><p>434        When used with a435        <code class="literal">BEGIN</code> block, <code class="literal">EXIT</code> passes436        control to the next statement after the end of the block.437        Note that a label must be used for this purpose; an unlabeled438        <code class="literal">EXIT</code> is never considered to match a439        <code class="literal">BEGIN</code> block.  (This is a change from440        pre-8.4 releases of <span class="productname">PostgreSQL</span>, which441        would allow an unlabeled <code class="literal">EXIT</code> to match442        a <code class="literal">BEGIN</code> block.)443       </p><p>444        Examples:445</p><pre class="programlisting">446LOOP447    -- some computations448    IF count &gt; 0 THEN449        EXIT;  -- exit loop450    END IF;451END LOOP;452 453LOOP454    -- some computations455    EXIT WHEN count &gt; 0;  -- same result as previous example456END LOOP;457 458&lt;&lt;ablock&gt;&gt;459BEGIN460    -- some computations461    IF stocks &gt; 100000 THEN462        EXIT ablock;  -- causes exit from the BEGIN block463    END IF;464    -- computations here will be skipped when stocks &gt; 100000465END;466</pre><p>467       </p></div><div class="sect3" id="PLPGSQL-CONTROL-STRUCTURES-LOOPS-CONTINUE"><div class="titlepage"><div><div><h4 class="title">43.6.5.3. <code class="literal">CONTINUE</code> <a href="#PLPGSQL-CONTROL-STRUCTURES-LOOPS-CONTINUE" class="id_link">#</a></h4></div></div></div><a id="id-1.8.8.8.7.6.2" class="indexterm"></a><pre class="synopsis">468CONTINUE [<span class="optional"> <em class="replaceable"><code>label</code></em> </span>] [<span class="optional"> WHEN <em class="replaceable"><code>boolean-expression</code></em> </span>];469</pre><p>470        If no <em class="replaceable"><code>label</code></em> is given, the next iteration of471        the innermost loop is begun. That is, all statements remaining472        in the loop body are skipped, and control returns473        to the loop control expression (if any) to determine whether474        another loop iteration is needed.475        If <em class="replaceable"><code>label</code></em> is present, it476        specifies the label of the loop whose execution will be477        continued.478       </p><p>479        If <code class="literal">WHEN</code> is specified, the next iteration of the480        loop is begun only if <em class="replaceable"><code>boolean-expression</code></em> is481        true. Otherwise, control passes to the statement after482        <code class="literal">CONTINUE</code>.483       </p><p>484        <code class="literal">CONTINUE</code> can be used with all types of loops; it485        is not limited to use with unconditional loops.486       </p><p>487        Examples:488</p><pre class="programlisting">489LOOP490    -- some computations491    EXIT WHEN count &gt; 100;492    CONTINUE WHEN count &lt; 50;493    -- some computations for count IN [50 .. 100]494END LOOP;495</pre><p>496       </p></div><div class="sect3" id="PLPGSQL-CONTROL-STRUCTURES-LOOPS-WHILE"><div class="titlepage"><div><div><h4 class="title">43.6.5.4. <code class="literal">WHILE</code> <a href="#PLPGSQL-CONTROL-STRUCTURES-LOOPS-WHILE" class="id_link">#</a></h4></div></div></div><a id="id-1.8.8.8.7.7.2" class="indexterm"></a><pre class="synopsis">497[<span class="optional"> &lt;&lt;<em class="replaceable"><code>label</code></em>&gt;&gt; </span>]498WHILE <em class="replaceable"><code>boolean-expression</code></em> LOOP499    <em class="replaceable"><code>statements</code></em>500END LOOP [<span class="optional"> <em class="replaceable"><code>label</code></em> </span>];501</pre><p>502        The <code class="literal">WHILE</code> statement repeats a503        sequence of statements so long as the504        <em class="replaceable"><code>boolean-expression</code></em>505        evaluates to true.  The expression is checked just before506        each entry to the loop body.507       </p><p>508        For example:509</p><pre class="programlisting">510WHILE amount_owed &gt; 0 AND gift_certificate_balance &gt; 0 LOOP511    -- some computations here512END LOOP;513 514WHILE NOT done LOOP515    -- some computations here516END LOOP;517</pre><p>518       </p></div><div class="sect3" id="PLPGSQL-INTEGER-FOR"><div class="titlepage"><div><div><h4 class="title">43.6.5.5. <code class="literal">FOR</code> (Integer Variant) <a href="#PLPGSQL-INTEGER-FOR" class="id_link">#</a></h4></div></div></div><pre class="synopsis">519[<span class="optional"> &lt;&lt;<em class="replaceable"><code>label</code></em>&gt;&gt; </span>]520FOR <em class="replaceable"><code>name</code></em> IN [<span class="optional"> REVERSE </span>] <em class="replaceable"><code>expression</code></em> .. <em class="replaceable"><code>expression</code></em> [<span class="optional"> BY <em class="replaceable"><code>expression</code></em> </span>] LOOP521    <em class="replaceable"><code>statements</code></em>522END LOOP [<span class="optional"> <em class="replaceable"><code>label</code></em> </span>];523</pre><p>524        This form of <code class="literal">FOR</code> creates a loop that iterates over a range525        of integer values. The variable526        <em class="replaceable"><code>name</code></em> is automatically defined as type527        <code class="type">integer</code> and exists only inside the loop (any existing528        definition of the variable name is ignored within the loop).529        The two expressions giving530        the lower and upper bound of the range are evaluated once when entering531        the loop. If the <code class="literal">BY</code> clause isn't specified the iteration532        step is 1, otherwise it's the value specified in the <code class="literal">BY</code>533        clause, which again is evaluated once on loop entry.534        If <code class="literal">REVERSE</code> is specified then the step value is535        subtracted, rather than added, after each iteration.536       </p><p>537        Some examples of integer <code class="literal">FOR</code> loops:538</p><pre class="programlisting">539FOR i IN 1..10 LOOP540    -- i will take on the values 1,2,3,4,5,6,7,8,9,10 within the loop541END LOOP;542 543FOR i IN REVERSE 10..1 LOOP544    -- i will take on the values 10,9,8,7,6,5,4,3,2,1 within the loop545END LOOP;546 547FOR i IN REVERSE 10..1 BY 2 LOOP548    -- i will take on the values 10,8,6,4,2 within the loop549END LOOP;550</pre><p>551       </p><p>552        If the lower bound is greater than the upper bound (or less than,553        in the <code class="literal">REVERSE</code> case), the loop body is not554        executed at all.  No error is raised.555       </p><p>556        If a <em class="replaceable"><code>label</code></em> is attached to the557        <code class="literal">FOR</code> loop then the integer loop variable can be558        referenced with a qualified name, using that559        <em class="replaceable"><code>label</code></em>.560       </p></div></div><div class="sect2" id="PLPGSQL-RECORDS-ITERATING"><div class="titlepage"><div><div><h3 class="title">43.6.6. Looping through Query Results <a href="#PLPGSQL-RECORDS-ITERATING" class="id_link">#</a></h3></div></div></div><p>561     Using a different type of <code class="literal">FOR</code> loop, you can iterate through562     the results of a query and manipulate that data563     accordingly. The syntax is:564</p><pre class="synopsis">565[<span class="optional"> &lt;&lt;<em class="replaceable"><code>label</code></em>&gt;&gt; </span>]566FOR <em class="replaceable"><code>target</code></em> IN <em class="replaceable"><code>query</code></em> LOOP567    <em class="replaceable"><code>statements</code></em>568END LOOP [<span class="optional"> <em class="replaceable"><code>label</code></em> </span>];569</pre><p>570     The <em class="replaceable"><code>target</code></em> is a record variable, row variable,571     or comma-separated list of scalar variables.572     The <em class="replaceable"><code>target</code></em> is successively assigned each row573     resulting from the <em class="replaceable"><code>query</code></em> and the loop body is574     executed for each row. Here is an example:575</p><pre class="programlisting">576CREATE FUNCTION refresh_mviews() RETURNS integer AS $$577DECLARE578    mviews RECORD;579BEGIN580    RAISE NOTICE 'Refreshing all materialized views...';581 582    FOR mviews IN583       SELECT n.nspname AS mv_schema,584              c.relname AS mv_name,585              pg_catalog.pg_get_userbyid(c.relowner) AS owner586         FROM pg_catalog.pg_class c587    LEFT JOIN pg_catalog.pg_namespace n ON (n.oid = c.relnamespace)588        WHERE c.relkind = 'm'589     ORDER BY 1590    LOOP591 592        -- Now "mviews" has one record with information about the materialized view593 594        RAISE NOTICE 'Refreshing materialized view %.% (owner: %)...',595                     quote_ident(mviews.mv_schema),596                     quote_ident(mviews.mv_name),597                     quote_ident(mviews.owner);598        EXECUTE format('REFRESH MATERIALIZED VIEW %I.%I', mviews.mv_schema, mviews.mv_name);599    END LOOP;600 601    RAISE NOTICE 'Done refreshing materialized views.';602    RETURN 1;603END;604$$ LANGUAGE plpgsql;605</pre><p>606 607     If the loop is terminated by an <code class="literal">EXIT</code> statement, the last608     assigned row value is still accessible after the loop.609    </p><p>610     The <em class="replaceable"><code>query</code></em> used in this type of <code class="literal">FOR</code>611     statement can be any SQL command that returns rows to the caller:612     <code class="command">SELECT</code> is the most common case,613     but you can also use <code class="command">INSERT</code>, <code class="command">UPDATE</code>, or614     <code class="command">DELETE</code> with a <code class="literal">RETURNING</code> clause.  Some utility615     commands such as <code class="command">EXPLAIN</code> will work too.616    </p><p>617     <span class="application">PL/pgSQL</span> variables are replaced by query parameters,618     and the query plan is cached for possible re-use, as discussed in619     detail in <a class="xref" href="plpgsql-implementation.html#PLPGSQL-VAR-SUBST" title="43.11.1. Variable Substitution">Section 43.11.1</a> and620     <a class="xref" href="plpgsql-implementation.html#PLPGSQL-PLAN-CACHING" title="43.11.2. Plan Caching">Section 43.11.2</a>.621    </p><p>622     The <code class="literal">FOR-IN-EXECUTE</code> statement is another way to iterate over623     rows:624</p><pre class="synopsis">625[<span class="optional"> &lt;&lt;<em class="replaceable"><code>label</code></em>&gt;&gt; </span>]626FOR <em class="replaceable"><code>target</code></em> IN EXECUTE <em class="replaceable"><code>text_expression</code></em> [<span class="optional"> USING <em class="replaceable"><code>expression</code></em> [<span class="optional">, ... </span>] </span>] LOOP627    <em class="replaceable"><code>statements</code></em>628END LOOP [<span class="optional"> <em class="replaceable"><code>label</code></em> </span>];629</pre><p>630     This is like the previous form, except that the source query631     is specified as a string expression, which is evaluated and replanned632     on each entry to the <code class="literal">FOR</code> loop.  This allows the programmer to633     choose the speed of a preplanned query or the flexibility of a dynamic634     query, just as with a plain <code class="command">EXECUTE</code> statement.635     As with <code class="command">EXECUTE</code>, parameter values can be inserted636     into the dynamic command via <code class="literal">USING</code>.637    </p><p>638     Another way to specify the query whose results should be iterated639     through is to declare it as a cursor.  This is described in640     <a class="xref" href="plpgsql-cursors.html#PLPGSQL-CURSOR-FOR-LOOP" title="43.7.4. Looping through a Cursor's Result">Section 43.7.4</a>.641    </p></div><div class="sect2" id="PLPGSQL-FOREACH-ARRAY"><div class="titlepage"><div><div><h3 class="title">43.6.7. Looping through Arrays <a href="#PLPGSQL-FOREACH-ARRAY" class="id_link">#</a></h3></div></div></div><p>642     The <code class="literal">FOREACH</code> loop is much like a <code class="literal">FOR</code> loop,643     but instead of iterating through the rows returned by an SQL query,644     it iterates through the elements of an array value.645     (In general, <code class="literal">FOREACH</code> is meant for looping through646     components of a composite-valued expression; variants for looping647     through composites besides arrays may be added in future.)648     The <code class="literal">FOREACH</code> statement to loop over an array is:649 650</p><pre class="synopsis">651[<span class="optional"> &lt;&lt;<em class="replaceable"><code>label</code></em>&gt;&gt; </span>]652FOREACH <em class="replaceable"><code>target</code></em> [<span class="optional"> SLICE <em class="replaceable"><code>number</code></em> </span>] IN ARRAY <em class="replaceable"><code>expression</code></em> LOOP653    <em class="replaceable"><code>statements</code></em>654END LOOP [<span class="optional"> <em class="replaceable"><code>label</code></em> </span>];655</pre><p>656    </p><p>657     Without <code class="literal">SLICE</code>, or if <code class="literal">SLICE 0</code> is specified,658     the loop iterates through individual elements of the array produced659     by evaluating the <em class="replaceable"><code>expression</code></em>.660     The <em class="replaceable"><code>target</code></em> variable is assigned each661     element value in sequence, and the loop body is executed for each element.662     Here is an example of looping through the elements of an integer663     array:664 665</p><pre class="programlisting">666CREATE FUNCTION sum(int[]) RETURNS int8 AS $$667DECLARE668  s int8 := 0;669  x int;670BEGIN671  FOREACH x IN ARRAY $1672  LOOP673    s := s + x;674  END LOOP;675  RETURN s;676END;677$$ LANGUAGE plpgsql;678</pre><p>679 680     The elements are visited in storage order, regardless of the number of681     array dimensions.  Although the <em class="replaceable"><code>target</code></em> is682     usually just a single variable, it can be a list of variables when683     looping through an array of composite values (records).  In that case,684     for each array element, the variables are assigned from successive685     columns of the composite value.686    </p><p>687     With a positive <code class="literal">SLICE</code> value, <code class="literal">FOREACH</code>688     iterates through slices of the array rather than single elements.689     The <code class="literal">SLICE</code> value must be an integer constant not larger690     than the number of dimensions of the array.  The691     <em class="replaceable"><code>target</code></em> variable must be an array,692     and it receives successive slices of the array value, where each slice693     is of the number of dimensions specified by <code class="literal">SLICE</code>.694     Here is an example of iterating through one-dimensional slices:695 696</p><pre class="programlisting">697CREATE FUNCTION scan_rows(int[]) RETURNS void AS $$698DECLARE699  x int[];700BEGIN701  FOREACH x SLICE 1 IN ARRAY $1702  LOOP703    RAISE NOTICE 'row = %', x;704  END LOOP;705END;706$$ LANGUAGE plpgsql;707 708SELECT scan_rows(ARRAY[[1,2,3],[4,5,6],[7,8,9],[10,11,12]]);709 710NOTICE:  row = {1,2,3}711NOTICE:  row = {4,5,6}712NOTICE:  row = {7,8,9}713NOTICE:  row = {10,11,12}714</pre><p>715    </p></div><div class="sect2" id="PLPGSQL-ERROR-TRAPPING"><div class="titlepage"><div><div><h3 class="title">43.6.8. Trapping Errors <a href="#PLPGSQL-ERROR-TRAPPING" class="id_link">#</a></h3></div></div></div><a id="id-1.8.8.8.10.2" class="indexterm"></a><p>716     By default, any error occurring in a <span class="application">PL/pgSQL</span>717     function aborts execution of the function and the718     surrounding transaction.  You can trap errors and recover719     from them by using a <code class="command">BEGIN</code> block with an720     <code class="literal">EXCEPTION</code> clause.  The syntax is an extension of the721     normal syntax for a <code class="command">BEGIN</code> block:722 723</p><pre class="synopsis">724[<span class="optional"> &lt;&lt;<em class="replaceable"><code>label</code></em>&gt;&gt; </span>]725[<span class="optional"> DECLARE726    <em class="replaceable"><code>declarations</code></em> </span>]727BEGIN728    <em class="replaceable"><code>statements</code></em>729EXCEPTION730    WHEN <em class="replaceable"><code>condition</code></em> [<span class="optional"> OR <em class="replaceable"><code>condition</code></em> ... </span>] THEN731        <em class="replaceable"><code>handler_statements</code></em>732    [<span class="optional"> WHEN <em class="replaceable"><code>condition</code></em> [<span class="optional"> OR <em class="replaceable"><code>condition</code></em> ... </span>] THEN733          <em class="replaceable"><code>handler_statements</code></em>734      ... </span>]735END;736</pre><p>737    </p><p>738     If no error occurs, this form of block simply executes all the739     <em class="replaceable"><code>statements</code></em>, and then control passes740     to the next statement after <code class="literal">END</code>.  But if an error741     occurs within the <em class="replaceable"><code>statements</code></em>, further742     processing of the <em class="replaceable"><code>statements</code></em> is743     abandoned, and control passes to the <code class="literal">EXCEPTION</code> list.744     The list is searched for the first <em class="replaceable"><code>condition</code></em>745     matching the error that occurred.  If a match is found, the746     corresponding <em class="replaceable"><code>handler_statements</code></em> are747     executed, and then control passes to the next statement after748     <code class="literal">END</code>.  If no match is found, the error propagates out749     as though the <code class="literal">EXCEPTION</code> clause were not there at all:750     the error can be caught by an enclosing block with751     <code class="literal">EXCEPTION</code>, or if there is none it aborts processing752     of the function.753    </p><p>754     The <em class="replaceable"><code>condition</code></em> names can be any of755     those shown in <a class="xref" href="errcodes-appendix.html" title="Appendix A. PostgreSQL Error Codes">Appendix A</a>.  A category756     name matches any error within its category.  The special757     condition name <code class="literal">OTHERS</code> matches every error type except758     <code class="literal">QUERY_CANCELED</code> and <code class="literal">ASSERT_FAILURE</code>.759     (It is possible, but often unwise, to trap those two error types760     by name.)  Condition names are761     not case-sensitive.  Also, an error condition can be specified762     by <code class="literal">SQLSTATE</code> code; for example these are equivalent:763</p><pre class="programlisting">764WHEN division_by_zero THEN ...765WHEN SQLSTATE '22012' THEN ...766</pre><p>767    </p><p>768     If a new error occurs within the selected769     <em class="replaceable"><code>handler_statements</code></em>, it cannot be caught770     by this <code class="literal">EXCEPTION</code> clause, but is propagated out.771     A surrounding <code class="literal">EXCEPTION</code> clause could catch it.772    </p><p>773     When an error is caught by an <code class="literal">EXCEPTION</code> clause,774     the local variables of the <span class="application">PL/pgSQL</span> function775     remain as they were when the error occurred, but all changes776     to persistent database state within the block are rolled back.777     As an example, consider this fragment:778 779</p><pre class="programlisting">780INSERT INTO mytab(firstname, lastname) VALUES('Tom', 'Jones');781BEGIN782    UPDATE mytab SET firstname = 'Joe' WHERE lastname = 'Jones';783    x := x + 1;784    y := x / 0;785EXCEPTION786    WHEN division_by_zero THEN787        RAISE NOTICE 'caught division_by_zero';788        RETURN x;789END;790</pre><p>791 792     When control reaches the assignment to <code class="literal">y</code>, it will793     fail with a <code class="literal">division_by_zero</code> error.  This will be caught by794     the <code class="literal">EXCEPTION</code> clause.  The value returned in the795     <code class="command">RETURN</code> statement will be the incremented value of796     <code class="literal">x</code>, but the effects of the <code class="command">UPDATE</code> command will797     have been rolled back.  The <code class="command">INSERT</code> command preceding the798     block is not rolled back, however, so the end result is that the database799     contains <code class="literal">Tom Jones</code> not <code class="literal">Joe Jones</code>.800    </p><div class="tip"><h3 class="title">Tip</h3><p>801      A block containing an <code class="literal">EXCEPTION</code> clause is significantly802      more expensive to enter and exit than a block without one.  Therefore,803      don't use <code class="literal">EXCEPTION</code> without need.804     </p></div><div class="example" id="PLPGSQL-UPSERT-EXAMPLE"><p class="title"><strong>Example 43.2. Exceptions with <code class="command">UPDATE</code>/<code class="command">INSERT</code></strong></p><div class="example-contents"><p>805 806    This example uses exception handling to perform either807    <code class="command">UPDATE</code> or <code class="command">INSERT</code>, as appropriate.  It is808    recommended that applications use <code class="command">INSERT</code> with809    <code class="literal">ON CONFLICT DO UPDATE</code> rather than actually using810    this pattern.  This example serves primarily to illustrate use of811    <span class="application">PL/pgSQL</span> control flow structures:812 813</p><pre class="programlisting">814CREATE TABLE db (a INT PRIMARY KEY, b TEXT);815 816CREATE FUNCTION merge_db(key INT, data TEXT) RETURNS VOID AS817$$818BEGIN819    LOOP820        -- first try to update the key821        UPDATE db SET b = data WHERE a = key;822        IF found THEN823            RETURN;824        END IF;825        -- not there, so try to insert the key826        -- if someone else inserts the same key concurrently,827        -- we could get a unique-key failure828        BEGIN829            INSERT INTO db(a,b) VALUES (key, data);830            RETURN;831        EXCEPTION WHEN unique_violation THEN832            -- Do nothing, and loop to try the UPDATE again.833        END;834    END LOOP;835END;836$$837LANGUAGE plpgsql;838 839SELECT merge_db(1, 'david');840SELECT merge_db(1, 'dennis');841</pre><p>842 843     This coding assumes the <code class="literal">unique_violation</code> error is caused by844     the <code class="command">INSERT</code>, and not by, say, an <code class="command">INSERT</code> in a845     trigger function on the table.  It might also misbehave if there is846     more than one unique index on the table, since it will retry the847     operation regardless of which index caused the error.848     More safety could be had by using the849     features discussed next to check that the trapped error was the one850     expected.851    </p></div></div><br class="example-break" /><div class="sect3" id="PLPGSQL-EXCEPTION-DIAGNOSTICS"><div class="titlepage"><div><div><h4 class="title">43.6.8.1. Obtaining Information about an Error <a href="#PLPGSQL-EXCEPTION-DIAGNOSTICS" class="id_link">#</a></h4></div></div></div><p>852     Exception handlers frequently need to identify the specific error that853     occurred.  There are two ways to get information about the current854     exception in <span class="application">PL/pgSQL</span>: special variables and the855     <code class="command">GET STACKED DIAGNOSTICS</code> command.856    </p><p>857     Within an exception handler, the special variable858     <code class="varname">SQLSTATE</code> contains the error code that corresponds to859     the exception that was raised (refer to <a class="xref" href="errcodes-appendix.html#ERRCODES-TABLE" title="Table A.1. PostgreSQL Error Codes">Table A.1</a>860     for a list of possible error codes). The special variable861     <code class="varname">SQLERRM</code> contains the error message associated with the862     exception. These variables are undefined outside exception handlers.863    </p><p>864     Within an exception handler, one may also retrieve865     information about the current exception by using the866     <code class="command">GET STACKED DIAGNOSTICS</code> command, which has the form:867 868</p><pre class="synopsis">869GET STACKED DIAGNOSTICS <em class="replaceable"><code>variable</code></em> { = | := } <em class="replaceable"><code>item</code></em> [<span class="optional"> , ... </span>];870</pre><p>871 872     Each <em class="replaceable"><code>item</code></em> is a key word identifying a status873     value to be assigned to the specified <em class="replaceable"><code>variable</code></em>874     (which should be of the right data type to receive it).  The currently875     available status items are shown876     in <a class="xref" href="plpgsql-control-structures.html#PLPGSQL-EXCEPTION-DIAGNOSTICS-VALUES" title="Table 43.2. Error Diagnostics Items">Table 43.2</a>.877    </p><div class="table" id="PLPGSQL-EXCEPTION-DIAGNOSTICS-VALUES"><p class="title"><strong>Table 43.2. Error Diagnostics Items</strong></p><div class="table-contents"><table class="table" summary="Error Diagnostics Items" border="1"><colgroup><col class="col1" /><col class="col2" /><col class="col3" /></colgroup><thead><tr><th>Name</th><th>Type</th><th>Description</th></tr></thead><tbody><tr><td><code class="literal">RETURNED_SQLSTATE</code></td><td><code class="type">text</code></td><td>the SQLSTATE error code of the exception</td></tr><tr><td><code class="literal">COLUMN_NAME</code></td><td><code class="type">text</code></td><td>the name of the column related to exception</td></tr><tr><td><code class="literal">CONSTRAINT_NAME</code></td><td><code class="type">text</code></td><td>the name of the constraint related to exception</td></tr><tr><td><code class="literal">PG_DATATYPE_NAME</code></td><td><code class="type">text</code></td><td>the name of the data type related to exception</td></tr><tr><td><code class="literal">MESSAGE_TEXT</code></td><td><code class="type">text</code></td><td>the text of the exception's primary message</td></tr><tr><td><code class="literal">TABLE_NAME</code></td><td><code class="type">text</code></td><td>the name of the table related to exception</td></tr><tr><td><code class="literal">SCHEMA_NAME</code></td><td><code class="type">text</code></td><td>the name of the schema related to exception</td></tr><tr><td><code class="literal">PG_EXCEPTION_DETAIL</code></td><td><code class="type">text</code></td><td>the text of the exception's detail message, if any</td></tr><tr><td><code class="literal">PG_EXCEPTION_HINT</code></td><td><code class="type">text</code></td><td>the text of the exception's hint message, if any</td></tr><tr><td><code class="literal">PG_EXCEPTION_CONTEXT</code></td><td><code class="type">text</code></td><td>line(s) of text describing the call stack at the time of the878          exception (see <a class="xref" href="plpgsql-control-structures.html#PLPGSQL-CALL-STACK" title="43.6.9. Obtaining Execution Location Information">Section 43.6.9</a>)</td></tr></tbody></table></div></div><br class="table-break" /><p>879     If the exception did not set a value for an item, an empty string880     will be returned.881    </p><p>882     Here is an example:883</p><pre class="programlisting">884DECLARE885  text_var1 text;886  text_var2 text;887  text_var3 text;888BEGIN889  -- some processing which might cause an exception890  ...891EXCEPTION WHEN OTHERS THEN892  GET STACKED DIAGNOSTICS text_var1 = MESSAGE_TEXT,893                          text_var2 = PG_EXCEPTION_DETAIL,894                          text_var3 = PG_EXCEPTION_HINT;895END;896</pre><p>897    </p></div></div><div class="sect2" id="PLPGSQL-CALL-STACK"><div class="titlepage"><div><div><h3 class="title">43.6.9. Obtaining Execution Location Information <a href="#PLPGSQL-CALL-STACK" class="id_link">#</a></h3></div></div></div><p>898    The <code class="command">GET DIAGNOSTICS</code> command, previously described899    in <a class="xref" href="plpgsql-statements.html#PLPGSQL-STATEMENTS-DIAGNOSTICS" title="43.5.5. Obtaining the Result Status">Section 43.5.5</a>, retrieves information900    about current execution state (whereas the <code class="command">GET STACKED901    DIAGNOSTICS</code> command discussed above reports information about902    the execution state as of a previous error).  Its <code class="literal">PG_CONTEXT</code>903    status item is useful for identifying the current execution904    location.  <code class="literal">PG_CONTEXT</code> returns a text string with line(s)905    of text describing the call stack.  The first line refers to the current906    function and currently executing <code class="command">GET DIAGNOSTICS</code>907    command.  The second and any subsequent lines refer to calling functions908    further up the call stack.  For example:909 910</p><pre class="programlisting">911CREATE OR REPLACE FUNCTION outer_func() RETURNS integer AS $$912BEGIN913  RETURN inner_func();914END;915$$ LANGUAGE plpgsql;916 917CREATE OR REPLACE FUNCTION inner_func() RETURNS integer AS $$918DECLARE919  stack text;920BEGIN921  GET DIAGNOSTICS stack = PG_CONTEXT;922  RAISE NOTICE E'--- Call Stack ---\n%', stack;923  RETURN 1;924END;925$$ LANGUAGE plpgsql;926 927SELECT outer_func();928 929NOTICE:  --- Call Stack ---930PL/pgSQL function inner_func() line 5 at GET DIAGNOSTICS931PL/pgSQL function outer_func() line 3 at RETURN932CONTEXT:  PL/pgSQL function outer_func() line 3 at RETURN933 outer_func934 ------------935           1936(1 row)937</pre><p>938 939   </p><p>940    <code class="literal">GET STACKED DIAGNOSTICS ... PG_EXCEPTION_CONTEXT</code>941    returns the same sort of stack trace, but describing the location942    at which an error was detected, rather than the current location.943   </p></div></div><div class="navfooter"><hr /><table width="100%" summary="Navigation footer"><tr><td width="40%" align="left"><a accesskey="p" href="plpgsql-statements.html" title="43.5. Basic Statements">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-cursors.html" title="43.7. Cursors">Next</a></td></tr><tr><td width="40%" align="left" valign="top">43.5. Basic Statements </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.7. Cursors</td></tr></table></div></body></html>
codekingpro/portable-devtools · Team Ai