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

Commit8690ebc

Browse files
author
Neil Conway
committed
Support for MOVE in PL/PgSQL. Initial patch from Magnus, some improvements
by Pavel Stehule, and reviewed by Neil Conway.
1 parentf2321a3 commit8690ebc

File tree

8 files changed

+226
-47
lines changed

8 files changed

+226
-47
lines changed

‎doc/src/sgml/plpgsql.sgml

Lines changed: 55 additions & 1 deletion
Original file line numberDiff line numberDiff line change
@@ -1,4 +1,4 @@
1-
<!-- $PostgreSQL: pgsql/doc/src/sgml/plpgsql.sgml,v 1.108 2007/04/28 23:54:58 neilc Exp $ -->
1+
<!-- $PostgreSQL: pgsql/doc/src/sgml/plpgsql.sgml,v 1.109 2007/04/29 01:21:08 neilc Exp $ -->
22

33
<chapter id="plpgsql">
44
<title><application>PL/pgSQL</application> - <acronym>SQL</acronym> Procedural Language</title>
@@ -1522,6 +1522,13 @@ GET DIAGNOSTICS integer_var = ROW_COUNT;
15221522
true if it returns a row, false if no row is returned.
15231523
</para>
15241524
</listitem>
1525+
<listitem>
1526+
<para>
1527+
A <command>MOVE</> statement sets <literal>FOUND</literal>
1528+
true if it successfully repositions the cursor, false otherwise.
1529+
</para>
1530+
</listitem>
1531+
15251532
<listitem>
15261533
<para>
15271534
A <command>FOR</> statement sets <literal>FOUND</literal> true
@@ -2562,6 +2569,53 @@ FETCH curs1 INTO rowvar;
25622569
FETCH curs2 INTO foo, bar, baz;
25632570
FETCH LAST FROM curs3 INTO x, y;
25642571
FETCH RELATIVE -2 FROM curs4 INTO x;
2572+
</programlisting>
2573+
</para>
2574+
</sect3>
2575+
2576+
<sect3>
2577+
<title><literal>MOVE</></title>
2578+
2579+
<synopsis>
2580+
MOVE <optional> <replaceable>direction</replaceable> { FROM | IN } </optional> <replaceable>cursor</replaceable>;
2581+
</synopsis>
2582+
2583+
<para>
2584+
<command>MOVE</command> repositions a cursor without retrieving
2585+
any data. <command>MOVE</command> works exactly like the
2586+
<command>FETCH</command> command, except it only positions the
2587+
cursor and does not return rows. As with <command>SELECT
2588+
INTO</command>, the special variable <literal>FOUND</literal> can
2589+
be checked to see whether the cursor was successfully
2590+
repositioned or not.
2591+
</para>
2592+
2593+
<para>
2594+
The <replaceable>direction</replaceable> clause can be any of the
2595+
variants allowed in the SQL <xref linkend="sql-move"
2596+
endterm="sql-move-title"> command except the ones that can move by
2597+
more than one row; namely, it can be
2598+
<literal>NEXT</>,
2599+
<literal>PRIOR</>,
2600+
<literal>FIRST</>,
2601+
<literal>LAST</>,
2602+
<literal>ABSOLUTE</> <replaceable>count</replaceable>,
2603+
<literal>RELATIVE</> <replaceable>count</replaceable>,
2604+
<literal>FORWARD</>, or
2605+
<literal>BACKWARD</>.
2606+
Omitting <replaceable>direction</replaceable> is the same
2607+
as specifying <literal>NEXT</>.
2608+
<replaceable>direction</replaceable> values that require moving
2609+
backward are likely to fail unless the cursor was declared or opened
2610+
with the <literal>SCROLL</> option.
2611+
</para>
2612+
2613+
<para>
2614+
Examples:
2615+
<programlisting>
2616+
MOVE curs1;
2617+
MOVE LAST FROM curs3;
2618+
MOVE RELATIVE -2 FROM curs4;
25652619
</programlisting>
25662620
</para>
25672621
</sect3>

‎src/pl/plpgsql/src/gram.y

