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

Commit960d7ff

Browse files
committed
Allow MOVE FORWARD n, MOVE BACKWARD n, MOVE FORWARD ALL, MOVE BACKWARD ALL
in plpgsql. Clean up a couple of corner cases in the MOVE/FETCH syntax.Pavel Stehule
1 parent25549ed commit960d7ff

File tree

7 files changed

+178
-23
lines changed

7 files changed

+178
-23
lines changed

‎doc/src/sgml/plpgsql.sgml

Lines changed: 8 additions & 5 deletions
Original file line numberDiff line numberDiff line change
@@ -1,4 +1,4 @@
1-
<!-- $PostgreSQL: pgsql/doc/src/sgml/plpgsql.sgml,v 1.142 2009/06/18 10:22:08 petere Exp $ -->
1+
<!-- $PostgreSQL: pgsql/doc/src/sgml/plpgsql.sgml,v 1.143 2009/09/29 20:05:29 tgl Exp $ -->
22

33
<chapter id="plpgsql">
44
<title><application>PL/pgSQL</application> - <acronym>SQL</acronym> Procedural Language</title>
@@ -2655,16 +2655,18 @@ MOVE <optional> <replaceable>direction</replaceable> { FROM | IN } </optional> <
26552655
</para>
26562656

26572657
<para>
2658-
The options for the <replaceable>direction</replaceable> clause are
2659-
the same as for <command>FETCH</>, namely
2658+
The <replaceable>direction</replaceable> clause can be any of the
2659+
variants allowed in the SQL <xref linkend="sql-fetch"
2660+
endterm="sql-fetch-title"> command, namely
26602661
<literal>NEXT</>,
26612662
<literal>PRIOR</>,
26622663
<literal>FIRST</>,
26632664
<literal>LAST</>,
26642665
<literal>ABSOLUTE</> <replaceable>count</replaceable>,
26652666
<literal>RELATIVE</> <replaceable>count</replaceable>,
2666-
<literal>FORWARD</>, or
2667-
<literal>BACKWARD</>.
2667+
<literal>ALL</>,
2668+
<literal>FORWARD</> <optional> <replaceable>count</replaceable> | <literal>ALL</> </optional>, or
2669+
<literal>BACKWARD</> <optional> <replaceable>count</replaceable> | <literal>ALL</> </optional>.
26682670
Omitting <replaceable>direction</replaceable> is the same
26692671
as specifying <literal>NEXT</>.
26702672
<replaceable>direction</replaceable> values that require moving
@@ -2678,6 +2680,7 @@ MOVE <optional> <replaceable>direction</replaceable> { FROM | IN } </optional> <
26782680
MOVE curs1;
26792681
MOVE LAST FROM curs3;
26802682
MOVE RELATIVE -2 FROM curs4;
2683+
MOVE FORWARD 2 FROM curs4;
26812684
</programlisting>
26822685
</para>
26832686
</sect3>

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

Lines changed: 82 additions & 9 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.127 2009/07/22 02:31:38 joe Exp $
12+
* $PostgreSQL: pgsql/src/pl/plpgsql/src/gram.y,v 1.128 2009/09/29 20:05:29 tgl Exp $
1313
*
1414
*-------------------------------------------------------------------------
1515
*/
@@ -48,6 +48,8 @@ staticPLpgSQL_expr*read_sql_stmt(const char *sqlstart);
4848
staticPLpgSQL_type*read_datatype(int tok);
4949
staticPLpgSQL_stmt*make_execsql_stmt(constchar *sqlstart,int lineno);
5050
staticPLpgSQL_stmt_fetch *read_fetch_direction(void);
51+
staticvoidcomplete_direction(PLpgSQL_stmt_fetch *fetch,
52+
bool *check_FROM);
5153
staticPLpgSQL_stmt*make_return_stmt(int lineno);
5254
staticPLpgSQL_stmt*make_return_next_stmt(int lineno);
5355
staticPLpgSQL_stmt*make_return_query_stmt(int lineno);
@@ -178,6 +180,7 @@ static List*read_raise_options(void);
178180
* Keyword tokens
179181
*/
180182
%tokenK_ALIAS
183+
%tokenK_ALL
181184
%tokenK_ASSIGN
182185
%tokenK_BEGIN
183186
%tokenK_BY
@@ -1622,6 +1625,15 @@ stmt_fetch: K_FETCH lno opt_fetch_direction cursor_variable K_INTO
16221625
if (yylex() !=';')
16231626
yyerror("syntax error");
16241627

1628+
/*
1629+
* We don't allow multiple rows in PL/pgSQL's FETCH
1630+
* statement, only in MOVE.
1631+
*/
1632+
if (fetch->returns_multiple_rows)
1633+
ereport(ERROR,
1634+
(errcode(ERRCODE_FEATURE_NOT_SUPPORTED),
1635+
errmsg("FETCH statement cannot return multiple rows")));
1636+
16251637
fetch->lineno =$2;
16261638
fetch->rec= rec;
16271639
fetch->row= row;
@@ -2252,6 +2264,9 @@ make_execsql_stmt(const char *sqlstart, int lineno)
22522264
}
22532265

