Skip to content

Commit 960d7ff

Browse files
tglsfdctglsfdc
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 parent 25549ed commit 960d7ff

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 @@ static PLpgSQL_expr *read_sql_stmt(const char *sqlstart);
4848
static PLpgSQL_type *read_datatype(int tok);
4949
static PLpgSQL_stmt *make_execsql_stmt(const char *sqlstart, int lineno);
5050
static PLpgSQL_stmt_fetch *read_fetch_direction(void);
51+
static void complete_direction(PLpgSQL_stmt_fetch *fetch,
52+
bool *check_FROM);
5153
static PLpgSQL_stmt *make_return_stmt(int lineno);
5254
static PLpgSQL_stmt *make_return_next_stmt(int lineno);
5355
static PLpgSQL_stmt *make_return_query_stmt(int lineno);
@@ -178,6 +180,7 @@ static List *read_raise_options(void);
178180
* Keyword tokens
179181
*/
180182
%token K_ALIAS
183+
%token K_ALL
181184
%token K_ASSIGN
182185
%token K_BEGIN
183186
%token K_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+
else if (pg_strcasecmp(yytext, "all") == 0)
2331+
{
2332+
fetch->how_many = FETCH_ALL;
2333+
fetch->returns_multiple_rows = true;
2334+
}
23142335
else if (pg_strcasecmp(yytext, "forward") == 0)
23152336
{
2316-
/* use defaults */
2337+
complete_direction(fetch, &check_FROM);
23172338
}
23182339
else if (pg_strcasecmp(yytext, "backward") == 0)
23192340
{
23202341
fetch->direction = FETCH_BACKWARD;
2342+
complete_direction(fetch, &check_FROM);
23212343
}
2322-
else if (tok != T_SCALAR)
2344+
else if (tok == K_FROM || tok == K_IN)
23232345
{
2346+
/* empty direction */
2347+
check_FROM = false;
2348+
}
2349+
else if (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+
static void
2391+
complete_direction(PLpgSQL_stmt_fetch *fetch, bool *check_FROM)
2392+
{
2393+
int tok;
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
int curvar; /* cursor variable to fetch from */
519519
FetchDirection direction; /* fetch direction */
520-
int how_many; /* count, if constant (expr is NULL) */
520+
long how_many; /* count, if constant (expr is NULL) */
521521
PLpgSQL_expr *expr; /* count, if expression */
522522
bool is_move; /* is this a fetch or move? */
523+
bool returns_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)