Lines changed: 18 additions & 2 deletions
Original file line numberDiff line numberDiff line change
@@ -9,7 +9,7 @@
99
*
1010
*
1111
* IDENTIFICATION
12-
* $PostgreSQL: pgsql/src/pl/plpgsql/src/gram.y,v 1.101 2007/04/28 23:54:59 neilc Exp $
12+
* $PostgreSQL: pgsql/src/pl/plpgsql/src/gram.y,v 1.102 2007/04/29 01:21:09 neilc Exp $
1313
*
1414
*-------------------------------------------------------------------------
1515
*/
@@ -125,7 +125,7 @@ staticvoid check_labels(const char *start_label,
125125
%type<stmt>stmt_assignstmt_ifstmt_loopstmt_whilestmt_exit
126126
%type<stmt>stmt_returnstmt_raisestmt_execsqlstmt_execsql_insert
127127
%type<stmt>stmt_dynexecutestmt_forstmt_performstmt_getdiag
128-
%type<stmt>stmt_openstmt_fetchstmt_closestmt_null
128+
%type<stmt>stmt_openstmt_fetchstmt_movestmt_closestmt_null
129129

130130
%type<list>proc_exceptions
131131
%type<exception_block>exception_sect
@@ -179,6 +179,7 @@ staticvoid check_labels(const char *start_label,
179179
%tokenK_IS
180180
%tokenK_LOG
181181
%tokenK_LOOP
182+
%tokenK_MOVE
182183
%tokenK_NEXT
183184
%tokenK_NOSCROLL
184185
%tokenK_NOT
@@ -635,6 +636,8 @@ proc_stmt: pl_block ';'
635636
{$$ =$1; }
636637
|stmt_fetch
637638
{$$ =$1; }
639+
|stmt_move
640+
{$$ =$1; }
638641
|stmt_close
639642
{$$ =$1; }
640643
|stmt_null
@@ -1478,6 +1481,19 @@ stmt_fetch: K_FETCH lno opt_fetch_direction cursor_variable K_INTO
14781481
fetch->rec= rec;
14791482
fetch->row= row;
14801483
fetch->curvar=$4->varno;
1484+
fetch->is_move=false;
1485+
1486+
$$ = (PLpgSQL_stmt *)fetch;
1487+
}
1488+
;
1489+
1490+
stmt_move:K_MOVElnoopt_fetch_directioncursor_variable';'
1491+
{
1492+
PLpgSQL_stmt_fetch *fetch =$3;
1493+
1494+
fetch->lineno =$2;
1495+
fetch->curvar=$4->varno;
1496+
fetch->is_move=true;
14811497

14821498
$$ = (PLpgSQL_stmt *)fetch;
14831499
}

‎src/pl/plpgsql/src/pl_exec.c

Lines changed: 44 additions & 32 deletions
Original file line numberDiff line numberDiff line change
@@ -8,7 +8,7 @@
88
*
99
*
1010
* IDENTIFICATION
11-
* $PostgreSQL: pgsql/src/pl/plpgsql/src/pl_exec.c,v 1.195 2007/04/19 16:33:24 tgl Exp $
11+
* $PostgreSQL: pgsql/src/pl/plpgsql/src/pl_exec.c,v 1.196 2007/04/29 01:21:09 neilc Exp $
1212
*
1313
*-------------------------------------------------------------------------
1414
*/
@@ -3114,7 +3114,8 @@ exec_stmt_open(PLpgSQL_execstate *estate, PLpgSQL_stmt_open *stmt)
31143114

31153115

31163116
/* ----------
3117-
* exec_stmt_fetchFetch from a cursor into a target
3117+
* exec_stmt_fetchFetch from a cursor into a target, or just
3118+
* move the current position of the cursor
31183119
* ----------
31193120
*/
31203121
staticint
@@ -3163,46 +3164,57 @@ exec_stmt_fetch(PLpgSQL_execstate *estate, PLpgSQL_stmt_fetch *stmt)
31633164
exec_eval_cleanup(estate);
31643165
}
31653166

