|
|
 | | From: | Neil Conway | | Subject: | test: pl/pgsql refcursors | | Date: | Wed, 19 Jan 2005 09:55:17 +1100 |
|
|
 | --=-WYm1pCvhVsgiMGMf0fnO
Content-Type: text/plain
Content-Transfer-Encoding: 7bit
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
--=-WYm1pCvhVsgiMGMf0fnO
Content-Disposition: attachment; filename=refcursor_test-1.patch
Content-Type: text/x-patch; name=refcursor_test-1.patch; charset=ISO-8859-1
Content-Transfer-Encoding: 7bit
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";
--=-WYm1pCvhVsgiMGMf0fnO
Content-Type: text/plain
Content-Disposition: inline
Content-Transfer-Encoding: 8bit
MIME-Version: 1.0
---------------------------(end of broadcast)---------------------------
TIP 3: if posting/reading through Usenet, please send an appropriate
subscribe-nomail command to majordomo@postgresql.org so that your
message can get through to the mailing list cleanly
--=-WYm1pCvhVsgiMGMf0fnO--
|
|
| | From: | Neil Conway | | Subject: | Re: test: pl/pgsql refcursors | | Date: | Wed, 19 Jan 2005 15:29:44 +1100 |
|
|
| On Wed, 2005-01-19 at 09:55 +1100, Neil Conway wrote:
> This patch adds some minimal regression tests for refcursors in PL/PgSQL
> (if someone wants to augment these with more, go right ahead).
Applied.
-Neil
---------------------------(end of broadcast)---------------------------
TIP 3: if posting/reading through Usenet, please send an appropriate
subscribe-nomail command to majordomo@postgresql.org so that your
message can get through to the mailing list cleanly
|
|
|
