diff options
Diffstat (limited to 'src/test')
| -rw-r--r-- | src/test/regress/expected/portals.out | 61 | ||||
| -rw-r--r-- | src/test/regress/expected/rules.out | 3 | ||||
| -rw-r--r-- | src/test/regress/sql/portals.sql | 25 |
3 files changed, 88 insertions, 1 deletions
diff --git a/src/test/regress/expected/portals.out b/src/test/regress/expected/portals.out index 3f0e0cdd265..cbff0b4245a 100644 --- a/src/test/regress/expected/portals.out +++ b/src/test/regress/expected/portals.out @@ -676,7 +676,30 @@ CLOSE foo10; CLOSE foo11; CLOSE foo12; -- leave some cursors open, to test that auto-close works. +-- record this in the system view as well (don't query the time field there +-- however) +SELECT name, statement, is_holdable, is_binary, is_scrollable FROM pg_cursors; + name | statement | is_holdable | is_binary | is_scrollable +-------+------------------------------------------------------+-------------+-----------+--------------- + foo13 | DECLARE foo13 SCROLL CURSOR FOR SELECT * FROM tenk1; | f | f | t + foo15 | DECLARE foo15 SCROLL CURSOR FOR SELECT * FROM tenk1; | f | f | t + foo19 | DECLARE foo19 SCROLL CURSOR FOR SELECT * FROM tenk1; | f | f | t + foo17 | DECLARE foo17 SCROLL CURSOR FOR SELECT * FROM tenk1; | f | f | t + foo14 | DECLARE foo14 SCROLL CURSOR FOR SELECT * FROM tenk2; | f | f | t + foo21 | DECLARE foo21 SCROLL CURSOR FOR SELECT * FROM tenk1; | f | f | t + foo23 | DECLARE foo23 SCROLL CURSOR FOR SELECT * FROM tenk1; | f | f | t + foo18 | DECLARE foo18 SCROLL CURSOR FOR SELECT * FROM tenk2; | f | f | t + foo20 | DECLARE foo20 SCROLL CURSOR FOR SELECT * FROM tenk2; | f | f | t + foo22 | DECLARE foo22 SCROLL CURSOR FOR SELECT * FROM tenk2; | f | f | t + foo16 | DECLARE foo16 SCROLL CURSOR FOR SELECT * FROM tenk2; | f | f | t +(11 rows) + END; +SELECT name, statement, is_holdable, is_binary, is_scrollable FROM pg_cursors; + name | statement | is_holdable | is_binary | is_scrollable +------+-----------+-------------+-----------+--------------- +(0 rows) + -- -- NO SCROLL disallows backward fetching -- @@ -695,6 +718,11 @@ END; -- -- Cursors outside transaction blocks -- +SELECT name, statement, is_holdable, is_binary, is_scrollable FROM pg_cursors; + name | statement | is_holdable | is_binary | is_scrollable +------+-----------+-------------+-----------+--------------- +(0 rows) + BEGIN; DECLARE foo25 SCROLL CURSOR WITH HOLD FOR SELECT * FROM tenk2; FETCH FROM foo25; @@ -728,6 +756,12 @@ FETCH ABSOLUTE -1 FROM foo25; 2968 | 9999 | 0 | 0 | 8 | 8 | 68 | 968 | 968 | 2968 | 2968 | 136 | 137 | EKAAAA | PUOAAA | VVVVxx (1 row) +SELECT name, statement, is_holdable, is_binary, is_scrollable FROM pg_cursors; + name | statement | is_holdable | is_binary | is_scrollable +-------+----------------------------------------------------------------+-------------+-----------+--------------- + foo25 | DECLARE foo25 SCROLL CURSOR WITH HOLD FOR SELECT * FROM tenk2; | t | f | t +(1 row) + CLOSE foo25; -- -- ROLLBACK should close holdable cursors @@ -808,3 +842,30 @@ fetch all from c2; drop function count_tt1_v(); drop function count_tt1_s(); +-- Create a cursor with the BINARY option and check the pg_cursors view +BEGIN; +SELECT name, statement, is_holdable, is_binary, is_scrollable FROM pg_cursors; + name | statement | is_holdable | is_binary | is_scrollable +------+----------------------------------------------------------------------+-------------+-----------+--------------- + c2 | declare c2 cursor with hold for select count_tt1_v(), count_tt1_s(); | t | f | f +(1 row) + +DECLARE bc BINARY CURSOR FOR SELECT * FROM tenk1; +SELECT name, statement, is_holdable, is_binary, is_scrollable FROM pg_cursors; + name | statement | is_holdable | is_binary | is_scrollable +------+----------------------------------------------------------------------+-------------+-----------+--------------- + c2 | declare c2 cursor with hold for select count_tt1_v(), count_tt1_s(); | t | f | f + bc | DECLARE bc BINARY CURSOR FOR SELECT * FROM tenk1; | f | t | t +(2 rows) + +ROLLBACK; +-- We should not see the portal that is created internally to +-- implement EXECUTE in pg_cursors +PREPARE cprep AS + SELECT name, statement, is_holdable, is_binary, is_scrollable FROM pg_cursors; +EXECUTE cprep; + name | statement | is_holdable | is_binary | is_scrollable +------+----------------------------------------------------------------------+-------------+-----------+--------------- + c2 | declare c2 cursor with hold for select count_tt1_v(), count_tt1_s(); | t | f | f +(1 row) + diff --git a/src/test/regress/expected/rules.out b/src/test/regress/expected/rules.out index 63c06ec75ac..90e38d013f3 100644 --- a/src/test/regress/expected/rules.out +++ b/src/test/regress/expected/rules.out @@ -1277,6 +1277,7 @@ SELECT viewname, definition FROM pg_views WHERE schemaname <> 'information_schem viewname | definition --------------------------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- iexit | SELECT ih.name, ih.thepath, interpt_pp(ih.thepath, r.thepath) AS exit FROM ihighway ih, ramp r WHERE (ih.thepath ## r.thepath); + pg_cursors | SELECT c.name, c."statement", c.is_holdable, c.is_binary, c.is_scrollable, c.creation_time FROM pg_cursor() c(name text, "statement" text, is_holdable boolean, is_binary boolean, is_scrollable boolean, creation_time timestamp with time zone); pg_group | SELECT pg_authid.rolname AS groname, pg_authid.oid AS grosysid, ARRAY(SELECT pg_auth_members.member FROM pg_auth_members WHERE (pg_auth_members.roleid = pg_authid.oid)) AS grolist FROM pg_authid WHERE (NOT pg_authid.rolcanlogin); pg_indexes | SELECT n.nspname AS schemaname, c.relname AS tablename, i.relname AS indexname, t.spcname AS "tablespace", pg_get_indexdef(i.oid) AS indexdef FROM ((((pg_index x JOIN pg_class c ON ((c.oid = x.indrelid))) JOIN pg_class i ON ((i.oid = x.indexrelid))) LEFT JOIN pg_namespace n ON ((n.oid = c.relnamespace))) LEFT JOIN pg_tablespace t ON ((t.oid = i.reltablespace))) WHERE ((c.relkind = 'r'::"char") AND (i.relkind = 'i'::"char")); pg_locks | SELECT l.locktype, l."database", l.relation, l.page, l.tuple, l.transactionid, l.classid, l.objid, l.objsubid, l."transaction", l.pid, l."mode", l."granted" FROM pg_lock_status() l(locktype text, "database" oid, relation oid, page integer, tuple smallint, transactionid xid, classid oid, objid oid, objsubid smallint, "transaction" xid, pid integer, "mode" text, "granted" boolean); @@ -1321,7 +1322,7 @@ SELECT viewname, definition FROM pg_views WHERE schemaname <> 'information_schem shoelace_obsolete | SELECT shoelace.sl_name, shoelace.sl_avail, shoelace.sl_color, shoelace.sl_len, shoelace.sl_unit, shoelace.sl_len_cm FROM shoelace WHERE (NOT (EXISTS (SELECT shoe.shoename FROM shoe WHERE (shoe.slcolor = shoelace.sl_color)))); street | SELECT r.name, r.thepath, c.cname FROM ONLY road r, real_city c WHERE (c.outline ## r.thepath); toyemp | SELECT emp.name, emp.age, emp."location", (12 * emp.salary) AS annualsal FROM emp; -(45 rows) +(46 rows) SELECT tablename, rulename, definition FROM pg_rules ORDER BY tablename, rulename; diff --git a/src/test/regress/sql/portals.sql b/src/test/regress/sql/portals.sql index da4e3b0e3ae..c0307874e80 100644 --- a/src/test/regress/sql/portals.sql +++ b/src/test/regress/sql/portals.sql @@ -168,8 +168,14 @@ CLOSE foo12; -- leave some cursors open, to test that auto-close works. +-- record this in the system view as well (don't query the time field there +-- however) +SELECT name, statement, is_holdable, is_binary, is_scrollable FROM pg_cursors; + END; +SELECT name, statement, is_holdable, is_binary, is_scrollable FROM pg_cursors; + -- -- NO SCROLL disallows backward fetching -- @@ -188,6 +194,9 @@ END; -- Cursors outside transaction blocks -- + +SELECT name, statement, is_holdable, is_binary, is_scrollable FROM pg_cursors; + BEGIN; DECLARE foo25 SCROLL CURSOR WITH HOLD FOR SELECT * FROM tenk2; @@ -204,6 +213,8 @@ FETCH BACKWARD FROM foo25; FETCH ABSOLUTE -1 FROM foo25; +SELECT name, statement, is_holdable, is_binary, is_scrollable FROM pg_cursors; + CLOSE foo25; -- @@ -278,3 +289,17 @@ fetch all from c2; drop function count_tt1_v(); drop function count_tt1_s(); + + +-- Create a cursor with the BINARY option and check the pg_cursors view +BEGIN; +SELECT name, statement, is_holdable, is_binary, is_scrollable FROM pg_cursors; +DECLARE bc BINARY CURSOR FOR SELECT * FROM tenk1; +SELECT name, statement, is_holdable, is_binary, is_scrollable FROM pg_cursors; +ROLLBACK; + +-- We should not see the portal that is created internally to +-- implement EXECUTE in pg_cursors +PREPARE cprep AS + SELECT name, statement, is_holdable, is_binary, is_scrollable FROM pg_cursors; +EXECUTE cprep; |