Movatterモバイル変換

[0]ホーム
URL:

Skip to content

Navigation Menu

Sign in
Appearance settings

Search code, repositories, users, issues, pull requests...

Provide feedback

We read every piece of feedback, and take your input very seriously.

Saved searches

Use saved searches to filter your results more quickly

 Sign up
Appearance settings

Commitd600c1d

Browse files
author
Neil Conway
committed
Add some basic regression tests for refcursors in PL/PgSQL.
1 parent3fc86c0 commitd600c1d

File tree

2 files changed

+126
-0
lines changed

2 files changed

+126
-0
lines changed

‎src/test/regress/expected/plpgsql.out

Lines changed: 70 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -2103,3 +2103,73 @@ select sp_add_user('user3');
21032103

21042104
drop function sp_add_user(text);
21052105
drop function sp_id_user(text);
2106+
--
2107+
-- tests for refcursors
2108+
--
2109+
create table rc_test (a int, b int);
2110+
copy rc_test from stdin;
2111+
create function return_refcursor(rc refcursor) returns refcursor as $$
2112+
begin
2113+
open rc for select a from rc_test;
2114+
return rc;
2115+
end
2116+
$$ language 'plpgsql';
2117+
create function refcursor_test1(refcursor) returns refcursor as $$
2118+
begin
2119+
perform return_refcursor($1);
2120+
return $1;
2121+
end
2122+
$$ language 'plpgsql';
2123+
begin;
2124+
select refcursor_test1('test1');
2125+
refcursor_test1
2126+
-----------------
2127+
test1
2128+
(1 row)
2129+
2130+
fetch next from test1;
2131+
a
2132+
---
2133+
5
2134+
(1 row)
2135+
2136+
select refcursor_test1('test2');
2137+
refcursor_test1
2138+
-----------------
2139+
test2
2140+
(1 row)
2141+
2142+
fetch all from test2;
2143+
a
2144+
-----
2145+
5
2146+
50
2147+
500
2148+
(3 rows)
2149+
2150+
commit;
2151+
-- should fail
2152+
fetch next from test1;
2153+
ERROR: cursor "test1" does not exist
2154+
create function refcursor_test2(int) returns boolean as $$
2155+
declare
2156+
c1 cursor (param integer) for select * from rc_test where a > param;
2157+
nonsense record;
2158+
begin
2159+
open c1($1);
2160+
fetch c1 into nonsense;
2161+
close c1;
2162+
if found then
2163+
return true;
2164+
else
2165+
return false;
2166+
end if;
2167+
end
2168+
$$ language 'plpgsql';
2169+
select refcursor_test2(20000) as "Should be false",
2170+
refcursor_test2(20) as "Should be true";
2171+
Should be false | Should be true
2172+
-----------------+----------------
2173+
f | t
2174+
(1 row)
2175+

‎src/test/regress/sql/plpgsql.sql

Lines changed: 56 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -1807,3 +1807,59 @@ select sp_add_user('user3');
18071807

18081808
dropfunction sp_add_user(text);
18091809
dropfunction sp_id_user(text);
1810+
1811+
--
1812+
-- tests for refcursors
1813+
--
1814+
createtablerc_test (aint, bint);
1815+
copy rc_testfrom stdin;
1816+
510
1817+
50100
1818+
5001000
1819+
\.
1820+
1821+
createfunctionreturn_refcursor(rc refcursor) returns refcursoras $$
1822+
begin
1823+
open rc forselect afrom rc_test;
1824+
return rc;
1825+
end
1826+
$$ language'plpgsql';
1827+
1828+
createfunctionrefcursor_test1(refcursor) returns refcursoras $$
1829+
begin
1830+
perform return_refcursor($1);
1831+
return $1;
1832+
end
1833+
$$ language'plpgsql';
1834+
1835+
begin;
1836+
1837+
select refcursor_test1('test1');
1838+
fetch nextfrom test1;
1839+
1840+
select refcursor_test1('test2');
1841+
fetch allfrom test2;
1842+
1843+
commit;
1844+
1845+
-- should fail
1846+
fetch nextfrom test1;
1847+
1848+
createfunctionrefcursor_test2(int) returnsbooleanas $$
1849+
declare
1850+
c1 cursor (paraminteger) forselect*from rc_testwhere a> param;
1851+
nonsense record;
1852+
begin
1853+
open c1($1);
1854+
fetch c1 into nonsense;
1855+
close c1;
1856+
if found then
1857+
return true;
1858+
else
1859+
return false;
1860+
end if;
1861+
end
1862+
$$ language'plpgsql';
1863+
1864+
select refcursor_test2(20000)as"Should be false",
1865+
refcursor_test2(20)as"Should be true";

0 commit comments

Comments
 (0)
[8]ページ先頭
©2009-2025 Movatter.jp