-- Cursor regression tests

--

BEGIN;

DECLARE foo1 SCROLL CURSOR FOR SELECT * FROM tenk1;

DECLARE foo1 SCROLL CURSOR FOR SELECT * FROM tenk1 ORDER BY unique2;

DECLARE foo2 SCROLL CURSOR FOR SELECT * FROM tenk2;

DECLARE foo3 SCROLL CURSOR FOR SELECT * FROM tenk1;

DECLARE foo3 SCROLL CURSOR FOR SELECT * FROM tenk1 ORDER BY unique2;

DECLARE foo4 SCROLL CURSOR FOR SELECT * FROM tenk2;

DECLARE foo5 SCROLL CURSOR FOR SELECT * FROM tenk1;

DECLARE foo5 SCROLL CURSOR FOR SELECT * FROM tenk1 ORDER BY unique2;

DECLARE foo6 SCROLL CURSOR FOR SELECT * FROM tenk2;

DECLARE foo7 SCROLL CURSOR FOR SELECT * FROM tenk1;

DECLARE foo7 SCROLL CURSOR FOR SELECT * FROM tenk1 ORDER BY unique2;

DECLARE foo8 SCROLL CURSOR FOR SELECT * FROM tenk2;

DECLARE foo9 SCROLL CURSOR FOR SELECT * FROM tenk1;

DECLARE foo9 SCROLL CURSOR FOR SELECT * FROM tenk1 ORDER BY unique2;

DECLARE foo10 SCROLL CURSOR FOR SELECT * FROM tenk2;

DECLARE foo11 SCROLL CURSOR FOR SELECT * FROM tenk1;

DECLARE foo11 SCROLL CURSOR FOR SELECT * FROM tenk1 ORDER BY unique2;

DECLARE foo12 SCROLL CURSOR FOR SELECT * FROM tenk2;

DECLARE foo13 SCROLL CURSOR FOR SELECT * FROM tenk1;

DECLARE foo13 SCROLL CURSOR FOR SELECT * FROM tenk1 ORDER BY unique2;

DECLARE foo14 SCROLL CURSOR FOR SELECT * FROM tenk2;

DECLARE foo15 SCROLL CURSOR FOR SELECT * FROM tenk1;

DECLARE foo15 SCROLL CURSOR FOR SELECT * FROM tenk1 ORDER BY unique2;

DECLARE foo16 SCROLL CURSOR FOR SELECT * FROM tenk2;

DECLARE foo17 SCROLL CURSOR FOR SELECT * FROM tenk1;

DECLARE foo17 SCROLL CURSOR FOR SELECT * FROM tenk1 ORDER BY unique2;

DECLARE foo18 SCROLL CURSOR FOR SELECT * FROM tenk2;

DECLARE foo19 SCROLL CURSOR FOR SELECT * FROM tenk1;

DECLARE foo19 SCROLL CURSOR FOR SELECT * FROM tenk1 ORDER BY unique2;

DECLARE foo20 SCROLL CURSOR FOR SELECT * FROM tenk2;

DECLARE foo21 SCROLL CURSOR FOR SELECT * FROM tenk1;

DECLARE foo21 SCROLL CURSOR FOR SELECT * FROM tenk1 ORDER BY unique2;

DECLARE foo22 SCROLL CURSOR FOR SELECT * FROM tenk2;

DECLARE foo23 SCROLL CURSOR FOR SELECT * FROM tenk1;

DECLARE foo23 SCROLL CURSOR FOR SELECT * FROM tenk1 ORDER BY unique2;

FETCH 1 in foo1;

unique1 | unique2 | two | four | ten | twenty | hundred | thousand | twothousand | fivethous | tenthous | odd | even | stringu1 | stringu2 | string4

---------+---------+-----+------+-----+--------+---------+----------+-------------+-----------+----------+-----+------+----------+----------+---------

-- 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

name |statement | is_holdable | is_binary | is_scrollable

-------+-----------------------------------------------------------------------+-------------+-----------+---------------

foo13 | DECLARE foo13 SCROLL CURSOR FOR SELECT * FROM tenk1 ORDER BY unique2; | f | f | t

foo15 | DECLARE foo15 SCROLL CURSOR FOR SELECT * FROM tenk1 ORDER BY unique2; | f | f | t

foo19 | DECLARE foo19 SCROLL CURSOR FOR SELECT * FROM tenk1 ORDER BY unique2; | f | f | t

foo17 | DECLARE foo17 SCROLL CURSOR FOR SELECT * FROM tenk1 ORDER BY unique2; | f | f | t

foo14 | DECLARE foo14 SCROLL CURSOR FOR SELECT * FROM tenk2;| f | f | t

foo21 | DECLARE foo21 SCROLL CURSOR FOR SELECT * FROM tenk1 ORDER BY unique2; | f | f | t

foo23 | DECLARE foo23 SCROLL CURSOR FOR SELECT * FROM tenk1 ORDER BY unique2; | 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;

-- NO SCROLL disallows backward fetching

--

BEGIN;

DECLARE foo24 NO SCROLL CURSOR FOR SELECT * FROM tenk1;

DECLARE foo24 NO SCROLL CURSOR FOR SELECT * FROM tenk1 ORDER BY unique2;

FETCH 1 FROM foo24;

unique1 | unique2 | two | four | ten | twenty | hundred | thousand | twothousand | fivethous | tenthous | odd | even | stringu1 | stringu2 | string4

---------+---------+-----+------+-----+--------+---------+----------+-------------+-----------+----------+-----+------+----------+----------+---------

-- ROLLBACK should close holdable cursors

--

BEGIN;

DECLARE foo26 CURSOR WITH HOLD FOR SELECT * FROM tenk1;

DECLARE foo26 CURSOR WITH HOLD FOR SELECT * FROM tenk1 ORDER BY unique2;

ROLLBACK;

-- should fail

FETCH FROM foo26;