3166-
/* ----------
3167-
* Determine if we fetch into a record or a row
3168-
* ----------
3169-
*/
3170-
if (stmt->rec!=NULL)
3171-
rec= (PLpgSQL_rec*) (estate->datums[stmt->rec->recno]);
3172-
elseif (stmt->row!=NULL)
3173-
row= (PLpgSQL_row*) (estate->datums[stmt->row->rowno]);
3174-
else
3175-
elog(ERROR,"unsupported target");
3167+
if (!stmt->is_move)
3168+
{
3169+
/* ----------
3170+
* Determine if we fetch into a record or a row
3171+
* ----------
3172+
*/
3173+
if (stmt->rec!=NULL)
3174+
rec= (PLpgSQL_rec*) (estate->datums[stmt->rec->recno]);
3175+
elseif (stmt->row!=NULL)
3176+
row= (PLpgSQL_row*) (estate->datums[stmt->row->rowno]);
3177+
else
3178+
elog(ERROR,"unsupported target");
31763179

3177-
/* ----------
3178-
* Fetch 1 tuple from the cursor
3179-
* ----------
3180-
*/
3181-
SPI_scroll_cursor_fetch(portal,stmt->direction,how_many);
3182-
tuptab=SPI_tuptable;
3183-
n=SPI_processed;
3180+
/* ----------
3181+
* Fetch 1 tuple from the cursor
3182+
* ----------
3183+
*/
3184+
SPI_scroll_cursor_fetch(portal,stmt->direction,how_many);
3185+
tuptab=SPI_tuptable;
3186+
n=SPI_processed;
31843187

3185-
/* ----------
3186-
* Set the target and the global FOUND variable appropriately.
3187-
* ----------
3188-
*/
3189-
if (n==0)
3190-
{
3191-
exec_move_row(estate,rec,row,NULL,tuptab->tupdesc);
3192-
exec_set_found(estate, false);
3188+
/* ----------
3189+
* Set the target and the global FOUND variable appropriately.
3190+
* ----------
3191+
*/
3192+
if (n==0)
3193+
{
3194+
exec_move_row(estate,rec,row,NULL,tuptab->tupdesc);
3195+
exec_set_found(estate, false);
3196+
}
3197+
else
3198+
{
3199+
exec_move_row(estate,rec,row,tuptab->vals[0],tuptab->tupdesc);
3200+
exec_set_found(estate, true);
3201+
}
3202+
3203+
SPI_freetuptable(tuptab);
31933204
}
31943205
else
31953206
{
3196-
exec_move_row(estate,rec,row,tuptab->vals[0],tuptab->tupdesc);
3197-
exec_set_found(estate, true);
3198-
}
3207+
/* Move the cursor */
3208+
SPI_scroll_cursor_move(portal,stmt->direction,how_many);
3209+
n=SPI_processed;
31993210

3200-
SPI_freetuptable(tuptab);
3211+
/* Set the global FOUND variable appropriately. */
3212+
exec_set_found(estate,n!=0);
3213+
}
32013214

32023215
returnPLPGSQL_RC_OK;
32033216
}
32043217

3205-
32063218
/* ----------
32073219
* exec_stmt_closeClose a cursor
32083220
* ----------

‎src/pl/plpgsql/src/pl_funcs.c

Lines changed: 54 additions & 9 deletions
Original file line numberDiff line numberDiff line change
@@ -8,7 +8,7 @@
88
*
99
*
1010
* IDENTIFICATION
11-
* $PostgreSQL: pgsql/src/pl/plpgsql/src/pl_funcs.c,v 1.58 2007/03/18 05:36:49 neilc Exp $
11+
* $PostgreSQL: pgsql/src/pl/plpgsql/src/pl_funcs.c,v 1.59 2007/04/29 01:21:09 neilc Exp $
1212
*
1313
*-------------------------------------------------------------------------
1414
*/
@@ -493,6 +493,7 @@ static void dump_dynfors(PLpgSQL_stmt_dynfors *stmt);
493493
staticvoiddump_getdiag(PLpgSQL_stmt_getdiag*stmt);
494494
staticvoiddump_open(PLpgSQL_stmt_open*stmt);
495495
staticvoiddump_fetch(PLpgSQL_stmt_fetch*stmt);
496+
staticvoiddump_cursor_direction(PLpgSQL_stmt_fetch*stmt);
496497
staticvoiddump_close(PLpgSQL_stmt_close*stmt);
497498
staticvoiddump_perform(PLpgSQL_stmt_perform*stmt);
498499
staticvoiddump_expr(PLpgSQL_expr*expr);
@@ -761,21 +762,64 @@ static void
761762
dump_fetch(PLpgSQL_stmt_fetch*stmt)
762763
{
763764
dump_ind();
764-
printf("FETCH curvar=%d\n",stmt->curvar);
765+
766+
if (!stmt->is_move)
767+
{
768+
printf("FETCH curvar=%d\n",stmt->curvar);
769+
dump_cursor_direction(stmt);
765770

771+
dump_indent+=2;
772+
if (stmt->rec!=NULL)
773+
{
774+
dump_ind();
775+
printf(" target = %d %s\n",stmt->rec->recno,stmt->rec->refname);
776+
}
777+
if (stmt->row!=NULL)
778+
{
779+
dump_ind();
780+
printf(" target = %d %s\n",stmt->row->rowno,stmt->row->refname);
781+
}
782+
dump_indent-=2;
783+
}
784+
else
785+
{
786+
printf("MOVE curvar=%d\n",stmt->curvar);
787+
dump_cursor_direction(stmt);
788+
}
789+
}
790+
791+
staticvoid
792+
dump_cursor_direction(PLpgSQL_stmt_fetch*stmt)
793+
{
766794
dump_indent+=2;
767-
if (stmt->rec!=NULL)
795+
dump_ind();
796+
switch (stmt->direction)
768797
{
769-
dump_ind();
770-
printf(" target = %d %s\n",stmt->rec->recno,stmt->rec->refname);
798+
caseFETCH_FORWARD:
799+
printf(" FORWARD ");
800+
break;
801+
caseFETCH_BACKWARD:
802+
printf(" BACKWARD ");
803+
break;
804+
caseFETCH_ABSOLUTE:
805+
printf(" ABSOLUTE ");
806+
break;
807+
caseFETCH_RELATIVE:
808+
printf(" RELATIVE ");
809+
break;
810+
default:
811+
printf("??? unknown cursor direction %d",stmt->direction);
771812
}
772-
if (stmt->row!=NULL)
813+
814+
if (stmt->expr)
773815
{
774-
dump_ind();
775-
printf(" target = %d %s\n",stmt->row->rowno,stmt->row->refname);
816+
dump_expr(stmt->expr);
817+
printf("\n");
776818
}
819+
else
820+
printf("%d\n",stmt->how_many);
821+
777822
dump_indent-=2;
778-
779823
}
780824