22542266

2267+
/*
2268+
* Read FETCH or MOVE direction clause (everything through FROM/IN).
2269+
*/
22552270
static PLpgSQL_stmt_fetch *
22562271
read_fetch_direction(void)
22572272
{
@@ -2269,6 +2284,7 @@ read_fetch_direction(void)
22692284
fetch->direction = FETCH_FORWARD;
22702285
fetch->how_many =1;
22712286
fetch->expr =NULL;
2287+
fetch->returns_multiple_rows =false;
22722288

22732289
/*
22742290
* Most of the direction keywords are not plpgsql keywords, so we
@@ -2311,26 +2327,46 @@ read_fetch_direction(void)
23112327
NULL);
23122328
check_FROM =false;
23132329
}
2330+
elseif (pg_strcasecmp(yytext,"all") ==0)
2331+
{
2332+
fetch->how_many = FETCH_ALL;
2333+
fetch->returns_multiple_rows =true;
2334+
}
23142335
elseif (pg_strcasecmp(yytext,"forward") ==0)
23152336
{
2316-
/* use defaults*/
2337+
complete_direction(fetch, &check_FROM);
23172338
}
23182339
elseif (pg_strcasecmp(yytext,"backward") ==0)
23192340
{
23202341
fetch->direction = FETCH_BACKWARD;
2342+
complete_direction(fetch, &check_FROM);
23212343
}
2322-
elseif (tok!= T_SCALAR)
2344+
elseif (tok== K_FROM || tok == K_IN)
23232345
{
2346+
/* empty direction*/
2347+
check_FROM =false;
2348+
}
2349+
elseif (tok == T_SCALAR)
2350+
{
2351+
/* Assume there's no direction clause and tok is a cursor name*/
23242352
plpgsql_push_back_token(tok);
2325-
fetch->expr =read_sql_expression2(K_FROM, K_IN,
2326-
"FROM or IN",
2327-
NULL);
23282353
check_FROM =false;
23292354
}
23302355
else
23312356
{
2332-
/* Assume there's no direction clause*/
2357+
/*
2358+
* Assume it's a count expression with no preceding keyword.
2359+
* Note: we allow this syntax because core SQL does, but we don't
2360+
* document it because of the ambiguity with the omitted-direction
2361+
* case. For instance, "MOVE n IN c" will fail if n is a scalar.
2362+
* Perhaps this can be improved someday, but it's hardly worth a
2363+
* lot of work.
2364+
*/
23332365
plpgsql_push_back_token(tok);
2366+
fetch->expr =read_sql_expression2(K_FROM, K_IN,
2367+
"FROM or IN",
2368+
NULL);
2369+
fetch->returns_multiple_rows =true;
23342370
check_FROM =false;
23352371
}
23362372

@@ -2345,6 +2381,43 @@ read_fetch_direction(void)
23452381
return fetch;
23462382
}
23472383

2384+
/*
2385+
* Process remainder of FETCH/MOVE direction after FORWARD or BACKWARD.
2386+
* Allows these cases:
2387+
* FORWARD expr, FORWARD ALL, FORWARD
2388+
* BACKWARD expr, BACKWARD ALL, BACKWARD
2389+
*/
2390+
staticvoid
2391+
complete_direction(PLpgSQL_stmt_fetch *fetch,bool *check_FROM)
2392+
{
2393+
inttok;
2394+
2395+
tok =yylex();
2396+
if (tok ==0)
2397+
yyerror("unexpected end of function definition");
2398+
2399+
if (tok == K_FROM || tok == K_IN)
2400+
{
2401+
*check_FROM =false;
2402+
return;
2403+
}
2404+
2405+
if (tok == K_ALL)
2406+
{
2407+
fetch->how_many = FETCH_ALL;
2408+
fetch->returns_multiple_rows =true;
2409+
*check_FROM =true;
2410+
return;
2411+
}
2412+
2413+
plpgsql_push_back_token(tok);
2414+
fetch->expr =read_sql_expression2(K_FROM, K_IN,
2415+
"FROM or IN",
2416+
NULL);
2417+
fetch->returns_multiple_rows =true;
2418+
*check_FROM =false;
2419+
}
2420+
23482421

