Skip to content

Commit f01b196

Browse files
tglsfdctglsfdc
committed
Support scrollable cursors (ie, 'direction' clause in FETCH) in plpgsql.
Pavel Stehule, reworked a bit by Tom.
1 parent 66888f7 commit f01b196

File tree

9 files changed

+421
-101
lines changed

9 files changed

+421
-101
lines changed

‎doc/src/sgml/plpgsql.sgml

Lines changed: 49 additions & 17 deletions
Original file line numberDiff line numberDiff line change
@@ -1,4 +1,4 @@
1-
<!-- $PostgreSQL: pgsql/doc/src/sgml/plpgsql.sgml,v 1.106 2007/04/02 03:49:37 tgl Exp $ -->
1+
<!-- $PostgreSQL: pgsql/doc/src/sgml/plpgsql.sgml,v 1.107 2007/04/16 17:21:22 tgl Exp $ -->
22

33
<chapter id="plpgsql">
44
<title><application>PL/pgSQL</application> - <acronym>SQL</acronym> Procedural Language</title>
@@ -2364,10 +2364,14 @@ SELECT merge_db(1, 'dennis');
23642364
Another way is to use the cursor declaration syntax,
23652365
which in general is:
23662366
<synopsis>
2367-
<replaceable>name</replaceable> CURSOR <optional> ( <replaceable>arguments</replaceable> ) </optional> FOR <replaceable>query</replaceable>;
2367+
<replaceable>name</replaceable> <optional> <optional> NO </optional> SCROLL </optional> CURSOR <optional> ( <replaceable>arguments</replaceable> ) </optional> FOR <replaceable>query</replaceable>;
23682368
</synopsis>
23692369
(<literal>FOR</> can be replaced by <literal>IS</> for
23702370
<productname>Oracle</productname> compatibility.)
2371+
If <literal>SCROLL</> is specified, the cursor will be capable of
2372+
scrolling backward; if <literal>NO SCROLL</> is specified, backward
2373+
fetches will be rejected; if neither specification appears, it is
2374+
query-dependent whether backward fetches will be allowed.
23712375
<replaceable>arguments</replaceable>, if specified, is a
23722376
comma-separated list of pairs <literal><replaceable>name</replaceable>
23732377
<replaceable>datatype</replaceable></literal> that define names to be
@@ -2409,7 +2413,7 @@ DECLARE
24092413
<title><command>OPEN FOR</command> <replaceable>query</replaceable></title>
24102414

24112415
<synopsis>
2412-
OPEN <replaceable>unbound_cursor</replaceable> FOR <replaceable>query</replaceable>;
2416+
OPEN <replaceable>unbound_cursor</replaceable> <optional> <optional> NO </optional> SCROLL </optional> FOR <replaceable>query</replaceable>;
24132417
</synopsis>
24142418

24152419
<para>
@@ -2422,7 +2426,8 @@ OPEN <replaceable>unbound_cursor</replaceable> FOR <replaceable>query</replaceab
24222426
is treated in the same way as other SQL commands in
24232427
<application>PL/pgSQL</>: <application>PL/pgSQL</>
24242428
variable names are substituted, and the query plan is cached for
2425-
possible reuse.
2429+
possible reuse. The <literal>SCROLL</> and <literal>NO SCROLL</>
2430+
options have the same meanings as for a bound cursor.
24262431
</para>
24272432

24282433
<para>
@@ -2437,7 +2442,7 @@ OPEN curs1 FOR SELECT * FROM foo WHERE key = mykey;
24372442
<title><command>OPEN FOR EXECUTE</command></title>
24382443

24392444
<synopsis>
2440-
OPEN <replaceable>unbound_cursor</replaceable> FOR EXECUTE <replaceable class="command">query_string</replaceable>;
2445+
OPEN <replaceable>unbound_cursor</replaceable> <optional> <optional> NO </optional> SCROLL </optional> FOR EXECUTE <replaceable class="command">query_string</replaceable>;
24412446
</synopsis>
24422447