781825
staticvoid
@@ -1067,3 +1111,4 @@ plpgsql_dumptree(PLpgSQL_function *func)
10671111
printf("\nEnd of execution tree of function %s\n\n",func->fn_name);
10681112
fflush(stdout);
10691113
}
1114+

‎src/pl/plpgsql/src/plpgsql.h

Lines changed: 3 additions & 2 deletions
Original file line numberDiff line numberDiff line change
@@ -8,7 +8,7 @@
88
*
99
*
1010
* IDENTIFICATION
11-
* $PostgreSQL: pgsql/src/pl/plpgsql/src/plpgsql.h,v 1.87 2007/04/16 17:21:23 tgl Exp $
11+
* $PostgreSQL: pgsql/src/pl/plpgsql/src/plpgsql.h,v 1.88 2007/04/29 01:21:09 neilc Exp $
1212
*
1313
*-------------------------------------------------------------------------
1414
*/
@@ -446,7 +446,7 @@ typedef struct
446446

447447

448448
typedefstruct
449-
{/* FETCH statement */
449+
{/* FETCHor MOVEstatement */
450450
intcmd_type;
451451
intlineno;
452452
PLpgSQL_rec*rec;/* target, as record or row */
@@ -455,6 +455,7 @@ typedef struct
455455
FetchDirectiondirection;/* fetch direction */
456456
inthow_many;/* count, if constant (expr is NULL) */
457457
PLpgSQL_expr*expr;/* count, if expression */
458+
boolis_move;/* is this a fetch or move? */
458459
}PLpgSQL_stmt_fetch;
459460

460461

‎src/pl/plpgsql/src/scan.l

Lines changed: 2 additions & 1 deletion
Original file line numberDiff line numberDiff line change
@@ -9,7 +9,7 @@
99
*
1010
*
1111
* IDENTIFICATION
12-
* $PostgreSQL: pgsql/src/pl/plpgsql/src/scan.l,v 1.56 2007/04/16 17:21:23 tgl Exp $
12+
* $PostgreSQL: pgsql/src/pl/plpgsql/src/scan.l,v 1.57 2007/04/29 01:21:09 neilc Exp $
1313
*
1414
*-------------------------------------------------------------------------
1515
*/
@@ -142,6 +142,7 @@ into{ return K_INTO;}
142142
is{return K_IS;}
143143
log{return K_LOG;}
144144
loop{return K_LOOP;}
145+
move{return K_MOVE;}
145146
next{return K_NEXT;}
146147
no{space}+scroll {return K_NOSCROLL;}
147148
not{return K_NOT;}

0 commit comments

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