begriffs open source - ai-pg/blob - full-docs/html/plpgsql-control-structures.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>41.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="41.5. Basic Statements" /><link rel="next" href="plpgsql-cursors.html" title="41.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">41.6. Control Structures</th></tr><tr><td width="10%" align="left"><a accesskey="p" href="plpgsql-statements.html" title="41.5. Basic Statements">Prev</a> </td><td width="10%" align="left"><a accesskey="u" href="plpgsql.html" title="Chapter 41. PL/pgSQL — SQL Procedural Language">Up</a></td><th width="60%" align="center">Chapter 41. <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 18.0 Documentation">Home</a></td><td width="10%" align="right"> <a accesskey="n" href="plpgsql-cursors.html" title="41.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">41.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">41.6.1. Returning from a Function</a></span></dt><dt><span class="sect2"><a href="plpgsql-control-structures.html#PLPGSQL-STATEMENTS-RETURNING-PROCEDURE">41.6.2. Returning from a Procedure</a></span></dt><dt><span class="sect2"><a href="plpgsql-control-structures.html#PLPGSQL-STATEMENTS-CALLING-PROCEDURE">41.6.3. Calling a Procedure</a></span></dt><dt><span class="sect2"><a href="plpgsql-control-structures.html#PLPGSQL-CONDITIONALS">41.6.4. Conditionals</a></span></dt><dt><span class="sect2"><a href="plpgsql-control-structures.html#PLPGSQL-CONTROL-STRUCTURES-LOOPS">41.6.5. Simple Loops</a></span></dt><dt><span class="sect2"><a href="plpgsql-control-structures.html#PLPGSQL-RECORDS-ITERATING">41.6.6. Looping through Query Results</a></span></dt><dt><span class="sect2"><a href="plpgsql-control-structures.html#PLPGSQL-FOREACH-ARRAY">41.6.7. Looping through Arrays</a></span></dt><dt><span class="sect2"><a href="plpgsql-control-structures.html#PLPGSQL-ERROR-TRAPPING">41.6.8. Trapping Errors</a></span></dt><dt><span class="sect2"><a href="plpgsql-control-structures.html#PLPGSQL-CALL-STACK">41.6.9. Obtaining Execution Location Information</a></span></dt></dl></div><p>
   3     Control structures are probably the most useful (and
   4     important) part of <span class="application">PL/pgSQL</span>. With
   5     <span class="application">PL/pgSQL</span>'s control structures,
   6     you can manipulate <span class="productname">PostgreSQL</span> data in a very
   7     flexible and powerful way.
   8    </p><div class="sect2" id="PLPGSQL-STATEMENTS-RETURNING"><div class="titlepage"><div><div><h3 class="title">41.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 data
  10      from a function: <code class="command">RETURN</code> and <code class="command">RETURN
  11      NEXT</code>.
  12     </p><div class="sect3" id="PLPGSQL-STATEMENTS-RETURNING-RETURN"><div class="titlepage"><div><div><h4 class="title">41.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">
  13 RETURN <em class="replaceable"><code>expression</code></em>;
  14 </pre><p>
  15       <code class="command">RETURN</code> with an expression terminates the
  16       function and returns the value of
  17       <em class="replaceable"><code>expression</code></em> to the caller.  This form
  18       is used for <span class="application">PL/pgSQL</span> functions that do
  19       not return a set.
  20      </p><p>
  21       In a function that returns a scalar type, the expression's result will
  22       automatically be cast into the function's return type as described for
  23       assignments.  But to return a composite (row) value, you must write an
  24       expression delivering exactly the requested column set.  This may
  25       require use of explicit casting.
  26      </p><p>
  27       If you declared the function with output parameters, write just
  28       <code class="command">RETURN</code> with no expression.  The current values
  29       of the output parameter variables will be returned.
  30      </p><p>
  31       If you declared the function to return <code class="type">void</code>, a
  32       <code class="command">RETURN</code> statement can be used to exit the function
  33       early; but do not write an expression following
  34       <code class="command">RETURN</code>.
  35      </p><p>
  36       The return value of a function cannot be left undefined. If
  37       control reaches the end of the top-level block of the function
  38       without hitting a <code class="command">RETURN</code> statement, a run-time
  39       error will occur.  This restriction does not apply to functions
  40       with output parameters and functions returning <code class="type">void</code>,
  41       however.  In those cases a <code class="command">RETURN</code> statement is
  42       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 type
  48 RETURN 1 + 2;
  49 RETURN scalar_var;
  50
  51 -- functions returning a composite type
  52 RETURN composite_type_var;
  53 RETURN (1, 2, 'three'::text);  -- must cast columns to correct types
  54 </pre><p>
  55      </p></div><div class="sect3" id="PLPGSQL-STATEMENTS-RETURNING-RETURN-NEXT"><div class="titlepage"><div><div><h4 class="title">41.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">
  56 RETURN NEXT <em class="replaceable"><code>expression</code></em>;
  57 RETURN QUERY <em class="replaceable"><code>query</code></em>;
  58 RETURN 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 return
  61       <code class="literal">SETOF <em class="replaceable"><code>sometype</code></em></code>, the procedure
  62       to follow is slightly different.  In that case, the individual
  63       items to return are specified by a sequence of <code class="command">RETURN
  64       NEXT</code> or <code class="command">RETURN QUERY</code> commands, and
  65       then a final <code class="command">RETURN</code> command with no argument
  66       is used to indicate that the function has finished executing.
  67       <code class="command">RETURN NEXT</code> can be used with both scalar and
  68       composite data types; with a composite result type, an entire
  69       <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 executing
  71       a query to the function's result set. <code class="command">RETURN
  72       NEXT</code> and <code class="command">RETURN QUERY</code> can be freely
  73       intermixed in a single set-returning function, in which case
  74       their results will be concatenated.
  75      </p><p>
  76       <code class="command">RETURN NEXT</code> and <code class="command">RETURN
  77       QUERY</code> do not actually return from the function —
  78       they simply append zero or more rows to the function's result
  79       set.  Execution then continues with the next statement in the
  80       <span class="application">PL/pgSQL</span> function.  As successive
  81       <code class="command">RETURN NEXT</code> or <code class="command">RETURN
  82       QUERY</code> commands are executed, the result set is built
  83       up.  A final <code class="command">RETURN</code>, which should have no
  84       argument, causes control to exit the function (or you can just
  85       let control reach the end of the function).
  86      </p><p>
  87       <code class="command">RETURN QUERY</code> has a variant
  88       <code class="command">RETURN QUERY EXECUTE</code>, which specifies the
  89       query to be executed dynamically.  Parameter expressions can
  90       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 just
  94       <code class="command">RETURN NEXT</code> with no expression.  On each
  95       execution, the current values of the output parameter
  96       variable(s) will be saved for eventual return as a row of the
  97       result.  Note that you must declare the function as returning
  98       <code class="literal">SETOF record</code> when there are multiple output
  99       parameters, or <code class="literal">SETOF <em class="replaceable"><code>sometype</code></em></code>
 100       when there is just one output parameter of type
 101       <em class="replaceable"><code>sometype</code></em>, in order to create a set-returning
 102       function with output parameters.
 103      </p><p>
 104       Here is an example of a function using <code class="command">RETURN
 105       NEXT</code>:
 106
 107 </p><pre class="programlisting">
 108 CREATE TABLE foo (fooid INT, foosubid INT, fooname TEXT);
 109 INSERT INTO foo VALUES (1, 2, 'three');
 110 INSERT INTO foo VALUES (4, 5, 'six');
 111
 112 CREATE OR REPLACE FUNCTION get_all_foo() RETURNS SETOF foo AS
 113 $BODY$
 114 DECLARE
 115     r foo%rowtype;
 116 BEGIN
 117     FOR r IN
 118         SELECT * FROM foo WHERE fooid &gt; 0
 119     LOOP
 120         -- can do some processing here
 121         RETURN NEXT r; -- return current row of SELECT
 122     END LOOP;
 123     RETURN;
 124 END;
 125 $BODY$
 126 LANGUAGE plpgsql;
 127
 128 SELECT * FROM get_all_foo();
 129 </pre><p>
 130      </p><p>
 131       Here is an example of a function using <code class="command">RETURN
 132       QUERY</code>:
 133
 134 </p><pre class="programlisting">
 135 CREATE FUNCTION get_available_flightid(date) RETURNS SETOF integer AS
 136 $BODY$
 137 BEGIN
 138     RETURN QUERY SELECT flightid
 139                    FROM flight
 140                   WHERE flightdate &gt;= $1
 141                     AND flightdate &lt; ($1 + 1);
 142
 143     -- Since execution is not finished, we can check whether rows were returned
 144     -- and raise exception if not.
 145     IF NOT FOUND THEN
 146         RAISE EXCEPTION 'No flight at %.', $1;
 147     END IF;
 148
 149     RETURN;
 150  END;
 151 $BODY$
 152 LANGUAGE plpgsql;
 153
 154 -- Returns available flights or raises exception if there are no
 155 -- available flights.
 156 SELECT * 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 set
 161        before returning from the function, as discussed above.  That
 162        means that if a <span class="application">PL/pgSQL</span> function produces a
 163        very large result set, performance might be poor: data will be
 164        written to disk to avoid memory exhaustion, but the function
 165        itself will not return until the entire result set has been
 166        generated.  A future version of <span class="application">PL/pgSQL</span> might
 167        allow users to define set-returning functions
 168        that do not have this limitation.  Currently, the point at
 169        which data begins being written to disk is controlled by the
 170        <a class="xref" href="runtime-config-resource.html#GUC-WORK-MEM">work_mem</a>
 171        configuration variable.  Administrators who have sufficient
 172        memory to store larger result sets in memory should consider
 173        increasing this parameter.
 174       </p></div></div></div><div class="sect2" id="PLPGSQL-STATEMENTS-RETURNING-PROCEDURE"><div class="titlepage"><div><div><h3 class="title">41.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 end
 176      without a <code class="command">RETURN</code> statement.  If you wish to use
 177      a <code class="command">RETURN</code> statement to exit the code early, write
 178      just <code class="command">RETURN</code> with no expression.
 179     </p><p>
 180      If the procedure has output parameters, the final values of the output
 181      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">41.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 procedure
 185      using <code class="command">CALL</code>.  Output parameters are handled
 186      differently from the way that <code class="command">CALL</code> works in plain
 187      SQL.  Each <code class="literal">OUT</code> or <code class="literal">INOUT</code>
 188      parameter of the procedure must
 189      correspond to a variable in the <code class="command">CALL</code> statement, and
 190      whatever the procedure returns is assigned back to that variable after
 191      it returns.  For example:
 192 </p><pre class="programlisting">
 193 CREATE PROCEDURE triple(INOUT x int)
 194 LANGUAGE plpgsql
 195 AS $$
 196 BEGIN
 197     x := x * 3;
 198 END;
 199 $$;
 200
 201 DO $$
 202 DECLARE myvar int := 5;
 203 BEGIN
 204   CALL triple(myvar);
 205   RAISE NOTICE 'myvar = %', myvar;  -- prints 15
 206 END;
 207 $$;
 208 </pre><p>
 209      The variable corresponding to an output parameter can be a simple
 210      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">41.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 execute
 214      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">41.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">
 221 IF <em class="replaceable"><code>boolean-expression</code></em> THEN
 222     <em class="replaceable"><code>statements</code></em>
 223 END IF;
 224 </pre><p>
 225         <code class="literal">IF-THEN</code> statements are the simplest form of
 226         <code class="literal">IF</code>. The statements between
 227         <code class="literal">THEN</code> and <code class="literal">END IF</code> will be
 228         executed if the condition is true. Otherwise, they are
 229         skipped.
 230        </p><p>
 231         Example:
 232 </p><pre class="programlisting">
 233 IF v_user_id &lt;&gt; 0 THEN
 234     UPDATE users SET email = v_email WHERE user_id = v_user_id;
 235 END IF;
 236 </pre><p>
 237        </p></div><div class="sect3" id="PLPGSQL-CONDITIONALS-IF-THEN-ELSE"><div class="titlepage"><div><div><h4 class="title">41.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">
 238 IF <em class="replaceable"><code>boolean-expression</code></em> THEN
 239     <em class="replaceable"><code>statements</code></em>
 240 ELSE
 241     <em class="replaceable"><code>statements</code></em>
 242 END IF;
 243 </pre><p>
 244         <code class="literal">IF-THEN-ELSE</code> statements add to
 245         <code class="literal">IF-THEN</code> by letting you specify an
 246         alternative set of statements that should be executed if the
 247         condition is not true.  (Note this includes the case where the
 248         condition evaluates to NULL.)
 249        </p><p>
 250         Examples:
 251 </p><pre class="programlisting">
 252 IF parentid IS NULL OR parentid = ''
 253 THEN
 254     RETURN fullname;
 255 ELSE
 256     RETURN hp_true_filename(parentid) || '/' || fullname;
 257 END IF;
 258 </pre><p>
 259
 260 </p><pre class="programlisting">
 261 IF v_count &gt; 0 THEN
 262     INSERT INTO users_count (count) VALUES (v_count);
 263     RETURN 't';
 264 ELSE
 265     RETURN 'f';
 266 END IF;
 267 </pre><p>
 268      </p></div><div class="sect3" id="PLPGSQL-CONDITIONALS-IF-THEN-ELSIF"><div class="titlepage"><div><div><h4 class="title">41.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">
 269 IF <em class="replaceable"><code>boolean-expression</code></em> THEN
 270     <em class="replaceable"><code>statements</code></em>
 271 [<span class="optional"> ELSIF <em class="replaceable"><code>boolean-expression</code></em> THEN
 272     <em class="replaceable"><code>statements</code></em>
 273 [<span class="optional"> ELSIF <em class="replaceable"><code>boolean-expression</code></em> THEN
 274     <em class="replaceable"><code>statements</code></em>
 275     ...
 276 </span>]
 277 </span>]
 278 [<span class="optional"> ELSE
 279     <em class="replaceable"><code>statements</code></em> </span>]
 280 END IF;
 281 </pre><p>
 282         Sometimes there are more than just two alternatives.
 283         <code class="literal">IF-THEN-ELSIF</code> provides a convenient
 284         method of checking several alternatives in turn.
 285         The <code class="literal">IF</code> conditions are tested successively
 286         until the first one that is true is found.  Then the
 287         associated statement(s) are executed, after which control
 288         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">
 296 IF number = 0 THEN
 297     result := 'zero';
 298 ELSIF number &gt; 0 THEN
 299     result := 'positive';
 300 ELSIF number &lt; 0 THEN
 301     result := 'negative';
 302 ELSE
 303     -- hmm, the only other possibility is that number is null
 304     result := 'NULL';
 305 END IF;
 306 </pre><p>
 307        </p><p>
 308         The key word <code class="literal">ELSIF</code> can also be spelled
 309         <code class="literal">ELSEIF</code>.
 310        </p><p>
 311         An alternative way of accomplishing the same task is to nest
 312         <code class="literal">IF-THEN-ELSE</code> statements, as in the
 313         following example:
 314
 315 </p><pre class="programlisting">
 316 IF demo_row.sex = 'm' THEN
 317     pretty_sex := 'man';
 318 ELSE
 319     IF demo_row.sex = 'f' THEN
 320         pretty_sex := 'woman';
 321     END IF;
 322 END 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 than
 327         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">41.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">
 329 CASE <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>] THEN
 331       <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>] THEN
 333       <em class="replaceable"><code>statements</code></em>
 334     ... </span>]
 335   [<span class="optional"> ELSE
 336       <em class="replaceable"><code>statements</code></em> </span>]
 337 END CASE;
 338 </pre><p>
 339        The simple form of <code class="command">CASE</code> provides conditional execution
 340        based on equality of operands.  The <em class="replaceable"><code>search-expression</code></em>
 341        is evaluated (once) and successively compared to each
 342        <em class="replaceable"><code>expression</code></em> in the <code class="literal">WHEN</code> clauses.
 343        If a match is found, then the corresponding
 344        <em class="replaceable"><code>statements</code></em> are executed, and then control
 345        passes to the next statement after <code class="literal">END CASE</code>.  (Subsequent
 346        <code class="literal">WHEN</code> expressions are not evaluated.)  If no match is
 347        found, the <code class="literal">ELSE</code> <em class="replaceable"><code>statements</code></em> are
 348        executed; but if <code class="literal">ELSE</code> is not present, then a
 349        <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">
 354 CASE x
 355     WHEN 1, 2 THEN
 356         msg := 'one or two';
 357     ELSE
 358         msg := 'other value than one or two';
 359 END CASE;
 360 </pre><p>
 361       </p></div><div class="sect3" id="PLPGSQL-CONDITIONALS-SEARCHED-CASE"><div class="titlepage"><div><div><h4 class="title">41.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">
 362 CASE
 363     WHEN <em class="replaceable"><code>boolean-expression</code></em> THEN
 364       <em class="replaceable"><code>statements</code></em>
 365   [<span class="optional"> WHEN <em class="replaceable"><code>boolean-expression</code></em> THEN
 366       <em class="replaceable"><code>statements</code></em>
 367     ... </span>]
 368   [<span class="optional"> ELSE
 369       <em class="replaceable"><code>statements</code></em> </span>]
 370 END CASE;
 371 </pre><p>
 372        The searched form of <code class="command">CASE</code> provides conditional execution
 373        based on truth of Boolean expressions.  Each <code class="literal">WHEN</code> clause's
 374        <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 the
 376        corresponding <em class="replaceable"><code>statements</code></em> are executed, and
 377        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 a
 382        <code class="literal">CASE_NOT_FOUND</code> exception is raised.
 383       </p><p>
 384        Here is an example:
 385
 386 </p><pre class="programlisting">
 387 CASE
 388     WHEN x BETWEEN 0 AND 10 THEN
 389         msg := 'value is between zero and ten';
 390     WHEN x BETWEEN 11 AND 20 THEN
 391         msg := 'value is between eleven and twenty';
 392 END CASE;
 393 </pre><p>
 394       </p><p>
 395        This form of <code class="command">CASE</code> is entirely equivalent to
 396        <code class="literal">IF-THEN-ELSIF</code>, except for the rule that reaching
 397        an omitted <code class="literal">ELSE</code> clause results in an error rather
 398        than doing nothing.
 399       </p></div></div><div class="sect2" id="PLPGSQL-CONTROL-STRUCTURES-LOOPS"><div class="titlepage"><div><div><h3 class="title">41.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 your
 403      <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">41.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>]
 406 LOOP
 407     <em class="replaceable"><code>statements</code></em>
 408 END 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 repeated
 411       indefinitely until terminated by an <code class="literal">EXIT</code> or
 412       <code class="command">RETURN</code> statement.  The optional
 413       <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 to
 415       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">41.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">
 417 EXIT [<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 innermost
 420         loop is terminated and the statement following <code class="literal">END
 421         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 outer
 423         level of nested loop or block. Then the named loop or block is
 424         terminated and control continues with the statement after the
 425         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 if
 428         <em class="replaceable"><code>boolean-expression</code></em> is true. Otherwise, control passes
 429         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 is
 432         not limited to use with unconditional loops.
 433        </p><p>
 434         When used with a
 435         <code class="literal">BEGIN</code> block, <code class="literal">EXIT</code> passes
 436         control to the next statement after the end of the block.
 437         Note that a label must be used for this purpose; an unlabeled
 438         <code class="literal">EXIT</code> is never considered to match a
 439         <code class="literal">BEGIN</code> block.  (This is a change from
 440         pre-8.4 releases of <span class="productname">PostgreSQL</span>, which
 441         would allow an unlabeled <code class="literal">EXIT</code> to match
 442         a <code class="literal">BEGIN</code> block.)
 443        </p><p>
 444         Examples:
 445 </p><pre class="programlisting">
 446 LOOP
 447     -- some computations
 448     IF count &gt; 0 THEN
 449         EXIT;  -- exit loop
 450     END IF;
 451 END LOOP;
 452
 453 LOOP
 454     -- some computations
 455     EXIT WHEN count &gt; 0;  -- same result as previous example
 456 END LOOP;
 457
 458 &lt;&lt;ablock&gt;&gt;
 459 BEGIN
 460     -- some computations
 461     IF stocks &gt; 100000 THEN
 462         EXIT ablock;  -- causes exit from the BEGIN block
 463     END IF;
 464     -- computations here will be skipped when stocks &gt; 100000
 465 END;
 466 </pre><p>
 467        </p></div><div class="sect3" id="PLPGSQL-CONTROL-STRUCTURES-LOOPS-CONTINUE"><div class="titlepage"><div><div><h4 class="title">41.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">
 468 CONTINUE [<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 of
 471         the innermost loop is begun. That is, all statements remaining
 472         in the loop body are skipped, and control returns
 473         to the loop control expression (if any) to determine whether
 474         another loop iteration is needed.
 475         If <em class="replaceable"><code>label</code></em> is present, it
 476         specifies the label of the loop whose execution will be
 477         continued.
 478        </p><p>
 479         If <code class="literal">WHEN</code> is specified, the next iteration of the
 480         loop is begun only if <em class="replaceable"><code>boolean-expression</code></em> is
 481         true. Otherwise, control passes to the statement after
 482         <code class="literal">CONTINUE</code>.
 483        </p><p>
 484         <code class="literal">CONTINUE</code> can be used with all types of loops; it
 485         is not limited to use with unconditional loops.
 486        </p><p>
 487         Examples:
 488 </p><pre class="programlisting">
 489 LOOP
 490     -- some computations
 491     EXIT WHEN count &gt; 100;
 492     CONTINUE WHEN count &lt; 50;
 493     -- some computations for count IN [50 .. 100]
 494 END LOOP;
 495 </pre><p>
 496        </p></div><div class="sect3" id="PLPGSQL-CONTROL-STRUCTURES-LOOPS-WHILE"><div class="titlepage"><div><div><h4 class="title">41.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>]
 498 WHILE <em class="replaceable"><code>boolean-expression</code></em> LOOP
 499     <em class="replaceable"><code>statements</code></em>
 500 END LOOP [<span class="optional"> <em class="replaceable"><code>label</code></em> </span>];
 501 </pre><p>
 502         The <code class="literal">WHILE</code> statement repeats a
 503         sequence of statements so long as the
 504         <em class="replaceable"><code>boolean-expression</code></em>
 505         evaluates to true.  The expression is checked just before
 506         each entry to the loop body.
 507        </p><p>
 508         For example:
 509 </p><pre class="programlisting">
 510 WHILE amount_owed &gt; 0 AND gift_certificate_balance &gt; 0 LOOP
 511     -- some computations here
 512 END LOOP;
 513
 514 WHILE NOT done LOOP
 515     -- some computations here
 516 END LOOP;
 517 </pre><p>
 518        </p></div><div class="sect3" id="PLPGSQL-INTEGER-FOR"><div class="titlepage"><div><div><h4 class="title">41.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>]
 520 FOR <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>] LOOP
 521     <em class="replaceable"><code>statements</code></em>
 522 END 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 range
 525         of integer values. The variable
 526         <em class="replaceable"><code>name</code></em> is automatically defined as type
 527         <code class="type">integer</code> and exists only inside the loop (any existing
 528         definition of the variable name is ignored within the loop).
 529         The two expressions giving
 530         the lower and upper bound of the range are evaluated once when entering
 531         the loop. If the <code class="literal">BY</code> clause isn't specified the iteration
 532         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 is
 535         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">
 539 FOR i IN 1..10 LOOP
 540     -- i will take on the values 1,2,3,4,5,6,7,8,9,10 within the loop
 541 END LOOP;
 542
 543 FOR i IN REVERSE 10..1 LOOP
 544     -- i will take on the values 10,9,8,7,6,5,4,3,2,1 within the loop
 545 END LOOP;
 546
 547 FOR i IN REVERSE 10..1 BY 2 LOOP
 548     -- i will take on the values 10,8,6,4,2 within the loop
 549 END 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 not
 554         executed at all.  No error is raised.
 555        </p><p>
 556         If a <em class="replaceable"><code>label</code></em> is attached to the
 557         <code class="literal">FOR</code> loop then the integer loop variable can be
 558         referenced with a qualified name, using that
 559         <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">41.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 through
 562      the results of a query and manipulate that data
 563      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>]
 566 FOR <em class="replaceable"><code>target</code></em> IN <em class="replaceable"><code>query</code></em> LOOP
 567     <em class="replaceable"><code>statements</code></em>
 568 END 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 row
 573      resulting from the <em class="replaceable"><code>query</code></em> and the loop body is
 574      executed for each row. Here is an example:
 575 </p><pre class="programlisting">
 576 CREATE FUNCTION refresh_mviews() RETURNS integer AS $$
 577 DECLARE
 578     mviews RECORD;
 579 BEGIN
 580     RAISE NOTICE 'Refreshing all materialized views...';
 581
 582     FOR mviews IN
 583        SELECT n.nspname AS mv_schema,
 584               c.relname AS mv_name,
 585               pg_catalog.pg_get_userbyid(c.relowner) AS owner
 586          FROM pg_catalog.pg_class c
 587     LEFT JOIN pg_catalog.pg_namespace n ON (n.oid = c.relnamespace)
 588         WHERE c.relkind = 'm'
 589      ORDER BY 1
 590     LOOP
 591
 592         -- Now "mviews" has one record with information about the materialized view
 593
 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;
 603 END;
 604 $$ LANGUAGE plpgsql;
 605 </pre><p>
 606
 607      If the loop is terminated by an <code class="literal">EXIT</code> statement, the last
 608      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>,
 614      <code class="command">DELETE</code>, or <code class="command">MERGE</code> with a
 615      <code class="literal">RETURNING</code> clause.  Some utility
 616      commands such as <code class="command">EXPLAIN</code> will work too.
 617     </p><p>
 618      <span class="application">PL/pgSQL</span> variables are replaced by query parameters,
 619      and the query plan is cached for possible re-use, as discussed in
 620      detail in <a class="xref" href="plpgsql-implementation.html#PLPGSQL-VAR-SUBST" title="41.11.1. Variable Substitution">Section 41.11.1</a> and
 621      <a class="xref" href="plpgsql-implementation.html#PLPGSQL-PLAN-CACHING" title="41.11.2. Plan Caching">Section 41.11.2</a>.
 622     </p><p>
 623      The <code class="literal">FOR-IN-EXECUTE</code> statement is another way to iterate over
 624      rows:
 625 </p><pre class="synopsis">
 626 [<span class="optional"> &lt;&lt;<em class="replaceable"><code>label</code></em>&gt;&gt; </span>]
 627 FOR <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>] LOOP
 628     <em class="replaceable"><code>statements</code></em>
 629 END LOOP [<span class="optional"> <em class="replaceable"><code>label</code></em> </span>];
 630 </pre><p>
 631      This is like the previous form, except that the source query
 632      is specified as a string expression, which is evaluated and replanned
 633      on each entry to the <code class="literal">FOR</code> loop.  This allows the programmer to
 634      choose the speed of a preplanned query or the flexibility of a dynamic
 635      query, just as with a plain <code class="command">EXECUTE</code> statement.
 636      As with <code class="command">EXECUTE</code>, parameter values can be inserted
 637      into the dynamic command via <code class="literal">USING</code>.
 638     </p><p>
 639      Another way to specify the query whose results should be iterated
 640      through is to declare it as a cursor.  This is described in
 641      <a class="xref" href="plpgsql-cursors.html#PLPGSQL-CURSOR-FOR-LOOP" title="41.7.4. Looping through a Cursor's Result">Section 41.7.4</a>.
 642     </p></div><div class="sect2" id="PLPGSQL-FOREACH-ARRAY"><div class="titlepage"><div><div><h3 class="title">41.6.7. Looping through Arrays <a href="#PLPGSQL-FOREACH-ARRAY" class="id_link">#</a></h3></div></div></div><p>
 643      The <code class="literal">FOREACH</code> loop is much like a <code class="literal">FOR</code> loop,
 644      but instead of iterating through the rows returned by an SQL query,
 645      it iterates through the elements of an array value.
 646      (In general, <code class="literal">FOREACH</code> is meant for looping through
 647      components of a composite-valued expression; variants for looping
 648      through composites besides arrays may be added in future.)
 649      The <code class="literal">FOREACH</code> statement to loop over an array is:
 650
 651 </p><pre class="synopsis">
 652 [<span class="optional"> &lt;&lt;<em class="replaceable"><code>label</code></em>&gt;&gt; </span>]
 653 FOREACH <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> LOOP
 654     <em class="replaceable"><code>statements</code></em>
 655 END LOOP [<span class="optional"> <em class="replaceable"><code>label</code></em> </span>];
 656 </pre><p>
 657     </p><p>
 658      Without <code class="literal">SLICE</code>, or if <code class="literal">SLICE 0</code> is specified,
 659      the loop iterates through individual elements of the array produced
 660      by evaluating the <em class="replaceable"><code>expression</code></em>.
 661      The <em class="replaceable"><code>target</code></em> variable is assigned each
 662      element value in sequence, and the loop body is executed for each element.
 663      Here is an example of looping through the elements of an integer
 664      array:
 665
 666 </p><pre class="programlisting">
 667 CREATE FUNCTION sum(int[]) RETURNS int8 AS $$
 668 DECLARE
 669   s int8 := 0;
 670   x int;
 671 BEGIN
 672   FOREACH x IN ARRAY $1
 673   LOOP
 674     s := s + x;
 675   END LOOP;
 676   RETURN s;
 677 END;
 678 $$ LANGUAGE plpgsql;
 679 </pre><p>
 680
 681      The elements are visited in storage order, regardless of the number of
 682      array dimensions.  Although the <em class="replaceable"><code>target</code></em> is
 683      usually just a single variable, it can be a list of variables when
 684      looping through an array of composite values (records).  In that case,
 685      for each array element, the variables are assigned from successive
 686      columns of the composite value.
 687     </p><p>
 688      With a positive <code class="literal">SLICE</code> value, <code class="literal">FOREACH</code>
 689      iterates through slices of the array rather than single elements.
 690      The <code class="literal">SLICE</code> value must be an integer constant not larger
 691      than the number of dimensions of the array.  The
 692      <em class="replaceable"><code>target</code></em> variable must be an array,
 693      and it receives successive slices of the array value, where each slice
 694      is of the number of dimensions specified by <code class="literal">SLICE</code>.
 695      Here is an example of iterating through one-dimensional slices:
 696
 697 </p><pre class="programlisting">
 698 CREATE FUNCTION scan_rows(int[]) RETURNS void AS $$
 699 DECLARE
 700   x int[];
 701 BEGIN
 702   FOREACH x SLICE 1 IN ARRAY $1
 703   LOOP
 704     RAISE NOTICE 'row = %', x;
 705   END LOOP;
 706 END;
 707 $$ LANGUAGE plpgsql;
 708
 709 SELECT scan_rows(ARRAY[[1,2,3],[4,5,6],[7,8,9],[10,11,12]]);
 710
 711 NOTICE:  row = {1,2,3}
 712 NOTICE:  row = {4,5,6}
 713 NOTICE:  row = {7,8,9}
 714 NOTICE:  row = {10,11,12}
 715 </pre><p>
 716     </p></div><div class="sect2" id="PLPGSQL-ERROR-TRAPPING"><div class="titlepage"><div><div><h3 class="title">41.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>
 717      By default, any error occurring in a <span class="application">PL/pgSQL</span>
 718      function aborts execution of the function and the
 719      surrounding transaction.  You can trap errors and recover
 720      from them by using a <code class="command">BEGIN</code> block with an
 721      <code class="literal">EXCEPTION</code> clause.  The syntax is an extension of the
 722      normal syntax for a <code class="command">BEGIN</code> block:
 723
 724 </p><pre class="synopsis">
 725 [<span class="optional"> &lt;&lt;<em class="replaceable"><code>label</code></em>&gt;&gt; </span>]
 726 [<span class="optional"> DECLARE
 727     <em class="replaceable"><code>declarations</code></em> </span>]
 728 BEGIN
 729     <em class="replaceable"><code>statements</code></em>
 730 EXCEPTION
 731     WHEN <em class="replaceable"><code>condition</code></em> [<span class="optional"> OR <em class="replaceable"><code>condition</code></em> ... </span>] THEN
 732         <em class="replaceable"><code>handler_statements</code></em>
 733     [<span class="optional"> WHEN <em class="replaceable"><code>condition</code></em> [<span class="optional"> OR <em class="replaceable"><code>condition</code></em> ... </span>] THEN
 734           <em class="replaceable"><code>handler_statements</code></em>
 735       ... </span>]
 736 END;
 737 </pre><p>
 738     </p><p>
 739      If no error occurs, this form of block simply executes all the
 740      <em class="replaceable"><code>statements</code></em>, and then control passes
 741      to the next statement after <code class="literal">END</code>.  But if an error
 742      occurs within the <em class="replaceable"><code>statements</code></em>, further
 743      processing of the <em class="replaceable"><code>statements</code></em> is
 744      abandoned, and control passes to the <code class="literal">EXCEPTION</code> list.
 745      The list is searched for the first <em class="replaceable"><code>condition</code></em>
 746      matching the error that occurred.  If a match is found, the
 747      corresponding <em class="replaceable"><code>handler_statements</code></em> are
 748      executed, and then control passes to the next statement after
 749      <code class="literal">END</code>.  If no match is found, the error propagates out
 750      as though the <code class="literal">EXCEPTION</code> clause were not there at all:
 751      the error can be caught by an enclosing block with
 752      <code class="literal">EXCEPTION</code>, or if there is none it aborts processing
 753      of the function.
 754     </p><p>
 755      The <em class="replaceable"><code>condition</code></em> names can be any of
 756      those shown in <a class="xref" href="errcodes-appendix.html" title="Appendix A. PostgreSQL Error Codes">Appendix A</a>.  A category
 757      name matches any error within its category.  The special
 758      condition name <code class="literal">OTHERS</code> matches every error type except
 759      <code class="literal">QUERY_CANCELED</code> and <code class="literal">ASSERT_FAILURE</code>.
 760      (It is possible, but often unwise, to trap those two error types
 761      by name.)  Condition names are
 762      not case-sensitive.  Also, an error condition can be specified
 763      by <code class="literal">SQLSTATE</code> code; for example these are equivalent:
 764 </p><pre class="programlisting">
 765 WHEN division_by_zero THEN ...
 766 WHEN SQLSTATE '22012' THEN ...
 767 </pre><p>
 768     </p><p>
 769      If a new error occurs within the selected
 770      <em class="replaceable"><code>handler_statements</code></em>, it cannot be caught
 771      by this <code class="literal">EXCEPTION</code> clause, but is propagated out.
 772      A surrounding <code class="literal">EXCEPTION</code> clause could catch it.
 773     </p><p>
 774      When an error is caught by an <code class="literal">EXCEPTION</code> clause,
 775      the local variables of the <span class="application">PL/pgSQL</span> function
 776      remain as they were when the error occurred, but all changes
 777      to persistent database state within the block are rolled back.
 778      As an example, consider this fragment:
 779
 780 </p><pre class="programlisting">
 781 INSERT INTO mytab(firstname, lastname) VALUES('Tom', 'Jones');
 782 BEGIN
 783     UPDATE mytab SET firstname = 'Joe' WHERE lastname = 'Jones';
 784     x := x + 1;
 785     y := x / 0;
 786 EXCEPTION
 787     WHEN division_by_zero THEN
 788         RAISE NOTICE 'caught division_by_zero';
 789         RETURN x;
 790 END;
 791 </pre><p>
 792
 793      When control reaches the assignment to <code class="literal">y</code>, it will
 794      fail with a <code class="literal">division_by_zero</code> error.  This will be caught by
 795      the <code class="literal">EXCEPTION</code> clause.  The value returned in the
 796      <code class="command">RETURN</code> statement will be the incremented value of
 797      <code class="literal">x</code>, but the effects of the <code class="command">UPDATE</code> command will
 798      have been rolled back.  The <code class="command">INSERT</code> command preceding the
 799      block is not rolled back, however, so the end result is that the database
 800      contains <code class="literal">Tom Jones</code> not <code class="literal">Joe Jones</code>.
 801     </p><div class="tip"><h3 class="title">Tip</h3><p>
 802       A block containing an <code class="literal">EXCEPTION</code> clause is significantly
 803       more expensive to enter and exit than a block without one.  Therefore,
 804       don't use <code class="literal">EXCEPTION</code> without need.
 805      </p></div><div class="example" id="PLPGSQL-UPSERT-EXAMPLE"><p class="title"><strong>Example 41.2. Exceptions with <code class="command">UPDATE</code>/<code class="command">INSERT</code></strong></p><div class="example-contents"><p>
 806
 807     This example uses exception handling to perform either
 808     <code class="command">UPDATE</code> or <code class="command">INSERT</code>, as appropriate.  It is
 809     recommended that applications use <code class="command">INSERT</code> with
 810     <code class="literal">ON CONFLICT DO UPDATE</code> rather than actually using
 811     this pattern.  This example serves primarily to illustrate use of
 812     <span class="application">PL/pgSQL</span> control flow structures:
 813
 814 </p><pre class="programlisting">
 815 CREATE TABLE db (a INT PRIMARY KEY, b TEXT);
 816
 817 CREATE FUNCTION merge_db(key INT, data TEXT) RETURNS VOID AS
 818 $$
 819 BEGIN
 820     LOOP
 821         -- first try to update the key
 822         UPDATE db SET b = data WHERE a = key;
 823         IF found THEN
 824             RETURN;
 825         END IF;
 826         -- not there, so try to insert the key
 827         -- if someone else inserts the same key concurrently,
 828         -- we could get a unique-key failure
 829         BEGIN
 830             INSERT INTO db(a,b) VALUES (key, data);
 831             RETURN;
 832         EXCEPTION WHEN unique_violation THEN
 833             -- Do nothing, and loop to try the UPDATE again.
 834         END;
 835     END LOOP;
 836 END;
 837 $$
 838 LANGUAGE plpgsql;
 839
 840 SELECT merge_db(1, 'david');
 841 SELECT merge_db(1, 'dennis');
 842 </pre><p>
 843
 844      This coding assumes the <code class="literal">unique_violation</code> error is caused by
 845      the <code class="command">INSERT</code>, and not by, say, an <code class="command">INSERT</code> in a
 846      trigger function on the table.  It might also misbehave if there is
 847      more than one unique index on the table, since it will retry the
 848      operation regardless of which index caused the error.
 849      More safety could be had by using the
 850      features discussed next to check that the trapped error was the one
 851      expected.
 852     </p></div></div><br class="example-break" /><div class="sect3" id="PLPGSQL-EXCEPTION-DIAGNOSTICS"><div class="titlepage"><div><div><h4 class="title">41.6.8.1. Obtaining Information about an Error <a href="#PLPGSQL-EXCEPTION-DIAGNOSTICS" class="id_link">#</a></h4></div></div></div><p>
 853      Exception handlers frequently need to identify the specific error that
 854      occurred.  There are two ways to get information about the current
 855      exception in <span class="application">PL/pgSQL</span>: special variables and the
 856      <code class="command">GET STACKED DIAGNOSTICS</code> command.
 857     </p><p>
 858      Within an exception handler, the special variable
 859      <code class="varname">SQLSTATE</code> contains the error code that corresponds to
 860      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>
 861      for a list of possible error codes). The special variable
 862      <code class="varname">SQLERRM</code> contains the error message associated with the
 863      exception. These variables are undefined outside exception handlers.
 864     </p><p>
 865      Within an exception handler, one may also retrieve
 866      information about the current exception by using the
 867      <code class="command">GET STACKED DIAGNOSTICS</code> command, which has the form:
 868
 869 </p><pre class="synopsis">
 870 GET STACKED DIAGNOSTICS <em class="replaceable"><code>variable</code></em> { = | := } <em class="replaceable"><code>item</code></em> [<span class="optional"> , ... </span>];
 871 </pre><p>
 872
 873      Each <em class="replaceable"><code>item</code></em> is a key word identifying a status
 874      value to be assigned to the specified <em class="replaceable"><code>variable</code></em>
 875      (which should be of the right data type to receive it).  The currently
 876      available status items are shown
 877      in <a class="xref" href="plpgsql-control-structures.html#PLPGSQL-EXCEPTION-DIAGNOSTICS-VALUES" title="Table 41.2. Error Diagnostics Items">Table 41.2</a>.
 878     </p><div class="table" id="PLPGSQL-EXCEPTION-DIAGNOSTICS-VALUES"><p class="title"><strong>Table 41.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 the
 879           exception (see <a class="xref" href="plpgsql-control-structures.html#PLPGSQL-CALL-STACK" title="41.6.9. Obtaining Execution Location Information">Section 41.6.9</a>)</td></tr></tbody></table></div></div><br class="table-break" /><p>
 880      If the exception did not set a value for an item, an empty string
 881      will be returned.
 882     </p><p>
 883      Here is an example:
 884 </p><pre class="programlisting">
 885 DECLARE
 886   text_var1 text;
 887   text_var2 text;
 888   text_var3 text;
 889 BEGIN
 890   -- some processing which might cause an exception
 891   ...
 892 EXCEPTION WHEN OTHERS THEN
 893   GET STACKED DIAGNOSTICS text_var1 = MESSAGE_TEXT,
 894                           text_var2 = PG_EXCEPTION_DETAIL,
 895                           text_var3 = PG_EXCEPTION_HINT;
 896 END;
 897 </pre><p>
 898     </p></div></div><div class="sect2" id="PLPGSQL-CALL-STACK"><div class="titlepage"><div><div><h3 class="title">41.6.9. Obtaining Execution Location Information <a href="#PLPGSQL-CALL-STACK" class="id_link">#</a></h3></div></div></div><p>
 899     The <code class="command">GET DIAGNOSTICS</code> command, previously described
 900     in <a class="xref" href="plpgsql-statements.html#PLPGSQL-STATEMENTS-DIAGNOSTICS" title="41.5.5. Obtaining the Result Status">Section 41.5.5</a>, retrieves information
 901     about current execution state (whereas the <code class="command">GET STACKED
 902     DIAGNOSTICS</code> command discussed above reports information about
 903     the execution state as of a previous error).  Its <code class="literal">PG_CONTEXT</code>
 904     status item is useful for identifying the current execution
 905     location.  <code class="literal">PG_CONTEXT</code> returns a text string with line(s)
 906     of text describing the call stack.  The first line refers to the current
 907     function and currently executing <code class="command">GET DIAGNOSTICS</code>
 908     command.  The second and any subsequent lines refer to calling functions
 909     further up the call stack.  For example:
 910
 911 </p><pre class="programlisting">
 912 CREATE OR REPLACE FUNCTION outer_func() RETURNS integer AS $$
 913 BEGIN
 914   RETURN inner_func();
 915 END;
 916 $$ LANGUAGE plpgsql;
 917
 918 CREATE OR REPLACE FUNCTION inner_func() RETURNS integer AS $$
 919 DECLARE
 920   stack text;
 921 BEGIN
 922   GET DIAGNOSTICS stack = PG_CONTEXT;
 923   RAISE NOTICE E'--- Call Stack ---\n%', stack;
 924   RETURN 1;
 925 END;
 926 $$ LANGUAGE plpgsql;
 927
 928 SELECT outer_func();
 929
 930 NOTICE:  --- Call Stack ---
 931 PL/pgSQL function inner_func() line 5 at GET DIAGNOSTICS
 932 PL/pgSQL function outer_func() line 3 at RETURN
 933 CONTEXT:  PL/pgSQL function outer_func() line 3 at RETURN
 934  outer_func
 935  ------------
 936            1
 937 (1 row)
 938 </pre><p>
 939
 940    </p><p>
 941     <code class="literal">GET STACKED DIAGNOSTICS ... PG_EXCEPTION_CONTEXT</code>
 942     returns the same sort of stack trace, but describing the location
 943     at which an error was detected, rather than the current location.
 944    </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="41.5. Basic Statements">Prev</a> </td><td width="20%" align="center"><a accesskey="u" href="plpgsql.html" title="Chapter 41. PL/pgSQL — SQL Procedural Language">Up</a></td><td width="40%" align="right"> <a accesskey="n" href="plpgsql-cursors.html" title="41.7. Cursors">Next</a></td></tr><tr><td width="40%" align="left" valign="top">41.5. Basic Statements </td><td width="20%" align="center"><a accesskey="h" href="index.html" title="PostgreSQL 18.0 Documentation">Home</a></td><td width="40%" align="right" valign="top"> 41.7. Cursors</td></tr></table></div></body></html>