23492422
static PLpgSQL_stmt *
23502423
make_return_stmt(int lineno)
@@ -3043,11 +3116,11 @@ make_case(int lineno, PLpgSQL_expr *t_expr,
30433116

30443117
/* copy expression query without SELECT keyword (expr->query + 7)*/
30453118
Assert(strncmp(expr->query,"SELECT",7) ==0);
3046-
3119+
30473120
/* And do the string hacking*/
30483121
initStringInfo(&ds);
30493122

3050-
appendStringInfo(&ds,"SELECT $%d IN(%s)",
3123+
appendStringInfo(&ds,"SELECT $%d IN(%s)",
30513124
nparams +1,
30523125
expr->query +7);
30533126

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

Lines changed: 2 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/pl_funcs.c,v 1.80 2009/07/22 02:31:38 joe Exp $
11+
* $PostgreSQL: pgsql/src/pl/plpgsql/src/pl_funcs.c,v 1.81 2009/09/29 20:05:29 tgl Exp $
1212
*
1313
*-------------------------------------------------------------------------
1414
*/
@@ -854,7 +854,7 @@ dump_cursor_direction(PLpgSQL_stmt_fetch *stmt)
854854
printf("\n");
855855
}
856856
else
857-
printf("%d\n",stmt->how_many);
857+
printf("%ld\n",stmt->how_many);
858858

859859
dump_indent-=2;
860860
}

‎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.116 2009/09/22 23:43:42 tgl Exp $
11+
* $PostgreSQL: pgsql/src/pl/plpgsql/src/plpgsql.h,v 1.117 2009/09/29 20:05:29 tgl Exp $
1212
*
1313
*-------------------------------------------------------------------------
1414
*/
@@ -517,9 +517,10 @@ typedef struct
517517
PLpgSQL_row*row;
518518
intcurvar;/* cursor variable to fetch from */
519519
FetchDirectiondirection;/* fetch direction */
520-
inthow_many;/* count, if constant (expr is NULL) */
520+
longhow_many;/* count, if constant (expr is NULL) */
521521
PLpgSQL_expr*expr;/* count, if expression */
522522
boolis_move;/* is this a fetch or move? */
523+
boolreturns_multiple_rows;/* can return more than one row? */
523524
}PLpgSQL_stmt_fetch;
524525

525526

‎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.71 2009/07/13 00:42:18 tgl Exp $
12+
* $PostgreSQL: pgsql/src/pl/plpgsql/src/scan.l,v 1.72 2009/09/29 20:05:29 tgl Exp $
1313
*
1414
*-------------------------------------------------------------------------
1515
*/
@@ -147,6 +147,7 @@ param\${digit}+
147147
={return K_ASSIGN;}
148148
\.\.{return K_DOTDOT;}
149149
alias{return K_ALIAS;}
150+
all{return K_ALL;}
150151
begin{return K_BEGIN;}
151152
by{return K_BY; }
152153
case{return K_CASE;}

‎src/test/regress/expected/plpgsql.out

Lines changed: 44 additions & 2 deletions
Original file line numberDiff line numberDiff line change
@@ -3025,6 +3025,28 @@ select * from sc_test();
30253025
0
30263026
(3 rows)
30273027

3028+
create or replace function sc_test() returns setof integer as $$
3029+
declare
3030+
c refcursor;
3031+
x integer;
3032+
begin
3033+
open c scroll for execute 'select f1 from int4_tbl';
3034+
fetch last from c into x;
3035+
while found loop
3036+
return next x;
3037+
move backward 2 from c;
3038+
fetch relative -1 from c into x;
3039+
end loop;
3040+
close c;
3041+
end;
3042+
$$ language plpgsql;
3043+
select * from sc_test();
3044+
sc_test
3045+
-------------
3046+
-2147483647
3047+
123456
3048+
(2 rows)
3049+
30283050
create or replace function sc_test() returns setof integer as $$
30293051
declare
30303052
c cursor for select * from generate_series(1, 10);
@@ -3052,6 +3074,26 @@ select * from sc_test();
30523074
9
30533075
(3 rows)
30543076

3077+
create or replace function sc_test() returns setof integer as $$
3078+
declare
3079+
c cursor for select * from generate_series(1, 10);
3080+
x integer;
3081+
begin
3082+
open c;
3083+
move forward all in c;
3084+
fetch backward from c into x;
3085+
if found then
3086+
return next x;
3087+
end if;
3088+
close c;
3089+
end;
3090+
$$ language plpgsql;
3091+
select * from sc_test();
3092+
sc_test
3093+
---------
3094+
10
3095+
(1 row)
3096+
30553097
drop function sc_test();
30563098
-- test qualified variable names
30573099
create function pl_qual_names (param1 int) returns void as $$
@@ -3864,7 +3906,7 @@ drop function strtest();
38643906
-- Test anonymous code blocks.
38653907
DO $$
38663908
DECLARE r record;
3867-
BEGIN
3909+
BEGIN
38683910
FOR r IN SELECT rtrim(roomno) AS roomno, comment FROM Room ORDER BY roomno
38693911
LOOP
38703912
RAISE NOTICE '%, %', r.roomno, r.comment;
@@ -3887,7 +3929,7 @@ LINE 1: DO LANGUAGE plpgsql $$begin return 1; end$$;
38873929
^
38883930
DO LANGUAGE plpgsql $$
38893931
DECLARE r record;
3890-
BEGIN
3932+
BEGIN
38913933
FOR r IN SELECT rtrim(roomno) AS roomno, foo FROM Room ORDER BY roomno
38923934
LOOP
38933935
RAISE NOTICE '%, %', r.roomno, r.comment;

0 commit comments

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