src/test/isolation2/input/parallel_retrieve_cursor/syntax.source

-- @Description Tests syntax for the PARALLEL RETRIEVE CURSOR statement
--
DROP TABLE IF EXISTS t1;
CREATE TABLE t1 (a INT) DISTRIBUTED by (a);
insert into t1 select generate_series(1,100);

-- Test1: PARALLEL RETRIEVE CURSOR with other options (WITH HOLD/SCROLL) is not supported
BEGIN;
DECLARE c1 PARALLEL RETRIEVE CURSOR WITHOUT HOLD FOR SELECT * FROM t1;
DECLARE c2 PARALLEL RETRIEVE CURSOR WITH HOLD FOR SELECT * FROM t1;
ROLLBACK;

BEGIN;
DECLARE c1 NO SCROLL PARALLEL RETRIEVE CURSOR FOR SELECT * FROM t1;
DECLARE c2 SCROLL PARALLEL RETRIEVE CURSOR FOR SELECT * FROM t1;
ROLLBACK;

-- Test2: PARALLEL RETRIEVE CURSOR can not support FETCH/MOVE statement
BEGIN;
DECLARE c1 PARALLEL RETRIEVE CURSOR FOR SELECT * FROM t1;
FETCH ALL FROM c1;
ROLLBACK;

BEGIN;
DECLARE c1 PARALLEL RETRIEVE CURSOR FOR SELECT * FROM t1;
MOVE 10 FROM c1;
ROLLBACK;

-- Test3: non-PARALLEL RETRIEVE CURSOR can not support the PARALLEL RETRIEVE CURSOR related UDF
BEGIN;
DECLARE c1 CURSOR FOR SELECT * FROM t1;
SELECT * FROM gp_wait_parallel_retrieve_cursor('c1', 0);
ROLLBACK;
BEGIN;
DECLARE c1 CURSOR FOR SELECT * FROM t1;
SELECT * FROM gp_wait_parallel_retrieve_cursor('c1', -1);
ROLLBACK;

-- Test4: PARALLEL RETRIEVE CURSOR can not support in SPI
BEGIN;
DECLARE c1 CURSOR WITHOUT HOLD FOR SELECT * FROM t1;
DO $$ DECLARE i int4; c2 refcursor = 'c1'; BEGIN FETCH FROM c2 INTO i; RAISE NOTICE '%', i; END; $$;
ROLLBACK;
BEGIN;
DECLARE c3 PARALLEL RETRIEVE CURSOR WITHOUT HOLD FOR SELECT * FROM t1;
DO $$ DECLARE i int4; c4 refcursor = 'c3'; BEGIN FETCH FROM c4 INTO i; RAISE NOTICE '%', i; END; $$;
ROLLBACK;