Hi
Last week I played with dbms_sql extension and some patterns of usage
cursor in PL/SQL and PL/pgSQL. I found fact, so iteration over cursor (FOR
statement) doesn't support unbound cursors. I think so this limit is not
necessary. This statement can open portal for bound cursor or can iterate
over before opened portal. When portal was opened inside FOR statement,
then it is closed inside this statement.
Implementation is simple, usage is simple too:
CREATE OR REPLACE FUNCTION public.forc02()
RETURNS void
LANGUAGE plpgsql
AS $function$
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 >= 6;
end loop;
for r in c
loop
raise notice 'cycle body two %', r.v;
end loop;
close c;
end
$function$
Comments, notes?
Regards
Pavel
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 <replaceable>recordvar</replaceable> IN <replaceable>bound_cursorvar</replac
END LOOP <optional> <replaceable>label</replaceable> </optional>;
</synopsis>
- 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 <emphasis>cannot</emphasis> be open already. The
<command>FOR</command> 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 <optional> <replaceable>label</replaceable> </optional>;
linkend="plpgsql-open-bound-cursor"/>).
</para>
+ <para>
+ There is a variant for iteration over unbound cursors.
+
+<synopsis>
+<optional> <<<replaceable>label</replaceable>>> </optional>
+FOR <replaceable>recordvar</replaceable> IN <replaceable>unbound_cursorvar</replaceable> LOOP
+ <replaceable>statements</replaceable>
+END LOOP <optional> <replaceable>label</replaceable> </optional>;
+</synopsis>
+
+ In this case the cursor have to be assigned to opened cursor portal. The
+ <command>FOR</command> statement in this case doesn't open the cursor and
+ doesn't close the cursor when loop exists.
+ </para>
+
<para>
The variable <replaceable>recordvar</replaceable> is automatically
defined as type <type>record</type> 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()
