1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
161
162
163
164
165
166
167
168
169
170
171
172
173
174
175
176
177
178
179
180
181
182
183
184
185
186
187
188
189
190
191
192
193
194
195
196
197
198
199
200
201
202
203
204
205
206
207
208
209
210
211
212
213
214
215
216
217
218
219
220
221
222
223
224
225
226
227
228
229
230
231
232
233
234
235
236
237
238
239
240
241
242
243
244
245
246
247
248
249
250
251
252
253
254
255
256
257
258
259
260
261
262
263
264
265
266
267
268
269
270
271
272
273
274
275
276
277
278
279
280
281
282
283
284
285
286
287
288
289
290
291
292
293
294
295
296
297
298
299
300
301
302
303
304
305
306
307
308
309
310
311
312
313
314
315
316
317
318
319
320
321
322
323
324
325
326
327
328
329
330
331
332
333
334
335
336
337
338
339
340
341
342
343
344
345
346
347
348
349
350
351
352
353
354
355
356
357
358
359
360
361
362
363
364
365
366
367
368
369
370
371
372
373
374
375
376
377
378
379
380
381
382
383
384
385
386
387
388
389
390
391
392
393
394
395
396
397
398
399
400
401
|
<?xml version="1.0" encoding="UTF-8" standalone="no"?>
<!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.2 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>
Rather than executing a whole query at once, it is possible to set
up a <em class="firstterm">cursor</em> that encapsulates the query, and then read
the query result a few rows at a time. One reason for doing this is
to avoid memory overrun when the result contains a large number of
rows. (However, <span class="application">PL/pgSQL</span> users do not normally need
to worry about that, since <code class="literal">FOR</code> loops automatically use a cursor
internally to avoid memory problems.) A more interesting usage is to
return a reference to a cursor that a function has created, allowing the
caller to read the rows. This provides an efficient way to return
large row sets from functions.
</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>
All access to cursors in <span class="application">PL/pgSQL</span> goes through
cursor variables, which are always of the special data type
<code class="type">refcursor</code>. One way to create a cursor variable
is just to declare it as a variable of type <code class="type">refcursor</code>.
Another way is to use the cursor declaration syntax,
which in general is:
</p><pre class="synopsis">
<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>;
</pre><p>
(<code class="literal">FOR</code> can be replaced by <code class="literal">IS</code> for
<span class="productname">Oracle</span> compatibility.)
If <code class="literal">SCROLL</code> is specified, the cursor will be capable of
scrolling backward; if <code class="literal">NO SCROLL</code> is specified, backward
fetches will be rejected; if neither specification appears, it is
query-dependent whether backward fetches will be allowed.
<em class="replaceable"><code>arguments</code></em>, if specified, is a
comma-separated list of pairs <code class="literal"><em class="replaceable"><code>name</code></em>
<em class="replaceable"><code>datatype</code></em></code> that define names to be
replaced by parameter values in the given query. The actual
values to substitute for these names will be specified later,
when the cursor is opened.
</p><p>
Some examples:
</p><pre class="programlisting">
DECLARE
curs1 refcursor;
curs2 CURSOR FOR SELECT * FROM tenk1;
curs3 CURSOR (key integer) FOR SELECT * FROM tenk1 WHERE unique1 = key;
</pre><p>
All three of these variables have the data type <code class="type">refcursor</code>,
but the first can be used with any query, while the second has
a fully specified query already <em class="firstterm">bound</em> to it, and the last
has a parameterized query bound to it. (<code class="literal">key</code> will be
replaced by an integer parameter value when the cursor is opened.)
The variable <code class="literal">curs1</code>
is said to be <em class="firstterm">unbound</em> since it is not bound to
any particular query.
</p><p>
The <code class="literal">SCROLL</code> option cannot be used when the cursor's
query uses <code class="literal">FOR UPDATE/SHARE</code>. Also, it is
best to use <code class="literal">NO SCROLL</code> with a query that involves
volatile functions. The implementation of <code class="literal">SCROLL</code>
assumes that re-reading the query's output will give consistent
results, which a volatile function might not do.
</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>
Before a cursor can be used to retrieve rows, it must be
<em class="firstterm">opened</em>. (This is the equivalent action to the SQL
command <a class="link" href="sql-declare.html" title="DECLARE"><code class="command">DECLARE
CURSOR</code></a>.)
<span class="application">PL/pgSQL</span> has
three forms of the <code class="command">OPEN</code> statement, two of which use unbound
cursor variables while the third uses a bound cursor variable.
</p><div class="note"><h3 class="title">Note</h3><p>
Bound cursor variables can also be used without explicitly opening the cursor,
via the <code class="command">FOR</code> statement described in
<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>.
A <code class="command">FOR</code> loop will open the cursor and then
close it again when the loop completes.
</p></div><a id="id-1.8.8.9.5.4" class="indexterm"></a><p>
Opening a cursor involves creating a server-internal data structure
called a <em class="firstterm">portal</em>, which holds the execution
state for the cursor's query. A portal has a name, which must be
unique within the session for the duration of the portal's existence.
By default, <span class="application">PL/pgSQL</span> will assign a unique
name to each portal it creates. However, if you assign a non-null
string value to a cursor variable, that string will be used as its
portal name. This feature can be used as described in
<a class="xref" href="plpgsql-cursors.html#PLPGSQL-CURSOR-RETURNING" title="43.7.3.5. Returning Cursors">Section 43.7.3.5</a>.
</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">
OPEN <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>;
</pre><p>
The cursor variable is opened and given the specified query to
execute. The cursor cannot be open already, and it must have been
declared as an unbound cursor variable (that is, as a simple
<code class="type">refcursor</code> variable). The query must be a
<code class="command">SELECT</code>, or something else that returns rows
(such as <code class="command">EXPLAIN</code>). The query
is treated in the same way as other SQL commands in
<span class="application">PL/pgSQL</span>: <span class="application">PL/pgSQL</span>
variable names are substituted, and the query plan is cached for
possible reuse. When a <span class="application">PL/pgSQL</span>
variable is substituted into the cursor query, the value that is
substituted is the one it has at the time of the <code class="command">OPEN</code>;
subsequent changes to the variable will not affect the cursor's
behavior.
The <code class="literal">SCROLL</code> and <code class="literal">NO SCROLL</code>
options have the same meanings as for a bound cursor.
</p><p>
An example:
</p><pre class="programlisting">
OPEN curs1 FOR SELECT * FROM foo WHERE key = mykey;
</pre><p>
</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">
OPEN <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>
[<span class="optional"> USING <em class="replaceable"><code>expression</code></em> [<span class="optional">, ... </span>] </span>];
</pre><p>
The cursor variable is opened and given the specified query to
execute. The cursor cannot be open already, and it must have been
declared as an unbound cursor variable (that is, as a simple
<code class="type">refcursor</code> variable). The query is specified as a string
expression, in the same way as in the <code class="command">EXECUTE</code>
command. As usual, this gives flexibility so the query plan can vary
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>),
and it also means that variable substitution is not done on the
command string. As with <code class="command">EXECUTE</code>, parameter values
can be inserted into the dynamic command via
<code class="literal">format()</code> and <code class="literal">USING</code>.
The <code class="literal">SCROLL</code> and
<code class="literal">NO SCROLL</code> options have the same meanings as for a bound
cursor.
</p><p>
An example:
</p><pre class="programlisting">
OPEN curs1 FOR EXECUTE format('SELECT * FROM %I WHERE col1 = $1',tabname) USING keyvalue;
</pre><p>
In this example, the table name is inserted into the query via
<code class="function">format()</code>. The comparison value for <code class="literal">col1</code>
is inserted via a <code class="literal">USING</code> parameter, so it needs
no quoting.
</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">
OPEN <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>];
</pre><p>
This form of <code class="command">OPEN</code> is used to open a cursor
variable whose query was bound to it when it was declared. The
cursor cannot be open already. A list of actual argument value
expressions must appear if and only if the cursor was declared to
take arguments. These values will be substituted in the query.
</p><p>
The query plan for a bound cursor is always considered cacheable;
there is no equivalent of <code class="command">EXECUTE</code> in this case.
Notice that <code class="literal">SCROLL</code> and <code class="literal">NO SCROLL</code> cannot be
specified in <code class="command">OPEN</code>, as the cursor's scrolling
behavior was already determined.
</p><p>
Argument values can be passed using either <em class="firstterm">positional</em>
or <em class="firstterm">named</em> notation. In positional
notation, all arguments are specified in order. In named notation,
each argument's name is specified using <code class="literal">:=</code> to
separate it from the argument expression. Similar to calling
functions, described in <a class="xref" href="sql-syntax-calling-funcs.html" title="4.3. Calling Functions">Section 4.3</a>, it
is also allowed to mix positional and named notation.
</p><p>
Examples (these use the cursor declaration examples above):
</p><pre class="programlisting">
OPEN curs2;
OPEN curs3(42);
OPEN curs3(key := 42);
</pre><p>
</p><p>
Because variable substitution is done on a bound cursor's query,
there are really two ways to pass values into the cursor: either
with an explicit argument to <code class="command">OPEN</code>, or implicitly by
referencing a <span class="application">PL/pgSQL</span> variable in the query.
However, only variables declared before the bound cursor was
declared will be substituted into it. In either case the value to
be passed is determined at the time of the <code class="command">OPEN</code>.
For example, another way to get the same effect as the
<code class="literal">curs3</code> example above is
</p><pre class="programlisting">
DECLARE
key integer;
curs4 CURSOR FOR SELECT * FROM tenk1 WHERE unique1 = key;
BEGIN
key := 42;
OPEN curs4;
</pre><p>
</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>
Once a cursor has been opened, it can be manipulated with the
statements described here.
</p><p>
These manipulations need not occur in the same function that
opened the cursor to begin with. You can return a <code class="type">refcursor</code>
value out of a function and let the caller operate on the cursor.
(Internally, a <code class="type">refcursor</code> value is simply the string name
of the portal containing the active query for the cursor. This name
can be passed around, assigned to other <code class="type">refcursor</code> variables,
and so on, without disturbing the portal.)
</p><p>
All portals are implicitly closed at transaction end. Therefore
a <code class="type">refcursor</code> value is usable to reference an open cursor
only until the end of the transaction.
</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">
FETCH [<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>;
</pre><p>
<code class="command">FETCH</code> retrieves the next row from the
cursor into a target, which might be a row variable, a record
variable, or a comma-separated list of simple variables, just like
<code class="command">SELECT INTO</code>. If there is no next row, the
target is set to NULL(s). As with <code class="command">SELECT
INTO</code>, the special variable <code class="literal">FOUND</code> can
be checked to see whether a row was obtained or not.
</p><p>
The <em class="replaceable"><code>direction</code></em> clause can be any of the
variants allowed in the SQL <a class="xref" href="sql-fetch.html" title="FETCH"><span class="refentrytitle">FETCH</span></a>
command except the ones that can fetch
more than one row; namely, it can be
<code class="literal">NEXT</code>,
<code class="literal">PRIOR</code>,
<code class="literal">FIRST</code>,
<code class="literal">LAST</code>,
<code class="literal">ABSOLUTE</code> <em class="replaceable"><code>count</code></em>,
<code class="literal">RELATIVE</code> <em class="replaceable"><code>count</code></em>,
<code class="literal">FORWARD</code>, or
<code class="literal">BACKWARD</code>.
Omitting <em class="replaceable"><code>direction</code></em> is the same
as specifying <code class="literal">NEXT</code>.
In the forms using a <em class="replaceable"><code>count</code></em>,
the <em class="replaceable"><code>count</code></em> can be any integer-valued
expression (unlike the SQL <code class="command">FETCH</code> command,
which only allows an integer constant).
<em class="replaceable"><code>direction</code></em> values that require moving
backward are likely to fail unless the cursor was declared or opened
with the <code class="literal">SCROLL</code> option.
</p><p>
<em class="replaceable"><code>cursor</code></em> must be the name of a <code class="type">refcursor</code>
variable that references an open cursor portal.
</p><p>
Examples:
</p><pre class="programlisting">
FETCH curs1 INTO rowvar;
FETCH curs2 INTO foo, bar, baz;
FETCH LAST FROM curs3 INTO x, y;
FETCH RELATIVE -2 FROM curs4 INTO x;
</pre><p>
</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">
MOVE [<span class="optional"> <em class="replaceable"><code>direction</code></em> { FROM | IN } </span>] <em class="replaceable"><code>cursor</code></em>;
</pre><p>
<code class="command">MOVE</code> repositions a cursor without retrieving
any data. <code class="command">MOVE</code> works exactly like the
<code class="command">FETCH</code> command, except it only repositions the
cursor and does not return the row moved to. As with <code class="command">SELECT
INTO</code>, the special variable <code class="literal">FOUND</code> can
be checked to see whether there was a next row to move to.
</p><p>
Examples:
</p><pre class="programlisting">
MOVE curs1;
MOVE LAST FROM curs3;
MOVE RELATIVE -2 FROM curs4;
MOVE FORWARD 2 FROM curs4;
</pre><p>
</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">
UPDATE <em class="replaceable"><code>table</code></em> SET ... WHERE CURRENT OF <em class="replaceable"><code>cursor</code></em>;
DELETE FROM <em class="replaceable"><code>table</code></em> WHERE CURRENT OF <em class="replaceable"><code>cursor</code></em>;
</pre><p>
When a cursor is positioned on a table row, that row can be updated
or deleted using the cursor to identify the row. There are
restrictions on what the cursor's query can be (in particular,
no grouping) and it's best to use <code class="literal">FOR UPDATE</code> in the
cursor. For more information see the
<a class="xref" href="sql-declare.html" title="DECLARE"><span class="refentrytitle">DECLARE</span></a>
reference page.
</p><p>
An example:
</p><pre class="programlisting">
UPDATE foo SET dataval = myval WHERE CURRENT OF curs1;
</pre><p>
</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">
CLOSE <em class="replaceable"><code>cursor</code></em>;
</pre><p>
<code class="command">CLOSE</code> closes the portal underlying an open
cursor. This can be used to release resources earlier than end of
transaction, or to free up the cursor variable to be opened again.
</p><p>
An example:
</p><pre class="programlisting">
CLOSE curs1;
</pre><p>
</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>
<span class="application">PL/pgSQL</span> functions can return cursors to the
caller. This is useful to return multiple rows or columns,
especially with very large result sets. To do this, the function
opens the cursor and returns the cursor name to the caller (or simply
opens the cursor using a portal name specified by or otherwise known
to the caller). The caller can then fetch rows from the cursor. The
cursor can be closed by the caller, or it will be closed automatically
when the transaction closes.
</p><p>
The portal name used for a cursor can be specified by the
programmer or automatically generated. To specify a portal name,
simply assign a string to the <code class="type">refcursor</code> variable before
opening it. The string value of the <code class="type">refcursor</code> variable
will be used by <code class="command">OPEN</code> as the name of the underlying portal.
However, if the <code class="type">refcursor</code> variable's value is null
(as it will be by default), then
<code class="command">OPEN</code> automatically generates a name that does not
conflict with any existing portal, and assigns it to the
<code class="type">refcursor</code> variable.
</p><div class="note"><h3 class="title">Note</h3><p>
Prior to <span class="productname">PostgreSQL</span> 16, bound cursor
variables were initialized to contain their own names, rather
than being left as null, so that the underlying portal name would
be the same as the cursor variable's name by default. This was
changed because it created too much risk of conflicts between
similarly-named cursors in different functions.
</p></div><p>
The following example shows one way a cursor name can be supplied by
the caller:
</p><pre class="programlisting">
CREATE TABLE test (col text);
INSERT INTO test VALUES ('123');
CREATE FUNCTION reffunc(refcursor) RETURNS refcursor AS '
BEGIN
OPEN $1 FOR SELECT col FROM test;
RETURN $1;
END;
' LANGUAGE plpgsql;
BEGIN;
SELECT reffunc('funccursor');
FETCH ALL IN funccursor;
COMMIT;
</pre><p>
</p><p>
The following example uses automatic cursor name generation:
</p><pre class="programlisting">
CREATE FUNCTION reffunc2() RETURNS refcursor AS '
DECLARE
ref refcursor;
BEGIN
OPEN ref FOR SELECT col FROM test;
RETURN ref;
END;
' LANGUAGE plpgsql;
-- need to be in a transaction to use cursors.
BEGIN;
SELECT reffunc2();
reffunc2
--------------------
<unnamed cursor 1>
(1 row)
FETCH ALL IN "<unnamed cursor 1>";
COMMIT;
</pre><p>
</p><p>
The following example shows one way to return multiple cursors
from a single function:
</p><pre class="programlisting">
CREATE FUNCTION myfunc(refcursor, refcursor) RETURNS SETOF refcursor AS $$
BEGIN
OPEN $1 FOR SELECT * FROM table_1;
RETURN NEXT $1;
OPEN $2 FOR SELECT * FROM table_2;
RETURN NEXT $2;
END;
$$ LANGUAGE plpgsql;
-- need to be in a transaction to use cursors.
BEGIN;
SELECT * FROM myfunc('a', 'b');
FETCH ALL FROM a;
FETCH ALL FROM b;
COMMIT;
</pre><p>
</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>
There is a variant of the <code class="command">FOR</code> statement that allows
iterating through the rows returned by a cursor. The syntax is:
</p><pre class="synopsis">
[<span class="optional"> <<<em class="replaceable"><code>label</code></em>>> </span>]
FOR <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>] LOOP
<em class="replaceable"><code>statements</code></em>
END LOOP [<span class="optional"> <em class="replaceable"><code>label</code></em> </span>];
</pre><p>
The cursor variable must have been bound to some query when it was
declared, and it <span class="emphasis"><em>cannot</em></span> be open already. The
<code class="command">FOR</code> statement automatically opens the cursor, and it closes
the cursor again when the loop exits. A list of actual argument value
expressions must appear if and only if the cursor was declared to take
arguments. These values will be substituted in the query, in just
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>).
</p><p>
The variable <em class="replaceable"><code>recordvar</code></em> is automatically
defined as type <code class="type">record</code> and exists only inside the loop (any
existing definition of the variable name is ignored within the loop).
Each row returned by the cursor is successively assigned to this
record variable and the loop body is executed.
</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.2 Documentation">Home</a></td><td width="40%" align="right" valign="top"> 43.8. Transaction Management</td></tr></table></div></body></html>
|
