diff --git a/doc/src/sgml/plpgsql.sgml b/doc/src/sgml/plpgsql.sgml index 71e5400422..34f8c5fea7 100644 --- a/doc/src/sgml/plpgsql.sgml +++ b/doc/src/sgml/plpgsql.sgml @@ -3471,7 +3471,7 @@ FOR recordvar IN bound_cursorvar label ; - The cursor variable must have been bound to some query when it was + In this case the cursor variable must have been bound to some query when it was declared, and it cannot be open already. The FOR statement automatically opens the cursor, and it closes the cursor again when the loop exits. A list of actual argument value @@ -3481,6 +3481,21 @@ END LOOP label ; linkend="plpgsql-open-bound-cursor"/>). + + There is a variant for iteration over unbound cursors. + + + <<label>> +FOR recordvar IN unbound_cursorvar LOOP + statements +END LOOP label ; + + + In this case the cursor have to be assigned to opened cursor portal. The + FOR statement in this case doesn't open the cursor and + doesn't close the cursor when loop exists. + + The variable recordvar is automatically defined as type record and exists only inside the loop (any diff --git a/src/pl/plpgsql/src/pl_exec.c b/src/pl/plpgsql/src/pl_exec.c index 9a87cd70f1..392cf9313a 100644 --- a/src/pl/plpgsql/src/pl_exec.c +++ b/src/pl/plpgsql/src/pl_exec.c @@ -2834,6 +2834,45 @@ exec_stmt_forc(PLpgSQL_execstate *estate, PLpgSQL_stmt_forc *stmt) Portal portal; int rc; + curvar = (PLpgSQL_var *) (estate->datums[stmt->curvar]); + + /* ---------- + * REFTYPE cursor support - in this case cursor should be opened, + * so it should be not null with active portal - same prereq like + * FETCH stmt. + * ---------- + */ + if (curvar->cursor_explicit_expr == NULL) + { + MemoryContext oldcontext; + + if (curvar->isnull) + ereport(ERROR, + (errcode(ERRCODE_NULL_VALUE_NOT_ALLOWED), + errmsg("cursor variable \"%s\" is null", curvar->refname))); + + /* Use eval_mcontext for short-lived string */ + oldcontext = MemoryContextSwitchTo(get_eval_mcontext(estate)); + curname = TextDatumGetCString(curvar->value); + MemoryContextSwitchTo(oldcontext); + + portal = SPI_cursor_find(curname); + if (portal == NULL) + ereport(ERROR, + (errcode(ERRCODE_UNDEFINED_CURSOR), + errmsg("cursor \"%s\" does not exist", curname))); + + rc = exec_for_query(estate, (PLpgSQL_stmt_forq *) stmt, portal, false); + + /* + * Don't close portal here - who did portal, then he should + * to close portal. + */ + + return rc; + } + + /* ---------- * Get the cursor variable and if it has an assigned name, check * that it's not in use currently. diff --git a/src/pl/plpgsql/src/pl_gram.y b/src/pl/plpgsql/src/pl_gram.y index 6778d0e771..e372945a82 100644 --- a/src/pl/plpgsql/src/pl_gram.y +++ b/src/pl/plpgsql/src/pl_gram.y @@ -1405,13 +1405,6 @@ for_control : for_variable K_IN errmsg("cursor FOR loop must have only one target variable"), parser_errposition(@1))); - /* can't use an unbound cursor this way */ - if (cursor->cursor_explicit_expr == NULL) - ereport(ERROR, - (errcode(ERRCODE_SYNTAX_ERROR), - errmsg("cursor FOR loop must use a bound cursor variable"), - parser_errposition(tokloc))); - /* collect cursor's parameters if any */ new->argquery = read_cursor_args(cursor, K_LOOP, @@ -3785,7 +3778,7 @@ read_cursor_args(PLpgSQL_var *cursor, int until, const char *expected) bool any_named = false; tok = yylex(); - if (cursor->cursor_explicit_argrow < 0) + if (cursor->cursor_explicit_argrow <= 0) { /* No arguments expected */ if (tok == '(') diff --git a/src/test/regress/expected/plpgsql.out b/src/test/regress/expected/plpgsql.out index d0a6b630b8..95f6bd72c9 100644 --- a/src/test/regress/expected/plpgsql.out +++ b/src/test/regress/expected/plpgsql.out @@ -3607,9 +3607,56 @@ begin end loop; end; $$ language plpgsql; -ERROR: cursor FOR loop must use a bound cursor variable -LINE 5: for r in c loop - ^ +select forc_bad(); +ERROR: cursor variable "c" is null +CONTEXT: PL/pgSQL function forc_bad() line 5 at FOR over cursor +-- test FOR-over-refcursor +create or replace function forc02() +returns void as $$ +declare + c refcursor; + r record; +begin + open c for select * from generate_series(1,20) g(v); + for r in c + loop + raise notice 'cycle body one %', r.v; + exit when r.v >= 10; + end loop; + + for r in c + loop + raise notice 'cycle body two %', r.v; + end loop; +end +$$ language plpgsql; +select forc02(); +NOTICE: cycle body one 1 +NOTICE: cycle body one 2 +NOTICE: cycle body one 3 +NOTICE: cycle body one 4 +NOTICE: cycle body one 5 +NOTICE: cycle body one 6 +NOTICE: cycle body one 7 +NOTICE: cycle body one 8 +NOTICE: cycle body one 9 +NOTICE: cycle body one 10 +NOTICE: cycle body two 11 +NOTICE: cycle body two 12 +NOTICE: cycle body two 13 +NOTICE: cycle body two 14 +NOTICE: cycle body two 15 +NOTICE: cycle body two 16 +NOTICE: cycle body two 17 +NOTICE: cycle body two 18 +NOTICE: cycle body two 19 +NOTICE: cycle body two 20 + forc02 +-------- + +(1 row) + +drop function forc02(); -- test RETURN QUERY EXECUTE create or replace function return_dquery() returns setof int as $$ diff --git a/src/test/regress/sql/plpgsql.sql b/src/test/regress/sql/plpgsql.sql index 07c60c80e4..2ae490c751 100644 --- a/src/test/regress/sql/plpgsql.sql +++ b/src/test/regress/sql/plpgsql.sql @@ -2990,6 +2990,33 @@ begin end; $$ language plpgsql; +select forc_bad(); + +-- test FOR-over-refcursor +create or replace function forc02() +returns void as $$ +declare + c refcursor; + r record; +begin + open c for select * from generate_series(1,20) g(v); + for r in c + loop + raise notice 'cycle body one %', r.v; + exit when r.v >= 10; + end loop; + + for r in c + loop + raise notice 'cycle body two %', r.v; + end loop; +end +$$ language plpgsql; + +select forc02(); + +drop function forc02(); + -- test RETURN QUERY EXECUTE create or replace function return_dquery()