(5 rows)

/*

* Test CTE query(DELETE)- by @parihaaraka (add varno to WalkerContext)

* Test CTE query - by @parihaaraka (add varno to WalkerContext)

*/

CREATE TABLE test.cte_del_xacts (id BIGSERIAL PRIMARY KEY, pdate DATE NOT NULL);

INSERT INTO test.cte_del_xacts (pdate) SELECT gen_date FROM generate_series('2016-01-01'::date, '2016-04-9'::date, '1 day') AS gen_date;

state_code SMALLINT NOT NULL DEFAULT 8,

regtime TIMESTAMP WITHOUT TIME ZONE NOT NULL

);

INSERT INTO test.cte_del_xacts_specdata VALUES(1, 1, 1, current_timestamp); /* for subquery test */

/* create 2 partitions */

SELECT pathman.create_range_partitions('test.cte_del_xacts'::regclass, 'pdate', '2016-01-01'::date, '50 days'::interval);

NOTICE: sequence "cte_del_xacts_seq" does not exist, skipping

-> Index Scan using cte_del_xacts_1_pkey on cte_del_xacts_1 t

(9 rows)

/* create stub pl/PgSQL function */

CREATE OR REPLACE FUNCTION test.cte_del_xacts_stab(name TEXT)

RETURNS smallint AS

$$

begin

return 2::smallint;

end

$$

LANGUAGE plpgsql STABLE;

/* test subquery planning */

WITH tmp AS (

SELECT tid FROM test.cte_del_xacts_specdata

WHERE state_code != test.cte_del_xacts_stab('test'))

SELECT * FROM test.cte_del_xacts t JOIN tmp ON t.id = tmp.tid;

id | pdate | tid

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

1 | 01-01-2016 | 1

(1 row)

/* test subquery planning (one more time) */

WITH tmp AS (

SELECT tid FROM test.cte_del_xacts_specdata

WHERE state_code != test.cte_del_xacts_stab('test'))

SELECT * FROM test.cte_del_xacts t JOIN tmp ON t.id = tmp.tid;

id | pdate | tid

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

1 | 01-01-2016 | 1

(1 row)

DROP FUNCTION test.cte_del_xacts_stab(TEXT);

DROP TABLE test.cte_del_xacts, test.cte_del_xacts_specdata CASCADE;

NOTICE: drop cascades to table test.cte_del_xacts_1

/*

CREATETABLEtest.cte_del_xacts (idBIGSERIALPRIMARY KEY, pdateDATENOT NULL);

INSERT INTOtest.cte_del_xacts (pdate)SELECT gen_dateFROM generate_series('2016-01-01'::date,'2016-04-9'::date,'1 day')AS gen_date;

state_codeSMALLINTNOT NULL DEFAULT8,

regtimeTIMESTAMP WITHOUT TIME ZONENOT NULL

);

INSERT INTOtest.cte_del_xacts_specdataVALUES(1,1,1,current_timestamp);/* for subquery test*/

SELECTpathman.create_range_partitions('test.cte_del_xacts'::regclass,'pdate','2016-01-01'::date,'50 days'::interval);

DELETEFROMtest.cte_del_xacts t USING tmp

WHEREt.id=tmp.tidANDt.pdate=tmp.pdateANDtmp.test_mode>0;

CREATE OR REPLACEFUNCTIONtest.cte_del_xacts_stab(nameTEXT)

RETURNSsmallintAS

$$

return2::smallint;

end

$$

LANGUAGE plpgsql STABLE;

WITH tmpAS (

SELECT tidFROMtest.cte_del_xacts_specdata

WHERE state_code!=test.cte_del_xacts_stab('test'))

SELECT*FROMtest.cte_del_xacts tJOIN tmpONt.id=tmp.tid;

WITH tmpAS (

SELECT tidFROMtest.cte_del_xacts_specdata

WHERE state_code!=test.cte_del_xacts_stab('test'))

SELECT*FROMtest.cte_del_xacts tJOIN tmpONt.id=tmp.tid;

DROPFUNCTIONtest.cte_del_xacts_stab(TEXT);

DROPTABLEtest.cte_del_xacts,test.cte_del_xacts_specdata CASCADE;