Movatterモバイル変換

[0]ホーム
URL:

Skip to content

Navigation Menu

Sign in
Appearance settings

Search code, repositories, users, issues, pull requests...

Provide feedback

We read every piece of feedback, and take your input very seriously.

Saved searches

Use saved searches to filter your results more quickly

 Sign up
Appearance settings

Commitf01b196

Browse files
committed
Support scrollable cursors (ie, 'direction' clause in FETCH) in plpgsql.
Pavel Stehule, reworked a bit by Tom.
1 parent66888f7 commitf01b196

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/1601:14:56 tgl Exp $
11+
* $PostgreSQL: pgsql/src/backend/executor/spi.c,v 1.177 2007/04/1617:21:23 tgl Exp $
1212
*
1313
*-------------------------------------------------------------------------
1414
*/
@@ -34,8 +34,7 @@ static int_SPI_stack_depth = 0;/* allocated size of _SPI_stack */
3434
staticint_SPI_connected=-1;
3535
staticint_SPI_curid=-1;
3636

37-
staticvoid_SPI_prepare_plan(constchar*src,SPIPlanPtrplan,
38-
intcursorOptions);
37+
staticvoid_SPI_prepare_plan(constchar*src,SPIPlanPtrplan);
3938

4039
staticint_SPI_execute_plan(SPIPlanPtrplan,
4140
Datum*Values,constchar*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
staticvoid
1341-
_SPI_prepare_plan(constchar*src,SPIPlanPtrplan,intcursorOptions)
1347+
_SPI_prepare_plan(constchar*src,SPIPlanPtrplan)
13421348
{
13431349
List*raw_parsetree_list;
13441350
List*plancache_list;
13451351
ListCell*list_item;
13461352
ErrorContextCallbackspierrcontext;
13471353
Oid*argtypes=plan->argtypes;
13481354
intnargs=plan->nargs;
1355+
intcursor_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
boolsaved;/* saved or unsaved plan? */
6666
List*plancache_list;/* one CachedPlanSource per parsetree */
6767
MemoryContextplancxt;/* Context containing _SPI_plan and data */
68+
intcursor_options;/* Cursor options used for planning */
6869
intnargs;/* number of plan arguments */
6970
Oid*argtypes;/* Argument types (NULL if nargs is 0) */
7071
}_SPI_plan;

0 commit comments

Comments
 (0)
[8]ページ先頭
©2009-2025 Movatter.jp