This patch adds some minimal regression tests for refcursors in PL/PgSQL
(if someone wants to augment these with more, go right ahead). Barring
any objections, I intend to apply this to HEAD before end of day.

-Neil

Index: src/test/regress/expected/plpgsql.out
===================================================================
RCS file: /var/lib/cvs/pgsql/src/test/regress/expected/plpgsql.out,v
retrieving revision 1.24
diff -c -r1.24 plpgsql.out
*** src/test/regress/expected/plpgsql.out	21 Dec 2004 18:33:36 -0000	1.24
--- src/test/regress/expected/plpgsql.out	18 Jan 2005 22:52:58 -0000
***************
*** 2103,2105 ****
--- 2103,2178 ----
  
  drop function sp_add_user(text);
  drop function sp_id_user(text);
+ --
+ -- tests for refcursors
+ --
+ create table rc_test (a int, b int);
+ copy rc_test from stdin;
+ create function return_refcursor(rc refcursor) returns refcursor as $$
+ begin
+     open rc for select a from rc_test;
+     return rc;
+ end
+ $$ language 'plpgsql';
+ create function refcursor_test1(refcursor) returns refcursor as $$
+ declare
+     c1 refcursor;
+     c2 refcursor;
+ begin
+     perform return_refcursor($1);
+     return $1;
+ end
+ $$ language 'plpgsql';
+ begin;
+ select refcursor_test1('test1');
+  refcursor_test1 
+ -----------------
+  test1
+ (1 row)
+ 
+ fetch next from test1;
+  a 
+ ---
+  5
+ (1 row)
+ 
+ select refcursor_test1('test2');
+  refcursor_test1 
+ -----------------
+  test2
+ (1 row)
+ 
+ fetch all from test2;
+   a  
+ -----
+    5
+   50
+  500
+ (3 rows)
+ 
+ commit;
+ -- should fail
+ fetch next from test1;
+ ERROR:  cursor "test1" does not exist
+ create function refcursor_test2(int) returns boolean as $$
+ declare
+     c3 cursor (param integer) for select * from rc_test where a > param;
+     nonsense record;
+ begin
+     open c3($1);
+     fetch c3 into nonsense;
+     close c3;
+     if found then
+         return true;
+     else
+         return false;
+     end if;
+ end
+ $$ language 'plpgsql';
+ select refcursor_test2(20000) as "Should be false",
+        refcursor_test2(20) as "Should be true";
+  Should be false | Should be true 
+ -----------------+----------------
+  f               | t
+ (1 row)
+ 
Index: src/test/regress/sql/plpgsql.sql
===================================================================
RCS file: /var/lib/cvs/pgsql/src/test/regress/sql/plpgsql.sql,v
retrieving revision 1.19
diff -c -r1.19 plpgsql.sql
*** src/test/regress/sql/plpgsql.sql	21 Dec 2004 18:33:36 -0000	1.19
--- src/test/regress/sql/plpgsql.sql	18 Jan 2005 22:50:23 -0000
***************
*** 1807,1809 ****
--- 1807,1868 ----
  
  drop function sp_add_user(text);
  drop function sp_id_user(text);
+ 
+ --
+ -- tests for refcursors
+ --
+ create table rc_test (a int, b int);
+ copy rc_test from stdin;
+ 5	10
+ 50	100
+ 500	1000
+ \.
+ 
+ create function return_refcursor(rc refcursor) returns refcursor as $$
+ begin
+     open rc for select a from rc_test;
+     return rc;
+ end
+ $$ language 'plpgsql';
+ 
+ create function refcursor_test1(refcursor) returns refcursor as $$
+ declare
+     c1 refcursor;
+     c2 refcursor;
+ begin
+     perform return_refcursor($1);
+     return $1;
+ end
+ $$ language 'plpgsql';
+ 
+ begin;
+ 
+ select refcursor_test1('test1');
+ fetch next from test1;
+ 
+ select refcursor_test1('test2');
+ fetch all from test2;
+ 
+ commit;
+ 
+ -- should fail
+ fetch next from test1;
+ 
+ create function refcursor_test2(int) returns boolean as $$
+ declare
+     c3 cursor (param integer) for select * from rc_test where a > param;
+     nonsense record;
+ begin
+     open c3($1);
+     fetch c3 into nonsense;
+     close c3;
+     if found then
+         return true;
+     else
+         return false;
+     end if;
+ end
+ $$ language 'plpgsql';
+ 
+ select refcursor_test2(20000) as "Should be false",
+        refcursor_test2(20) as "Should be true";
---------------------------(end of broadcast)---------------------------
TIP 3: if posting/reading through Usenet, please send an appropriate
      subscribe-nomail command to [EMAIL PROTECTED] so that your
      message can get through to the mailing list cleanly

Reply via email to