codekingpro/portable-devtools
115k
1<?xml version="1.0" encoding="UTF-8" standalone="no"?>2<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd"><html xmlns="http://www.w3.org/1999/xhtml"><head><meta http-equiv="Content-Type" content="text/html; charset=UTF-8" /><title>43.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 > 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 >= $1141 AND flightdate < ($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 <> 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 > 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 > 0 THEN299 result := 'positive';300ELSIF number < 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"> <<<em class="replaceable"><code>label</code></em>>> </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 > 0 THEN449 EXIT; -- exit loop450 END IF;451END LOOP;452 453LOOP454 -- some computations455 EXIT WHEN count > 0; -- same result as previous example456END LOOP;457 458<<ablock>>459BEGIN460 -- some computations461 IF stocks > 100000 THEN462 EXIT ablock; -- causes exit from the BEGIN block463 END IF;464 -- computations here will be skipped when stocks > 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 > 100;492 CONTINUE WHEN count < 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"> <<<em class="replaceable"><code>label</code></em>>> </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 > 0 AND gift_certificate_balance > 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"> <<<em class="replaceable"><code>label</code></em>>> </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"> <<<em class="replaceable"><code>label</code></em>>> </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"> <<<em class="replaceable"><code>label</code></em>>> </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"> <<<em class="replaceable"><code>label</code></em>>> </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"> <<<em class="replaceable"><code>label</code></em>>> </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>