Team Ai
Datasetpublic

codekingpro/portable-devtools

sourceHugging Faceupdated 5mo agoView on Hugging Face
1likes15kdownloads
plpgsql-cursors.html401 linesDownload Raw Back to html
1<?xml version="1.0" encoding="UTF-8" standalone="no"?>2<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd"><html xmlns="http://www.w3.org/1999/xhtml"><head><meta http-equiv="Content-Type" content="text/html; charset=UTF-8" /><title>43.7. Cursors</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-control-structures.html" title="43.6. Control Structures" /><link rel="next" href="plpgsql-transactions.html" title="43.8. Transaction Management" /></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.7. Cursors</th></tr><tr><td width="10%" align="left"><a accesskey="p" href="plpgsql-control-structures.html" title="43.6. Control Structures">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-transactions.html" title="43.8. Transaction Management">Next</a></td></tr></table><hr /></div><div class="sect1" id="PLPGSQL-CURSORS"><div class="titlepage"><div><div><h2 class="title" style="clear: both">43.7. Cursors <a href="#PLPGSQL-CURSORS" class="id_link">#</a></h2></div></div></div><div class="toc"><dl class="toc"><dt><span class="sect2"><a href="plpgsql-cursors.html#PLPGSQL-CURSOR-DECLARATIONS">43.7.1. Declaring Cursor Variables</a></span></dt><dt><span class="sect2"><a href="plpgsql-cursors.html#PLPGSQL-CURSOR-OPENING">43.7.2. Opening Cursors</a></span></dt><dt><span class="sect2"><a href="plpgsql-cursors.html#PLPGSQL-CURSOR-USING">43.7.3. Using Cursors</a></span></dt><dt><span class="sect2"><a href="plpgsql-cursors.html#PLPGSQL-CURSOR-FOR-LOOP">43.7.4. Looping through a Cursor's Result</a></span></dt></dl></div><a id="id-1.8.8.9.2" class="indexterm"></a><p>3    Rather than executing a whole query at once, it is possible to set4    up a <em class="firstterm">cursor</em> that encapsulates the query, and then read5    the query result a few rows at a time. One reason for doing this is6    to avoid memory overrun when the result contains a large number of7    rows. (However, <span class="application">PL/pgSQL</span> users do not normally need8    to worry about that, since <code class="literal">FOR</code> loops automatically use a cursor9    internally to avoid memory problems.) A more interesting usage is to10    return a reference to a cursor that a function has created, allowing the11    caller to read the rows. This provides an efficient way to return12    large row sets from functions.13   </p><div class="sect2" id="PLPGSQL-CURSOR-DECLARATIONS"><div class="titlepage"><div><div><h3 class="title">43.7.1. Declaring Cursor Variables <a href="#PLPGSQL-CURSOR-DECLARATIONS" class="id_link">#</a></h3></div></div></div><p>14     All access to cursors in <span class="application">PL/pgSQL</span> goes through15     cursor variables, which are always of the special data type16     <code class="type">refcursor</code>.  One way to create a cursor variable17     is just to declare it as a variable of type <code class="type">refcursor</code>.18     Another way is to use the cursor declaration syntax,19     which in general is:20</p><pre class="synopsis">21<em class="replaceable"><code>name</code></em> [<span class="optional"> [<span class="optional"> NO </span>] SCROLL </span>] CURSOR [<span class="optional"> ( <em class="replaceable"><code>arguments</code></em> ) </span>] FOR <em class="replaceable"><code>query</code></em>;22</pre><p>23     (<code class="literal">FOR</code> can be replaced by <code class="literal">IS</code> for24     <span class="productname">Oracle</span> compatibility.)25     If <code class="literal">SCROLL</code> is specified, the cursor will be capable of26     scrolling backward; if <code class="literal">NO SCROLL</code> is specified, backward27     fetches will be rejected; if neither specification appears, it is28     query-dependent whether backward fetches will be allowed.29     <em class="replaceable"><code>arguments</code></em>, if specified, is a30     comma-separated list of pairs <code class="literal"><em class="replaceable"><code>name</code></em>31     <em class="replaceable"><code>datatype</code></em></code> that define names to be32     replaced by parameter values in the given query.  The actual33     values to substitute for these names will be specified later,34     when the cursor is opened.35    </p><p>36     Some examples:37</p><pre class="programlisting">38DECLARE39    curs1 refcursor;40    curs2 CURSOR FOR SELECT * FROM tenk1;41    curs3 CURSOR (key integer) FOR SELECT * FROM tenk1 WHERE unique1 = key;42</pre><p>43     All three of these variables have the data type <code class="type">refcursor</code>,44     but the first can be used with any query, while the second has45     a fully specified query already <em class="firstterm">bound</em> to it, and the last46     has a parameterized query bound to it.  (<code class="literal">key</code> will be47     replaced by an integer parameter value when the cursor is opened.)48     The variable <code class="literal">curs1</code>49     is said to be <em class="firstterm">unbound</em> since it is not bound to50     any particular query.51    </p><p>52     The <code class="literal">SCROLL</code> option cannot be used when the cursor's53     query uses <code class="literal">FOR UPDATE/SHARE</code>.  Also, it is54     best to use <code class="literal">NO SCROLL</code> with a query that involves55     volatile functions.  The implementation of <code class="literal">SCROLL</code>56     assumes that re-reading the query's output will give consistent57     results, which a volatile function might not do.58    </p></div><div class="sect2" id="PLPGSQL-CURSOR-OPENING"><div class="titlepage"><div><div><h3 class="title">43.7.2. Opening Cursors <a href="#PLPGSQL-CURSOR-OPENING" class="id_link">#</a></h3></div></div></div><p>59     Before a cursor can be used to retrieve rows, it must be60     <em class="firstterm">opened</em>. (This is the equivalent action to the SQL61     command <a class="link" href="sql-declare.html" title="DECLARE"><code class="command">DECLARE62     CURSOR</code></a>.)63     <span class="application">PL/pgSQL</span> has64     three forms of the <code class="command">OPEN</code> statement, two of which use unbound65     cursor variables while the third uses a bound cursor variable.66    </p><div class="note"><h3 class="title">Note</h3><p>67      Bound cursor variables can also be used without explicitly opening the cursor,68      via the <code class="command">FOR</code> statement described in69      <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>.70      A <code class="command">FOR</code> loop will open the cursor and then71      close it again when the loop completes.72     </p></div><a id="id-1.8.8.9.5.4" class="indexterm"></a><p>73     Opening a cursor involves creating a server-internal data structure74     called a <em class="firstterm">portal</em>, which holds the execution75     state for the cursor's query.  A portal has a name, which must be76     unique within the session for the duration of the portal's existence.77     By default, <span class="application">PL/pgSQL</span> will assign a unique78     name to each portal it creates.  However, if you assign a non-null79     string value to a cursor variable, that string will be used as its80     portal name.  This feature can be used as described in81     <a class="xref" href="plpgsql-cursors.html#PLPGSQL-CURSOR-RETURNING" title="43.7.3.5. Returning Cursors">Section 43.7.3.5</a>.82    </p><div class="sect3" id="PLPGSQL-CURSOR-OPENING-OPEN-FOR-QUERY"><div class="titlepage"><div><div><h4 class="title">43.7.2.1. <code class="command">OPEN FOR</code> <em class="replaceable"><code>query</code></em> <a href="#PLPGSQL-CURSOR-OPENING-OPEN-FOR-QUERY" class="id_link">#</a></h4></div></div></div><pre class="synopsis">83OPEN <em class="replaceable"><code>unbound_cursorvar</code></em> [<span class="optional"> [<span class="optional"> NO </span>] SCROLL </span>] FOR <em class="replaceable"><code>query</code></em>;84</pre><p>85        The cursor variable is opened and given the specified query to86        execute.  The cursor cannot be open already, and it must have been87        declared as an unbound cursor variable (that is, as a simple88        <code class="type">refcursor</code> variable).  The query must be a89        <code class="command">SELECT</code>, or something else that returns rows90        (such as <code class="command">EXPLAIN</code>).  The query91        is treated in the same way as other SQL commands in92        <span class="application">PL/pgSQL</span>: <span class="application">PL/pgSQL</span>93        variable names are substituted, and the query plan is cached for94        possible reuse.  When a <span class="application">PL/pgSQL</span>95        variable is substituted into the cursor query, the value that is96        substituted is the one it has at the time of the <code class="command">OPEN</code>;97        subsequent changes to the variable will not affect the cursor's98        behavior.99        The <code class="literal">SCROLL</code> and <code class="literal">NO SCROLL</code>100        options have the same meanings as for a bound cursor.101       </p><p>102        An example:103</p><pre class="programlisting">104OPEN curs1 FOR SELECT * FROM foo WHERE key = mykey;105</pre><p>106       </p></div><div class="sect3" id="PLPGSQL-CURSOR-OPENING-OPEN-FOR-EXECUTE"><div class="titlepage"><div><div><h4 class="title">43.7.2.2. <code class="command">OPEN FOR EXECUTE</code> <a href="#PLPGSQL-CURSOR-OPENING-OPEN-FOR-EXECUTE" class="id_link">#</a></h4></div></div></div><pre class="synopsis">107OPEN <em class="replaceable"><code>unbound_cursorvar</code></em> [<span class="optional"> [<span class="optional"> NO </span>] SCROLL </span>] FOR EXECUTE <em class="replaceable"><code>query_string</code></em>108                                     [<span class="optional"> USING <em class="replaceable"><code>expression</code></em> [<span class="optional">, ... </span>] </span>];109</pre><p>110          The cursor variable is opened and given the specified query to111          execute.  The cursor cannot be open already, and it must have been112          declared as an unbound cursor variable (that is, as a simple113          <code class="type">refcursor</code> variable).  The query is specified as a string114          expression, in the same way as in the <code class="command">EXECUTE</code>115          command.  As usual, this gives flexibility so the query plan can vary116          from one run to the next (see <a class="xref" href="plpgsql-implementation.html#PLPGSQL-PLAN-CACHING" title="43.11.2. Plan Caching">Section 43.11.2</a>),117          and it also means that variable substitution is not done on the118          command string. As with <code class="command">EXECUTE</code>, parameter values119          can be inserted into the dynamic command via120          <code class="literal">format()</code> and <code class="literal">USING</code>.121          The <code class="literal">SCROLL</code> and122          <code class="literal">NO SCROLL</code> options have the same meanings as for a bound123          cursor.124         </p><p>125        An example:126</p><pre class="programlisting">127OPEN curs1 FOR EXECUTE format('SELECT * FROM %I WHERE col1 = $1',tabname) USING keyvalue;128</pre><p>129        In this example, the table name is inserted into the query via130        <code class="function">format()</code>.  The comparison value for <code class="literal">col1</code>131        is inserted via a <code class="literal">USING</code> parameter, so it needs132        no quoting.133       </p></div><div class="sect3" id="PLPGSQL-OPEN-BOUND-CURSOR"><div class="titlepage"><div><div><h4 class="title">43.7.2.3. Opening a Bound Cursor <a href="#PLPGSQL-OPEN-BOUND-CURSOR" class="id_link">#</a></h4></div></div></div><pre class="synopsis">134OPEN <em class="replaceable"><code>bound_cursorvar</code></em> [<span class="optional"> ( [<span class="optional"> <em class="replaceable"><code>argument_name</code></em> := </span>] <em class="replaceable"><code>argument_value</code></em> [<span class="optional">, ...</span>] ) </span>];135</pre><p>136          This form of <code class="command">OPEN</code> is used to open a cursor137          variable whose query was bound to it when it was declared.  The138          cursor cannot be open already.  A list of actual argument value139          expressions must appear if and only if the cursor was declared to140          take arguments.  These values will be substituted in the query.141         </p><p>142          The query plan for a bound cursor is always considered cacheable;143          there is no equivalent of <code class="command">EXECUTE</code> in this case.144          Notice that <code class="literal">SCROLL</code> and <code class="literal">NO SCROLL</code> cannot be145          specified in <code class="command">OPEN</code>, as the cursor's scrolling146          behavior was already determined.147         </p><p>148          Argument values can be passed using either <em class="firstterm">positional</em>149          or <em class="firstterm">named</em> notation.  In positional150          notation, all arguments are specified in order.  In named notation,151          each argument's name is specified using <code class="literal">:=</code> to152          separate it from the argument expression. Similar to calling153          functions, described in <a class="xref" href="sql-syntax-calling-funcs.html" title="4.3. Calling Functions">Section 4.3</a>, it154          is also allowed to mix positional and named notation.155         </p><p>156          Examples (these use the cursor declaration examples above):157</p><pre class="programlisting">158OPEN curs2;159OPEN curs3(42);160OPEN curs3(key := 42);161</pre><p>162         </p><p>163          Because variable substitution is done on a bound cursor's query,164          there are really two ways to pass values into the cursor: either165          with an explicit argument to <code class="command">OPEN</code>, or implicitly by166          referencing a <span class="application">PL/pgSQL</span> variable in the query.167          However, only variables declared before the bound cursor was168          declared will be substituted into it.  In either case the value to169          be passed is determined at the time of the <code class="command">OPEN</code>.170          For example, another way to get the same effect as the171          <code class="literal">curs3</code> example above is172</p><pre class="programlisting">173DECLARE174    key integer;175    curs4 CURSOR FOR SELECT * FROM tenk1 WHERE unique1 = key;176BEGIN177    key := 42;178    OPEN curs4;179</pre><p>180         </p></div></div><div class="sect2" id="PLPGSQL-CURSOR-USING"><div class="titlepage"><div><div><h3 class="title">43.7.3. Using Cursors <a href="#PLPGSQL-CURSOR-USING" class="id_link">#</a></h3></div></div></div><p>181     Once a cursor has been opened, it can be manipulated with the182     statements described here.183    </p><p>184     These manipulations need not occur in the same function that185     opened the cursor to begin with.  You can return a <code class="type">refcursor</code>186     value out of a function and let the caller operate on the cursor.187     (Internally, a <code class="type">refcursor</code> value is simply the string name188     of the portal containing the active query for the cursor.  This name189     can be passed around, assigned to other <code class="type">refcursor</code> variables,190     and so on, without disturbing the portal.)191    </p><p>192     All portals are implicitly closed at transaction end.  Therefore193     a <code class="type">refcursor</code> value is usable to reference an open cursor194     only until the end of the transaction.195    </p><div class="sect3" id="PLPGSQL-CURSOR-USING-FETCH"><div class="titlepage"><div><div><h4 class="title">43.7.3.1. <code class="literal">FETCH</code> <a href="#PLPGSQL-CURSOR-USING-FETCH" class="id_link">#</a></h4></div></div></div><pre class="synopsis">196FETCH [<span class="optional"> <em class="replaceable"><code>direction</code></em> { FROM | IN } </span>] <em class="replaceable"><code>cursor</code></em> INTO <em class="replaceable"><code>target</code></em>;197</pre><p>198     <code class="command">FETCH</code> retrieves the next row from the199     cursor into a target, which might be a row variable, a record200     variable, or a comma-separated list of simple variables, just like201     <code class="command">SELECT INTO</code>.  If there is no next row, the202     target is set to NULL(s).  As with <code class="command">SELECT203     INTO</code>, the special variable <code class="literal">FOUND</code> can204     be checked to see whether a row was obtained or not.205    </p><p>206     The <em class="replaceable"><code>direction</code></em> clause can be any of the207     variants allowed in the SQL <a class="xref" href="sql-fetch.html" title="FETCH"><span class="refentrytitle">FETCH</span></a>208     command except the ones that can fetch209     more than one row; namely, it can be210     <code class="literal">NEXT</code>,211     <code class="literal">PRIOR</code>,212     <code class="literal">FIRST</code>,213     <code class="literal">LAST</code>,214     <code class="literal">ABSOLUTE</code> <em class="replaceable"><code>count</code></em>,215     <code class="literal">RELATIVE</code> <em class="replaceable"><code>count</code></em>,216     <code class="literal">FORWARD</code>, or217     <code class="literal">BACKWARD</code>.218     Omitting <em class="replaceable"><code>direction</code></em> is the same219     as specifying <code class="literal">NEXT</code>.220     In the forms using a <em class="replaceable"><code>count</code></em>,221     the <em class="replaceable"><code>count</code></em> can be any integer-valued222     expression (unlike the SQL <code class="command">FETCH</code> command,223     which only allows an integer constant).224     <em class="replaceable"><code>direction</code></em> values that require moving225     backward are likely to fail unless the cursor was declared or opened226     with the <code class="literal">SCROLL</code> option.227    </p><p>228     <em class="replaceable"><code>cursor</code></em> must be the name of a <code class="type">refcursor</code>229     variable that references an open cursor portal.230    </p><p>231     Examples:232</p><pre class="programlisting">233FETCH curs1 INTO rowvar;234FETCH curs2 INTO foo, bar, baz;235FETCH LAST FROM curs3 INTO x, y;236FETCH RELATIVE -2 FROM curs4 INTO x;237</pre><p>238       </p></div><div class="sect3" id="PLPGSQL-CURSOR-USING-MOVE"><div class="titlepage"><div><div><h4 class="title">43.7.3.2. <code class="literal">MOVE</code> <a href="#PLPGSQL-CURSOR-USING-MOVE" class="id_link">#</a></h4></div></div></div><pre class="synopsis">239MOVE [<span class="optional"> <em class="replaceable"><code>direction</code></em> { FROM | IN } </span>] <em class="replaceable"><code>cursor</code></em>;240</pre><p>241     <code class="command">MOVE</code> repositions a cursor without retrieving242     any data. <code class="command">MOVE</code> works exactly like the243     <code class="command">FETCH</code> command, except it only repositions the244     cursor and does not return the row moved to. As with <code class="command">SELECT245     INTO</code>, the special variable <code class="literal">FOUND</code> can246     be checked to see whether there was a next row to move to.247    </p><p>248     Examples:249</p><pre class="programlisting">250MOVE curs1;251MOVE LAST FROM curs3;252MOVE RELATIVE -2 FROM curs4;253MOVE FORWARD 2 FROM curs4;254</pre><p>255       </p></div><div class="sect3" id="PLPGSQL-CURSOR-USING-UPDATE-DELETE"><div class="titlepage"><div><div><h4 class="title">43.7.3.3. <code class="literal">UPDATE/DELETE WHERE CURRENT OF</code> <a href="#PLPGSQL-CURSOR-USING-UPDATE-DELETE" class="id_link">#</a></h4></div></div></div><pre class="synopsis">256UPDATE <em class="replaceable"><code>table</code></em> SET ... WHERE CURRENT OF <em class="replaceable"><code>cursor</code></em>;257DELETE FROM <em class="replaceable"><code>table</code></em> WHERE CURRENT OF <em class="replaceable"><code>cursor</code></em>;258</pre><p>259        When a cursor is positioned on a table row, that row can be updated260        or deleted using the cursor to identify the row.  There are261        restrictions on what the cursor's query can be (in particular,262        no grouping) and it's best to use <code class="literal">FOR UPDATE</code> in the263        cursor.  For more information see the264        <a class="xref" href="sql-declare.html" title="DECLARE"><span class="refentrytitle">DECLARE</span></a>265        reference page.266       </p><p>267        An example:268</p><pre class="programlisting">269UPDATE foo SET dataval = myval WHERE CURRENT OF curs1;270</pre><p>271       </p></div><div class="sect3" id="PLPGSQL-CURSOR-USING-CLOSE"><div class="titlepage"><div><div><h4 class="title">43.7.3.4. <code class="literal">CLOSE</code> <a href="#PLPGSQL-CURSOR-USING-CLOSE" class="id_link">#</a></h4></div></div></div><pre class="synopsis">272CLOSE <em class="replaceable"><code>cursor</code></em>;273</pre><p>274        <code class="command">CLOSE</code> closes the portal underlying an open275        cursor.  This can be used to release resources earlier than end of276        transaction, or to free up the cursor variable to be opened again.277       </p><p>278        An example:279</p><pre class="programlisting">280CLOSE curs1;281</pre><p>282       </p></div><div class="sect3" id="PLPGSQL-CURSOR-RETURNING"><div class="titlepage"><div><div><h4 class="title">43.7.3.5. Returning Cursors <a href="#PLPGSQL-CURSOR-RETURNING" class="id_link">#</a></h4></div></div></div><p>283        <span class="application">PL/pgSQL</span> functions can return cursors to the284        caller. This is useful to return multiple rows or columns,285        especially with very large result sets.  To do this, the function286        opens the cursor and returns the cursor name to the caller (or simply287        opens the cursor using a portal name specified by or otherwise known288        to the caller).  The caller can then fetch rows from the cursor. The289        cursor can be closed by the caller, or it will be closed automatically290        when the transaction closes.291       </p><p>292        The portal name used for a cursor can be specified by the293        programmer or automatically generated.  To specify a portal name,294        simply assign a string to the <code class="type">refcursor</code> variable before295        opening it.  The string value of the <code class="type">refcursor</code> variable296        will be used by <code class="command">OPEN</code> as the name of the underlying portal.297        However, if the <code class="type">refcursor</code> variable's value is null298        (as it will be by default), then299        <code class="command">OPEN</code> automatically generates a name that does not300        conflict with any existing portal, and assigns it to the301        <code class="type">refcursor</code> variable.302       </p><div class="note"><h3 class="title">Note</h3><p>303         Prior to <span class="productname">PostgreSQL</span> 16, bound cursor304         variables were initialized to contain their own names, rather305         than being left as null, so that the underlying portal name would306         be the same as the cursor variable's name by default.  This was307         changed because it created too much risk of conflicts between308         similarly-named cursors in different functions.309        </p></div><p>310        The following example shows one way a cursor name can be supplied by311        the caller:312 313</p><pre class="programlisting">314CREATE TABLE test (col text);315INSERT INTO test VALUES ('123');316 317CREATE FUNCTION reffunc(refcursor) RETURNS refcursor AS '318BEGIN319    OPEN $1 FOR SELECT col FROM test;320    RETURN $1;321END;322' LANGUAGE plpgsql;323 324BEGIN;325SELECT reffunc('funccursor');326FETCH ALL IN funccursor;327COMMIT;328</pre><p>329       </p><p>330        The following example uses automatic cursor name generation:331 332</p><pre class="programlisting">333CREATE FUNCTION reffunc2() RETURNS refcursor AS '334DECLARE335    ref refcursor;336BEGIN337    OPEN ref FOR SELECT col FROM test;338    RETURN ref;339END;340' LANGUAGE plpgsql;341 342-- need to be in a transaction to use cursors.343BEGIN;344SELECT reffunc2();345 346      reffunc2347--------------------348 &lt;unnamed cursor 1&gt;349(1 row)350 351FETCH ALL IN "&lt;unnamed cursor 1&gt;";352COMMIT;353</pre><p>354       </p><p>355        The following example shows one way to return multiple cursors356        from a single function:357 358</p><pre class="programlisting">359CREATE FUNCTION myfunc(refcursor, refcursor) RETURNS SETOF refcursor AS $$360BEGIN361    OPEN $1 FOR SELECT * FROM table_1;362    RETURN NEXT $1;363    OPEN $2 FOR SELECT * FROM table_2;364    RETURN NEXT $2;365END;366$$ LANGUAGE plpgsql;367 368-- need to be in a transaction to use cursors.369BEGIN;370 371SELECT * FROM myfunc('a', 'b');372 373FETCH ALL FROM a;374FETCH ALL FROM b;375COMMIT;376</pre><p>377       </p></div></div><div class="sect2" id="PLPGSQL-CURSOR-FOR-LOOP"><div class="titlepage"><div><div><h3 class="title">43.7.4. Looping through a Cursor's Result <a href="#PLPGSQL-CURSOR-FOR-LOOP" class="id_link">#</a></h3></div></div></div><p>378     There is a variant of the <code class="command">FOR</code> statement that allows379     iterating through the rows returned by a cursor.  The syntax is:380 381</p><pre class="synopsis">382[<span class="optional"> &lt;&lt;<em class="replaceable"><code>label</code></em>&gt;&gt; </span>]383FOR <em class="replaceable"><code>recordvar</code></em> IN <em class="replaceable"><code>bound_cursorvar</code></em> [<span class="optional"> ( [<span class="optional"> <em class="replaceable"><code>argument_name</code></em> := </span>] <em class="replaceable"><code>argument_value</code></em> [<span class="optional">, ...</span>] ) </span>] LOOP384    <em class="replaceable"><code>statements</code></em>385END LOOP [<span class="optional"> <em class="replaceable"><code>label</code></em> </span>];386</pre><p>387 388     The cursor variable must have been bound to some query when it was389     declared, and it <span class="emphasis"><em>cannot</em></span> be open already.  The390     <code class="command">FOR</code> statement automatically opens the cursor, and it closes391     the cursor again when the loop exits.  A list of actual argument value392     expressions must appear if and only if the cursor was declared to take393     arguments.  These values will be substituted in the query, in just394     the same way as during an <code class="command">OPEN</code> (see <a class="xref" href="plpgsql-cursors.html#PLPGSQL-OPEN-BOUND-CURSOR" title="43.7.2.3. Opening a Bound Cursor">Section 43.7.2.3</a>).395   </p><p>396     The variable <em class="replaceable"><code>recordvar</code></em> is automatically397     defined as type <code class="type">record</code> and exists only inside the loop (any398     existing definition of the variable name is ignored within the loop).399     Each row returned by the cursor is successively assigned to this400     record variable and the loop body is executed.401    </p></div></div><div class="navfooter"><hr /><table width="100%" summary="Navigation footer"><tr><td width="40%" align="left"><a accesskey="p" href="plpgsql-control-structures.html" title="43.6. Control Structures">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-transactions.html" title="43.8. Transaction Management">Next</a></td></tr><tr><td width="40%" align="left" valign="top">43.6. Control Structures </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.8. Transaction Management</td></tr></table></div></body></html>
codekingpro/portable-devtools · Team Ai