24432448
<para>
@@ -2447,8 +2452,10 @@ OPEN <replaceable>unbound_cursor</replaceable> FOR EXECUTE <replaceable class="c
24472452
<type>refcursor</> variable). The query is specified as a string
24482453
expression, in the same way as in the <command>EXECUTE</command>
24492454
command. As usual, this gives flexibility so the query can vary
2450-
from one run to the next.
2451-
</para>
2455+
from one run to the next. The <literal>SCROLL</> and
2456+
<literal>NO SCROLL</> options have the same meanings as for a bound
2457+
cursor.
2458+
</para>
24522459

24532460
<para>
24542461
An example:
@@ -2473,6 +2480,9 @@ OPEN <replaceable>bound_cursor</replaceable> <optional> ( <replaceable>argument_
24732480
take arguments. These values will be substituted in the query.
24742481
The query plan for a bound cursor is always considered cacheable;
24752482
there is no equivalent of <command>EXECUTE</command> in this case.
2483+
Notice that <literal>SCROLL</> and
2484+
<literal>NO SCROLL</> cannot be specified, as the cursor's scrolling
2485+
behavior was already determined.
24762486
</para>
24772487

24782488
<para>
@@ -2513,23 +2523,45 @@ OPEN curs3(42);
25132523
<title><literal>FETCH</></title>
25142524

25152525
<synopsis>
2516-
FETCH <replaceable>cursor</replaceable> INTO <replaceable>target</replaceable>;
2526+
FETCH <optional> <replaceable>direction</replaceable> FROM </optional> <replaceable>cursor</replaceable> INTO <replaceable>target</replaceable>;
25172527
</synopsis>
25182528

2519-
<para>
2520-
<command>FETCH</command> retrieves the next row from the
2521-
cursor into a target, which might be a row variable, a record
2522-
variable, or a comma-separated list of simple variables, just like
2523-
<command>SELECT INTO</command>. As with <command>SELECT
2524-
INTO</command>, the special variable <literal>FOUND</literal> can
2525-
be checked to see whether a row was obtained or not.
2526-
</para>
2529+
<para>
2530+
<command>FETCH</command> retrieves the next row from the
2531+
cursor into a target, which might be a row variable, a record
2532+
variable, or a comma-separated list of simple variables, just like
2533+
<command>SELECT INTO</command>. As with <command>SELECT
2534+
INTO</command>, the special variable <literal>FOUND</literal> can
2535+
be checked to see whether a row was obtained or not.
2536+
</para>
25272537

25282538
<para>
2529-
An example:
2539+
The <replaceable>direction</replaceable> clause can be any of the
2540+
variants allowed in the SQL <xref linkend="sql-fetch"
2541+
endterm="sql-fetch-title"> command except the ones that can fetch
2542+
more than one row; namely, it can be
2543+
<literal>NEXT</>,
2544+
<literal>PRIOR</>,
2545+
<literal>FIRST</>,
2546+
<literal>LAST</>,
2547+
<literal>ABSOLUTE</> <replaceable>count</replaceable>,
2548+
<literal>RELATIVE</> <replaceable>count</replaceable>,
2549+
<literal>FORWARD</>, or
2550+
<literal>BACKWARD</>.
2551+
Omitting <replaceable>direction</replaceable> is the same
2552+
as specifying <literal>NEXT</>.
2553+
<replaceable>direction</replaceable> values that require moving
2554+
backward are likely to fail unless the cursor was declared or opened
2555+
with the <literal>SCROLL</> option.
2556+
</para>
2557+
2558+
<para>
2559+
Examples:
25302560
<programlisting>
25312561
FETCH curs1 INTO rowvar;
25322562
FETCH curs2 INTO foo, bar, baz;
2563+
FETCH LAST FROM curs3 INTO x, y;
2564+
FETCH RELATIVE -2 FROM curs4 INTO x;
25332565
</programlisting>
25342566
</para>
25352567
</sect3>

‎src/backend/executor/spi.c

Lines changed: 25 additions & 16 deletions
Original file line numberDiff line numberDiff line change
@@ -8,7 +8,7 @@
88
*
99
*
1010
* IDENTIFICATION
11-
* $PostgreSQL: pgsql/src/backend/executor/spi.c,v 1.176 2007/04/16 01:14:56 tgl Exp $
11+
* $PostgreSQL: pgsql/src/backend/executor/spi.c,v 1.177 2007/04/16 17:21:23 tgl Exp $
1212
*
1313
*-------------------------------------------------------------------------
1414
*/
@@ -34,8 +34,7 @@ static int _SPI_stack_depth = 0; /* allocated size of _SPI_stack */
3434
static int _SPI_connected = -1;
3535
static int _SPI_curid = -1;
3636

37-
static void _SPI_prepare_plan(const char *src, SPIPlanPtr plan,
38-
int cursorOptions);
37+
static void _SPI_prepare_plan(const char *src, SPIPlanPtr plan);
3938

4039
static int _SPI_execute_plan(SPIPlanPtr plan,
4140
Datum *Values, const char *Nulls,
@@ -311,8 +310,9 @@ SPI_execute(const char *src, bool read_only, long tcount)
311310

312311
memset(&plan, 0, sizeof(_SPI_plan));
313312
plan.magic = _SPI_PLAN_MAGIC;
313+
plan.cursor_options = 0;
314314

315-
_SPI_prepare_plan(src, &plan, 0);
315+
_SPI_prepare_plan(src, &plan);
316316

317317
res = _SPI_execute_plan(&plan, NULL, NULL,
318318
InvalidSnapshot, InvalidSnapshot,
@@ -423,10 +423,11 @@ SPI_prepare_cursor(const char *src, int nargs, Oid *argtypes,
423423

424424
memset(&plan, 0, sizeof(_SPI_plan));
425425
plan.magic = _SPI_PLAN_MAGIC;
426+
plan.cursor_options = cursorOptions;
426427
plan.nargs = nargs;
427428
plan.argtypes = argtypes;
428429

429-
_SPI_prepare_plan(src, &plan, cursorOptions);
430+
_SPI_prepare_plan(src, &plan);
430431

431432
/* copy plan to procedure context */
432433
result = _SPI_copy_plan(&plan, _SPI_current->procCxt);
@@ -963,15 +964,19 @@ SPI_cursor_open(const char *name, SPIPlanPtr plan,
963964
cplan);
964965

965966
/*
966-
* Set up options for portal.
967+
* Set up options for portal. Default SCROLL type is chosen the same
968+
* way as PerformCursorOpen does it.
967969
*/
968-
portal->cursorOptions &= ~(CURSOR_OPT_SCROLL | CURSOR_OPT_NO_SCROLL);
969-
if (list_length(stmt_list) == 1 &&
970-
IsA((Node *) linitial(stmt_list), PlannedStmt) &&
971-
ExecSupportsBackwardScan(((PlannedStmt *) linitial(stmt_list))->planTree))
972-
portal->cursorOptions |= CURSOR_OPT_SCROLL;
973-
else
974-
portal->cursorOptions |= CURSOR_OPT_NO_SCROLL;
970+
portal->cursorOptions = plan->cursor_options;
971+
if (!(portal->cursorOptions & (CURSOR_OPT_SCROLL | CURSOR_OPT_NO_SCROLL)))
972+
{
973+
if (list_length(stmt_list) == 1 &&
974+
IsA((Node *) linitial(stmt_list), PlannedStmt) &&
975+
ExecSupportsBackwardScan(((PlannedStmt *) linitial(stmt_list))->planTree))
976+
portal->cursorOptions |= CURSOR_OPT_SCROLL;
977+
else
978+
portal->cursorOptions |= CURSOR_OPT_NO_SCROLL;
979+
}
975980

976981
/*
977982
* If told to be read-only, we'd better check for read-only queries.
@@ -1331,21 +1336,23 @@ spi_printtup(TupleTableSlot *slot, DestReceiver *self)
13311336
/*
13321337
* Parse and plan a querystring.
13331338
*
1334-
* At entry, plan->argtypes and plan->nargs must be valid.
1339+
* At entry, plan->argtypes, plan->nargs, and plan->cursor_options must be
1340+
* valid.
13351341
*
13361342
* Results are stored into *plan (specifically, plan->plancache_list).
13371343
* Note however that the result trees are all in CurrentMemoryContext
13381344
* and need to be copied somewhere to survive.
13391345
*/
13401346
static void
1341-
_SPI_prepare_plan(const char *src, SPIPlanPtr plan, int cursorOptions)
1347+
_SPI_prepare_plan(const char *src, SPIPlanPtr plan)
13421348
{
13431349
List *raw_parsetree_list;
13441350
List *plancache_list;
13451351
ListCell *list_item;
13461352
ErrorContextCallback spierrcontext;
13471353
Oid *argtypes = plan->argtypes;
13481354
int nargs = plan->nargs;
1355+
int cursor_options = plan->cursor_options;
13491356

13501357
/*
13511358
* Increment CommandCounter to see changes made by now. We must do this
@@ -1384,7 +1391,7 @@ _SPI_prepare_plan(const char *src, SPIPlanPtr plan, int cursorOptions)
13841391
/* Need a copyObject here to keep parser from modifying raw tree */
13851392
stmt_list = pg_analyze_and_rewrite(copyObject(parsetree),
13861393
src, argtypes, nargs);
1387-
stmt_list = pg_plan_queries(stmt_list, cursorOptions, NULL, false);
1394+
stmt_list = pg_plan_queries(stmt_list, cursor_options, NULL, false);
13881395

13891396
plansource = (CachedPlanSource *) palloc0(sizeof(CachedPlanSource));
13901397
cplan = (CachedPlan *) palloc0(sizeof(CachedPlan));
@@ -1926,6 +1933,7 @@ _SPI_copy_plan(SPIPlanPtr plan, MemoryContext parentcxt)
19261933
newplan->saved = false;
19271934
newplan->plancache_list = NIL;
19281935
newplan->plancxt = plancxt;
1936+
newplan->cursor_options = plan->cursor_options;
19291937
newplan->nargs = plan->nargs;
19301938
if (plan->nargs > 0)
19311939
{
@@ -2000,6 +2008,7 @@ _SPI_save_plan(SPIPlanPtr plan)
20002008
newplan->saved = true;
20012009
newplan->plancache_list = NIL;
20022010
newplan->plancxt = plancxt;
2011+
newplan->cursor_options = plan->cursor_options;
20032012
newplan->nargs = plan->nargs;
20042013
if (plan->nargs > 0)
20052014
{

‎src/include/executor/spi_priv.h

Lines changed: 2 additions & 1 deletion
Original file line numberDiff line numberDiff line change
@@ -6,7 +6,7 @@
66
* Portions Copyright (c) 1996-2007, PostgreSQL Global Development Group
77
* Portions Copyright (c) 1994, Regents of the University of California
88
*
9-
* $PostgreSQL: pgsql/src/include/executor/spi_priv.h,v 1.28 2007/03/15 23:12:07 tgl Exp $
9+
* $PostgreSQL: pgsql/src/include/executor/spi_priv.h,v 1.29 2007/04/16 17:21:23 tgl Exp $
1010
*
1111
*-------------------------------------------------------------------------
1212
*/
@@ -65,6 +65,7 @@ typedef struct _SPI_plan
6565
bool saved; /* saved or unsaved plan? */
6666
List *plancache_list; /* one CachedPlanSource per parsetree */
6767
MemoryContext plancxt; /* Context containing _SPI_plan and data */
68+
int cursor_options; /* Cursor options used for planning */
6869
int nargs; /* number of plan arguments */
6970
Oid *argtypes; /* Argument types (NULL if nargs is 0) */
7071
} _SPI_plan;

0 commit comments

Comments
 (0)