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

2 files changed, 81 insertions, 4 deletions

diff --git a/src/test/regress/expected/plpgsql.out b/src/test/regress/expected/plpgsql.out
index ca4c9dc2331..a362fb53705 100644
--- a/src/test/regress/expected/plpgsql.out
+++ b/src/test/regress/expected/plpgsql.out
@@ -3027,6 +3027,28 @@ select * from sc_test();
 
 create or replace function sc_test() returns setof integer as $$
 declare
+  c refcursor;
+  x integer;
+begin
+  open c scroll for execute 'select f1 from int4_tbl';
+  fetch last from c into x;
+  while found loop
+    return next x;
+    move backward 2 from c;
+    fetch relative -1 from c into x;
+  end loop;
+  close c;
+end;
+$$ language plpgsql;
+select * from sc_test();
+   sc_test   
+-------------
+ -2147483647
+      123456
+(2 rows)
+
+create or replace function sc_test() returns setof integer as $$
+declare
   c cursor for select * from generate_series(1, 10);
   x integer;
 begin
@@ -3052,6 +3074,26 @@ select * from sc_test();
        9
 (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;
+  move forward all in c;
+  fetch backward from c into x;
+  if found then
+    return next x;
+  end if;
+  close c;
+end;
+$$ language plpgsql;
+select * from sc_test();
+ sc_test 
+---------
+      10
+(1 row)
+
 drop function sc_test();
 -- test qualified variable names
 create function pl_qual_names (param1 int) returns void as $$
@@ -3864,7 +3906,7 @@ drop function strtest();
 -- Test anonymous code blocks.
 DO $$
 DECLARE r record;
-BEGIN 
+BEGIN
     FOR r IN SELECT rtrim(roomno) AS roomno, comment FROM Room ORDER BY roomno
     LOOP
         RAISE NOTICE '%, %', r.roomno, r.comment;
@@ -3887,7 +3929,7 @@ LINE 1: DO LANGUAGE plpgsql $$begin return 1; end$$;
                                            ^
 DO LANGUAGE plpgsql $$
 DECLARE r record;
-BEGIN 
+BEGIN
     FOR r IN SELECT rtrim(roomno) AS roomno, foo FROM Room ORDER BY roomno
     LOOP
         RAISE NOTICE '%, %', r.roomno, r.comment;
diff --git a/src/test/regress/sql/plpgsql.sql b/src/test/regress/sql/plpgsql.sql
index 96f89144b77..80de8eb72f8 100644
--- a/src/test/regress/sql/plpgsql.sql
+++ b/src/test/regress/sql/plpgsql.sql
@@ -2513,6 +2513,24 @@ select * from sc_test();
 
 create or replace function sc_test() returns setof integer as $$
 declare
+  c refcursor;
+  x integer;
+begin
+  open c scroll for execute 'select f1 from int4_tbl';
+  fetch last from c into x;
+  while found loop
+    return next x;
+    move backward 2 from c;
+    fetch relative -1 from c into x;
+  end loop;
+  close c;
+end;
+$$ 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
@@ -2533,6 +2551,23 @@ $$ 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;
+  move forward all in c;
+  fetch backward from c into x;
+  if found then
+    return next x;
+  end if;
+  close c;
+end;
+$$ language plpgsql;
+
+select * from sc_test();
+
 drop function sc_test();
 
 -- test qualified variable names
@@ -3084,7 +3119,7 @@ drop function strtest();
 
 DO $$
 DECLARE r record;
-BEGIN 
+BEGIN
     FOR r IN SELECT rtrim(roomno) AS roomno, comment FROM Room ORDER BY roomno
     LOOP
         RAISE NOTICE '%, %', r.roomno, r.comment;
@@ -3096,7 +3131,7 @@ DO LANGUAGE plpgsql $$begin return 1; end$$;
 
 DO LANGUAGE plpgsql $$
 DECLARE r record;
-BEGIN 
+BEGIN
     FOR r IN SELECT rtrim(roomno) AS roomno, foo FROM Room ORDER BY roomno
     LOOP
         RAISE NOTICE '%, %', r.roomno, r.comment;