Make plpgsql support FOR over a query specified by a cursor declaration,

for improved compatibility with Oracle.

Pavel Stehule, with some fixes by me.

2 files changed, 190 insertions, 0 deletions

diff --git a/src/test/regress/expected/plpgsql.out b/src/test/regress/expected/plpgsql.out
index bcf974483e..018c8c2b50 100644
--- a/src/test/regress/expected/plpgsql.out
+++ b/src/test/regress/expected/plpgsql.out
@@ -3151,3 +3151,119 @@ NOTICE:  6
         26
 (1 row)
 
+-- test FOR-over-cursor
+create or replace function forc01() returns void as $$
+declare
+  c cursor(r1 integer, r2 integer)
+       for select * from generate_series(r1,r2) i;
+  c2 cursor
+       for select * from generate_series(41,43) i;
+begin
+  for r in c(5,7) loop
+    raise notice '% from %', r.i, c;
+  end loop;
+  -- again, to test if cursor was closed properly
+  for r in c(9,10) loop
+    raise notice '% from %', r.i, c;
+  end loop;
+  -- and test a parameterless cursor
+  for r in c2 loop
+    raise notice '% from %', r.i, c2;
+  end loop;
+  -- and try it with a hand-assigned name
+  raise notice 'after loop, c2 = %', c2;
+  c2 := 'special_name';
+  for r in c2 loop
+    raise notice '% from %', r.i, c2;
+  end loop;
+  raise notice 'after loop, c2 = %', c2;
+  -- and try it with a generated name
+  -- (which we can't show in the output because it's variable)
+  c2 := null;
+  for r in c2 loop
+    raise notice '%', r.i;
+  end loop;
+  raise notice 'after loop, c2 = %', c2;
+  return;
+end;
+$$ language plpgsql;
+select forc01();
+NOTICE:  5 from c
+NOTICE:  6 from c
+NOTICE:  7 from c
+NOTICE:  9 from c
+NOTICE:  10 from c
+NOTICE:  41 from c2
+NOTICE:  42 from c2
+NOTICE:  43 from c2
+NOTICE:  after loop, c2 = c2
+NOTICE:  41 from special_name
+NOTICE:  42 from special_name
+NOTICE:  43 from special_name
+NOTICE:  after loop, c2 = special_name
+NOTICE:  41
+NOTICE:  42
+NOTICE:  43
+NOTICE:  after loop, c2 = <NULL>
+ forc01 
+--------
+ 
+(1 row)
+
+-- try updating the cursor's current row
+create temp table forc_test as
+  select n as i, n as j from generate_series(1,10) n;
+create or replace function forc01() returns void as $$
+declare
+  c cursor for select * from forc_test;
+begin
+  for r in c loop
+    raise notice '%, %', r.i, r.j;
+    update forc_test set i = i * 100, j = r.j * 2 where current of c;
+  end loop;
+end;
+$$ language plpgsql;
+select forc01();
+NOTICE:  1, 1
+NOTICE:  2, 2
+NOTICE:  3, 3
+NOTICE:  4, 4
+NOTICE:  5, 5
+NOTICE:  6, 6
+NOTICE:  7, 7
+NOTICE:  8, 8
+NOTICE:  9, 9
+NOTICE:  10, 10
+ forc01 
+--------
+ 
+(1 row)
+
+select * from forc_test;
+  i   | j  
+------+----
+  100 |  2
+  200 |  4
+  300 |  6
+  400 |  8
+  500 | 10
+  600 | 12
+  700 | 14
+  800 | 16
+  900 | 18
+ 1000 | 20
+(10 rows)
+
+drop function forc01();
+-- fail because cursor has no query bound to it
+create or replace function forc_bad() returns void as $$
+declare
+  c refcursor;
+begin
+  for r in c loop
+    raise notice '%', r.i;
+  end loop;
+end;
+$$ language plpgsql;
+ERROR:  cursor FOR loop must use a bound cursor variable
+CONTEXT:  compile of PL/pgSQL function "forc_bad" near line 4
diff --git a/src/test/regress/sql/plpgsql.sql b/src/test/regress/sql/plpgsql.sql
index 2a93ffc611..066ccbeba6 100644
--- a/src/test/regress/sql/plpgsql.sql
+++ b/src/test/regress/sql/plpgsql.sql
@@ -2595,3 +2595,77 @@ end
 $$ language plpgsql;
 
 select exc_using(5, 'foobar');
+
+-- test FOR-over-cursor
+
+create or replace function forc01() returns void as $$
+declare
+  c cursor(r1 integer, r2 integer)
+       for select * from generate_series(r1,r2) i;
+  c2 cursor
+       for select * from generate_series(41,43) i;
+begin
+  for r in c(5,7) loop
+    raise notice '% from %', r.i, c;
+  end loop;
+  -- again, to test if cursor was closed properly
+  for r in c(9,10) loop
+    raise notice '% from %', r.i, c;
+  end loop;
+  -- and test a parameterless cursor
+  for r in c2 loop
+    raise notice '% from %', r.i, c2;
+  end loop;
+  -- and try it with a hand-assigned name
+  raise notice 'after loop, c2 = %', c2;
+  c2 := 'special_name';
+  for r in c2 loop
+    raise notice '% from %', r.i, c2;
+  end loop;
+  raise notice 'after loop, c2 = %', c2;
+  -- and try it with a generated name
+  -- (which we can't show in the output because it's variable)
+  c2 := null;
+  for r in c2 loop
+    raise notice '%', r.i;
+  end loop;
+  raise notice 'after loop, c2 = %', c2;
+  return;
+end;
+$$ language plpgsql;
+
+select forc01();
+
+-- try updating the cursor's current row
+
+create temp table forc_test as
+  select n as i, n as j from generate_series(1,10) n;
+
+create or replace function forc01() returns void as $$
+declare
+  c cursor for select * from forc_test;
+begin
+  for r in c loop
+    raise notice '%, %', r.i, r.j;
+    update forc_test set i = i * 100, j = r.j * 2 where current of c;
+  end loop;
+end;
+$$ language plpgsql;
+
+select forc01();
+
+select * from forc_test;
+
+drop function forc01();
+
+-- fail because cursor has no query bound to it
+
+create or replace function forc_bad() returns void as $$
+declare
+  c refcursor;
+begin
+  for r in c loop
+    raise notice '%', r.i;
+  end loop;
+end;
+$$ language plpgsql;