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
