From 8690ebc26f26b2fe39d22eee8b76cab67a96bc09 Mon Sep 17 00:00:00 2001 From: Neil Conway Date: Sun, 29 Apr 2007 01:21:09 +0000 Subject: [PATCH] Support for MOVE in PL/PgSQL. Initial patch from Magnus, some improvements by Pavel Stehule, and reviewed by Neil Conway. --- doc/src/sgml/plpgsql.sgml | 56 +++++++++++++++++++- src/pl/plpgsql/src/gram.y | 20 ++++++- src/pl/plpgsql/src/pl_exec.c | 76 ++++++++++++++++----------- src/pl/plpgsql/src/pl_funcs.c | 63 ++++++++++++++++++---- src/pl/plpgsql/src/plpgsql.h | 5 +- src/pl/plpgsql/src/scan.l | 3 +- src/test/regress/expected/plpgsql.out | 27 ++++++++++ src/test/regress/sql/plpgsql.sql | 23 ++++++++ 8 files changed, 226 insertions(+), 47 deletions(-) diff --git a/doc/src/sgml/plpgsql.sgml b/doc/src/sgml/plpgsql.sgml index 97090b7316..c1f57ddf4f 100644 --- a/doc/src/sgml/plpgsql.sgml +++ b/doc/src/sgml/plpgsql.sgml @@ -1,4 +1,4 @@ - + <application>PL/pgSQL</application> - <acronym>SQL</acronym> Procedural Language @@ -1522,6 +1522,13 @@ GET DIAGNOSTICS integer_var = ROW_COUNT; true if it returns a row, false if no row is returned. + + + A MOVE statement sets FOUND + true if it successfully repositions the cursor, false otherwise. + + + A FOR statement sets FOUND true @@ -2562,6 +2569,53 @@ FETCH curs1 INTO rowvar; FETCH curs2 INTO foo, bar, baz; FETCH LAST FROM curs3 INTO x, y; FETCH RELATIVE -2 FROM curs4 INTO x; + + + + + + <literal>MOVE</> + + +MOVE direction { FROM | IN } cursor; + + + + MOVE repositions a cursor without retrieving + any data. MOVE works exactly like the + FETCH command, except it only positions the + cursor and does not return rows. As with SELECT + INTO, the special variable FOUND can + be checked to see whether the cursor was successfully + repositioned or not. + + + + The direction clause can be any of the + variants allowed in the SQL command except the ones that can move by + more than one row; namely, it can be + NEXT, + PRIOR, + FIRST, + LAST, + ABSOLUTE count, + RELATIVE count, + FORWARD, or + BACKWARD. + Omitting direction is the same + as specifying NEXT. + direction values that require moving + backward are likely to fail unless the cursor was declared or opened + with the SCROLL option. + + + + Examples: + +MOVE curs1; +MOVE LAST FROM curs3; +MOVE RELATIVE -2 FROM curs4; diff --git a/src/pl/plpgsql/src/gram.y b/src/pl/plpgsql/src/gram.y index 48c2ed6785..164a2179a3 100644 --- a/src/pl/plpgsql/src/gram.y +++ b/src/pl/plpgsql/src/gram.y @@ -9,7 +9,7 @@ * * * IDENTIFICATION - * $PostgreSQL: pgsql/src/pl/plpgsql/src/gram.y,v 1.101 2007/04/28 23:54:59 neilc Exp $ + * $PostgreSQL: pgsql/src/pl/plpgsql/src/gram.y,v 1.102 2007/04/29 01:21:09 neilc Exp $ * *------------------------------------------------------------------------- */ @@ -125,7 +125,7 @@ static void check_labels(const char *start_label, %type stmt_assign stmt_if stmt_loop stmt_while stmt_exit %type stmt_return stmt_raise stmt_execsql stmt_execsql_insert %type stmt_dynexecute stmt_for stmt_perform stmt_getdiag -%type stmt_open stmt_fetch stmt_close stmt_null +%type stmt_open stmt_fetch stmt_move stmt_close stmt_null %type proc_exceptions %type exception_sect @@ -179,6 +179,7 @@ static void check_labels(const char *start_label, %token K_IS %token K_LOG %token K_LOOP +%token K_MOVE %token K_NEXT %token K_NOSCROLL %token K_NOT @@ -635,6 +636,8 @@ proc_stmt : pl_block ';' { $$ = $1; } | stmt_fetch { $$ = $1; } + | stmt_move + { $$ = $1; } | stmt_close { $$ = $1; } | stmt_null @@ -1478,6 +1481,19 @@ stmt_fetch : K_FETCH lno opt_fetch_direction cursor_variable K_INTO fetch->rec = rec; fetch->row = row; fetch->curvar = $4->varno; + fetch->is_move = false; + + $$ = (PLpgSQL_stmt *)fetch; + } + ; + +stmt_move : K_MOVE lno opt_fetch_direction cursor_variable ';' + { + PLpgSQL_stmt_fetch *fetch = $3; + + fetch->lineno = $2; + fetch->curvar = $4->varno; + fetch->is_move = true; $$ = (PLpgSQL_stmt *)fetch; } diff --git a/src/pl/plpgsql/src/pl_exec.c b/src/pl/plpgsql/src/pl_exec.c index 620b363bda..af6430f1ff 100644 --- a/src/pl/plpgsql/src/pl_exec.c +++ b/src/pl/plpgsql/src/pl_exec.c @@ -8,7 +8,7 @@ * * * IDENTIFICATION - * $PostgreSQL: pgsql/src/pl/plpgsql/src/pl_exec.c,v 1.195 2007/04/19 16:33:24 tgl Exp $ + * $PostgreSQL: pgsql/src/pl/plpgsql/src/pl_exec.c,v 1.196 2007/04/29 01:21:09 neilc Exp $ * *------------------------------------------------------------------------- */ @@ -3114,7 +3114,8 @@ exec_stmt_open(PLpgSQL_execstate *estate, PLpgSQL_stmt_open *stmt) /* ---------- - * exec_stmt_fetch Fetch from a cursor into a target + * exec_stmt_fetch Fetch from a cursor into a target, or just + * move the current position of the cursor * ---------- */ static int @@ -3163,46 +3164,57 @@ exec_stmt_fetch(PLpgSQL_execstate *estate, PLpgSQL_stmt_fetch *stmt) exec_eval_cleanup(estate); } - /* ---------- - * Determine if we fetch into a record or a row - * ---------- - */ - if (stmt->rec != NULL) - rec = (PLpgSQL_rec *) (estate->datums[stmt->rec->recno]); - else if (stmt->row != NULL) - row = (PLpgSQL_row *) (estate->datums[stmt->row->rowno]); - else - elog(ERROR, "unsupported target"); + if (!stmt->is_move) + { + /* ---------- + * Determine if we fetch into a record or a row + * ---------- + */ + if (stmt->rec != NULL) + rec = (PLpgSQL_rec *) (estate->datums[stmt->rec->recno]); + else if (stmt->row != NULL) + row = (PLpgSQL_row *) (estate->datums[stmt->row->rowno]); + else + elog(ERROR, "unsupported target"); - /* ---------- - * Fetch 1 tuple from the cursor - * ---------- - */ - SPI_scroll_cursor_fetch(portal, stmt->direction, how_many); - tuptab = SPI_tuptable; - n = SPI_processed; + /* ---------- + * Fetch 1 tuple from the cursor + * ---------- + */ + SPI_scroll_cursor_fetch(portal, stmt->direction, how_many); + tuptab = SPI_tuptable; + n = SPI_processed; - /* ---------- - * Set the target and the global FOUND variable appropriately. - * ---------- - */ - if (n == 0) - { - exec_move_row(estate, rec, row, NULL, tuptab->tupdesc); - exec_set_found(estate, false); + /* ---------- + * Set the target and the global FOUND variable appropriately. + * ---------- + */ + if (n == 0) + { + exec_move_row(estate, rec, row, NULL, tuptab->tupdesc); + exec_set_found(estate, false); + } + else + { + exec_move_row(estate, rec, row, tuptab->vals[0], tuptab->tupdesc); + exec_set_found(estate, true); + } + + SPI_freetuptable(tuptab); } else { - exec_move_row(estate, rec, row, tuptab->vals[0], tuptab->tupdesc); - exec_set_found(estate, true); - } + /* Move the cursor */ + SPI_scroll_cursor_move(portal, stmt->direction, how_many); + n = SPI_processed; - SPI_freetuptable(tuptab); + /* Set the global FOUND variable appropriately. */ + exec_set_found(estate, n != 0); + } return PLPGSQL_RC_OK; } - /* ---------- * exec_stmt_close Close a cursor * ---------- diff --git a/src/pl/plpgsql/src/pl_funcs.c b/src/pl/plpgsql/src/pl_funcs.c index da1b2d48fd..c344c9e4ea 100644 --- a/src/pl/plpgsql/src/pl_funcs.c +++ b/src/pl/plpgsql/src/pl_funcs.c @@ -8,7 +8,7 @@ * * * IDENTIFICATION - * $PostgreSQL: pgsql/src/pl/plpgsql/src/pl_funcs.c,v 1.58 2007/03/18 05:36:49 neilc Exp $ + * $PostgreSQL: pgsql/src/pl/plpgsql/src/pl_funcs.c,v 1.59 2007/04/29 01:21:09 neilc Exp $ * *------------------------------------------------------------------------- */ @@ -493,6 +493,7 @@ static void dump_dynfors(PLpgSQL_stmt_dynfors *stmt); static void dump_getdiag(PLpgSQL_stmt_getdiag *stmt); static void dump_open(PLpgSQL_stmt_open *stmt); static void dump_fetch(PLpgSQL_stmt_fetch *stmt); +static void dump_cursor_direction(PLpgSQL_stmt_fetch *stmt); static void dump_close(PLpgSQL_stmt_close *stmt); static void dump_perform(PLpgSQL_stmt_perform *stmt); static void dump_expr(PLpgSQL_expr *expr); @@ -761,21 +762,64 @@ static void dump_fetch(PLpgSQL_stmt_fetch *stmt) { dump_ind(); - printf("FETCH curvar=%d\n", stmt->curvar); + + if (!stmt->is_move) + { + printf("FETCH curvar=%d\n", stmt->curvar); + dump_cursor_direction(stmt); + dump_indent += 2; + if (stmt->rec != NULL) + { + dump_ind(); + printf(" target = %d %s\n", stmt->rec->recno, stmt->rec->refname); + } + if (stmt->row != NULL) + { + dump_ind(); + printf(" target = %d %s\n", stmt->row->rowno, stmt->row->refname); + } + dump_indent -= 2; + } + else + { + printf("MOVE curvar=%d\n", stmt->curvar); + dump_cursor_direction(stmt); + } +} + +static void +dump_cursor_direction(PLpgSQL_stmt_fetch *stmt) +{ dump_indent += 2; - if (stmt->rec != NULL) + dump_ind(); + switch (stmt->direction) { - dump_ind(); - printf(" target = %d %s\n", stmt->rec->recno, stmt->rec->refname); + case FETCH_FORWARD: + printf(" FORWARD "); + break; + case FETCH_BACKWARD: + printf(" BACKWARD "); + break; + case FETCH_ABSOLUTE: + printf(" ABSOLUTE "); + break; + case FETCH_RELATIVE: + printf(" RELATIVE "); + break; + default: + printf("??? unknown cursor direction %d", stmt->direction); } - if (stmt->row != NULL) + + if (stmt->expr) { - dump_ind(); - printf(" target = %d %s\n", stmt->row->rowno, stmt->row->refname); + dump_expr(stmt->expr); + printf("\n"); } + else + printf("%d\n", stmt->how_many); + dump_indent -= 2; - } static void @@ -1067,3 +1111,4 @@ plpgsql_dumptree(PLpgSQL_function *func) printf("\nEnd of execution tree of function %s\n\n", func->fn_name); fflush(stdout); } + diff --git a/src/pl/plpgsql/src/plpgsql.h b/src/pl/plpgsql/src/plpgsql.h index b2c0711310..20ee074564 100644 --- a/src/pl/plpgsql/src/plpgsql.h +++ b/src/pl/plpgsql/src/plpgsql.h @@ -8,7 +8,7 @@ * * * IDENTIFICATION - * $PostgreSQL: pgsql/src/pl/plpgsql/src/plpgsql.h,v 1.87 2007/04/16 17:21:23 tgl Exp $ + * $PostgreSQL: pgsql/src/pl/plpgsql/src/plpgsql.h,v 1.88 2007/04/29 01:21:09 neilc Exp $ * *------------------------------------------------------------------------- */ @@ -446,7 +446,7 @@ typedef struct typedef struct -{ /* FETCH statement */ +{ /* FETCH or MOVE statement */ int cmd_type; int lineno; PLpgSQL_rec *rec; /* target, as record or row */ @@ -455,6 +455,7 @@ typedef struct FetchDirection direction; /* fetch direction */ int how_many; /* count, if constant (expr is NULL) */ PLpgSQL_expr *expr; /* count, if expression */ + bool is_move; /* is this a fetch or move? */ } PLpgSQL_stmt_fetch; diff --git a/src/pl/plpgsql/src/scan.l b/src/pl/plpgsql/src/scan.l index df71faacd8..0d71dd6436 100644 --- a/src/pl/plpgsql/src/scan.l +++ b/src/pl/plpgsql/src/scan.l @@ -9,7 +9,7 @@ * * * IDENTIFICATION - * $PostgreSQL: pgsql/src/pl/plpgsql/src/scan.l,v 1.56 2007/04/16 17:21:23 tgl Exp $ + * $PostgreSQL: pgsql/src/pl/plpgsql/src/scan.l,v 1.57 2007/04/29 01:21:09 neilc Exp $ * *------------------------------------------------------------------------- */ @@ -142,6 +142,7 @@ into { return K_INTO; } is { return K_IS; } log { return K_LOG; } loop { return K_LOOP; } +move { return K_MOVE; } next { return K_NEXT; } no{space}+scroll { return K_NOSCROLL; } not { return K_NOT; } diff --git a/src/test/regress/expected/plpgsql.out b/src/test/regress/expected/plpgsql.out index 669077edee..12a6d4e4e4 100644 --- a/src/test/regress/expected/plpgsql.out +++ b/src/test/regress/expected/plpgsql.out @@ -3023,4 +3023,31 @@ select * from sc_test(); 0 (3 rows) +create or replace function sc_test() returns setof integer as $$ +declare + c cursor for select * from generate_series(1, 10); + x integer; +begin + open c; + loop + move relative 2 in c; + if not found then + exit; + end if; + fetch next from c into x; + if found then + return next x; + end if; + end loop; + close c; +end; +$$ language plpgsql; +select * from sc_test(); + sc_test +--------- + 3 + 6 + 9 +(3 rows) + drop function sc_test(); diff --git a/src/test/regress/sql/plpgsql.sql b/src/test/regress/sql/plpgsql.sql index 33637d9e79..ee9de0a583 100644 --- a/src/test/regress/sql/plpgsql.sql +++ b/src/test/regress/sql/plpgsql.sql @@ -2511,4 +2511,27 @@ $$ language plpgsql; select * from sc_test(); +create or replace function sc_test() returns setof integer as $$ +declare + c cursor for select * from generate_series(1, 10); + x integer; +begin + open c; + loop + move relative 2 in c; + if not found then + exit; + end if; + fetch next from c into x; + if found then + return next x; + end if; + end loop; + close c; +end; +$$ language plpgsql; + +select * from sc_test(); + drop function sc_test(); + -- 2.40.0