5
(5 rows)
+-- recursive view
+CREATE RECURSIVE VIEW nums (n) AS
+ VALUES (1)
+UNION ALL
+ SELECT n+1 FROM nums WHERE n < 5;
+SELECT * FROM nums;
+ n
+---
+ 1
+ 2
+ 3
+ 4
+ 5
+(5 rows)
+
+CREATE OR REPLACE RECURSIVE VIEW nums (n) AS
+ VALUES (1)
+UNION ALL
+ SELECT n+1 FROM nums WHERE n < 6;
+SELECT * FROM nums;
+ n
+---
+ 1
+ 2
+ 3
+ 4
+ 5
+ 6
+(6 rows)
+
+-- This is an infinite loop with UNION ALL, but not with UNION
+WITH RECURSIVE t(n) AS (
+ SELECT 1
+UNION
+ SELECT 10-n FROM t)
+SELECT * FROM t;
+ n
+---
+ 1
+ 9
+(2 rows)
+
-- This'd be an infinite loop, but outside query reads only as much as needed
WITH RECURSIVE t(n) AS (
VALUES (1)
10
(10 rows)
+-- UNION case should have same property
+WITH RECURSIVE t(n) AS (
+ SELECT 1
+UNION
+ SELECT n+1 FROM t)
+SELECT * FROM t LIMIT 10;
+ n
+----
+ 1
+ 2
+ 3
+ 4
+ 5
+ 6
+ 7
+ 8
+ 9
+ 10
+(10 rows)
+
+-- Test behavior with an unknown-type literal in the WITH
+WITH q AS (SELECT 'foo' AS x)
+SELECT x, x IS OF (unknown) as is_unknown FROM q;
+ x | is_unknown
+-----+------------
+ foo | t
+(1 row)
+
+WITH RECURSIVE t(n) AS (
+ SELECT 'foo'
+UNION ALL
+ SELECT n || ' bar' FROM t WHERE length(n) < 20
+)
+SELECT n, n IS OF (text) as is_text FROM t;
+ n | is_text
+-------------------------+---------
+ foo | t
+ foo bar | t
+ foo bar bar | t
+ foo bar bar bar | t
+ foo bar bar bar bar | t
+ foo bar bar bar bar bar | t
+(6 rows)
+
--
-- Some examples with a tree
--
parent_department INTEGER REFERENCES department, -- upper department ID
name TEXT -- department name
);
-NOTICE: CREATE TABLE / PRIMARY KEY will create implicit index "department_pkey" for table "department"
INSERT INTO department VALUES (0, NULL, 'ROOT');
INSERT INTO department VALUES (1, 0, 'A');
INSERT INTO department VALUES (2, 1, 'B');
-- Check reverse listing
SELECT pg_get_viewdef('vsubdepartment'::regclass);
- pg_get_viewdef
----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
- WITH RECURSIVE subdepartment AS (SELECT department.id, department.parent_department, department.name FROM department WHERE (department.name = 'A'::text) UNION ALL SELECT d.id, d.parent_department, d.name FROM department d, subdepartment sd WHERE (d.parent_department = sd.id)) SELECT subdepartment.id, subdepartment.parent_department, subdepartment.name FROM subdepartment;
+ pg_get_viewdef
+-----------------------------------------------
+ WITH RECURSIVE subdepartment AS ( +
+ SELECT department.id, +
+ department.parent_department, +
+ department.name +
+ FROM department +
+ WHERE (department.name = 'A'::text)+
+ UNION ALL +
+ SELECT d.id, +
+ d.parent_department, +
+ d.name +
+ FROM department d, +
+ subdepartment sd +
+ WHERE (d.parent_department = sd.id)+
+ ) +
+ SELECT subdepartment.id, +
+ subdepartment.parent_department, +
+ subdepartment.name +
+ FROM subdepartment;
(1 row)
SELECT pg_get_viewdef('vsubdepartment'::regclass, true);
- pg_get_viewdef
---------------------------------------------------------------------------------------
- WITH RECURSIVE subdepartment AS (
- SELECT department.id, department.parent_department, department.name
- FROM department
- WHERE department.name = 'A'::text
- UNION ALL
- SELECT d.id, d.parent_department, d.name
- FROM department d, subdepartment sd
- WHERE d.parent_department = sd.id
- )
- SELECT subdepartment.id, subdepartment.parent_department, subdepartment.name
+ pg_get_viewdef
+---------------------------------------------
+ WITH RECURSIVE subdepartment AS ( +
+ SELECT department.id, +
+ department.parent_department, +
+ department.name +
+ FROM department +
+ WHERE department.name = 'A'::text+
+ UNION ALL +
+ SELECT d.id, +
+ d.parent_department, +
+ d.name +
+ FROM department d, +
+ subdepartment sd +
+ WHERE d.parent_department = sd.id+
+ ) +
+ SELECT subdepartment.id, +
+ subdepartment.parent_department, +
+ subdepartment.name +
FROM subdepartment;
(1 row)
+-- Another reverse-listing example
+CREATE VIEW sums_1_100 AS
+WITH RECURSIVE t(n) AS (
+ VALUES (1)
+UNION ALL
+ SELECT n+1 FROM t WHERE n < 100
+)
+SELECT sum(n) FROM t;
+\d+ sums_1_100
+ View "public.sums_1_100"
+ Column | Type | Modifiers | Storage | Description
+--------+--------+-----------+---------+-------------
+ sum | bigint | | plain |
+View definition:
+ WITH RECURSIVE t(n) AS (
+ VALUES (1)
+ UNION ALL
+ SELECT t_1.n + 1
+ FROM t t_1
+ WHERE t_1.n < 100
+ )
+ SELECT sum(t.n) AS sum
+ FROM t;
+
+-- corner case in which sub-WITH gets initialized first
+with recursive q as (
+ select * from department
+ union all
+ (with x as (select * from q)
+ select * from x)
+ )
+select * from q limit 24;
+ id | parent_department | name
+----+-------------------+------
+ 0 | | ROOT
+ 1 | 0 | A
+ 2 | 1 | B
+ 3 | 2 | C
+ 4 | 2 | D
+ 5 | 0 | E
+ 6 | 4 | F
+ 7 | 5 | G
+ 0 | | ROOT
+ 1 | 0 | A
+ 2 | 1 | B
+ 3 | 2 | C
+ 4 | 2 | D
+ 5 | 0 | E
+ 6 | 4 | F
+ 7 | 5 | G
+ 0 | | ROOT
+ 1 | 0 | A
+ 2 | 1 | B
+ 3 | 2 | C
+ 4 | 2 | D
+ 5 | 0 | E
+ 6 | 4 | F
+ 7 | 5 | G
+(24 rows)
+
+with recursive q as (
+ select * from department
+ union all
+ (with recursive x as (
+ select * from department
+ union all
+ (select * from q union all select * from x)
+ )
+ select * from x)
+ )
+select * from q limit 32;
+ id | parent_department | name
+----+-------------------+------
+ 0 | | ROOT
+ 1 | 0 | A
+ 2 | 1 | B
+ 3 | 2 | C
+ 4 | 2 | D
+ 5 | 0 | E
+ 6 | 4 | F
+ 7 | 5 | G
+ 0 | | ROOT
+ 1 | 0 | A
+ 2 | 1 | B
+ 3 | 2 | C
+ 4 | 2 | D
+ 5 | 0 | E
+ 6 | 4 | F
+ 7 | 5 | G
+ 0 | | ROOT
+ 1 | 0 | A
+ 2 | 1 | B
+ 3 | 2 | C
+ 4 | 2 | D
+ 5 | 0 | E
+ 6 | 4 | F
+ 7 | 5 | G
+ 0 | | ROOT
+ 1 | 0 | A
+ 2 | 1 | B
+ 3 | 2 | C
+ 4 | 2 | D
+ 5 | 0 | E
+ 6 | 4 | F
+ 7 | 5 | G
+(32 rows)
+
-- recursive term has sub-UNION
WITH RECURSIVE t(i,j) AS (
VALUES (1,2)
id INTEGER PRIMARY KEY,
parent_id INTEGER REFERENCES tree(id)
);
-NOTICE: CREATE TABLE / PRIMARY KEY will create implicit index "tree_pkey" for table "tree"
INSERT INTO tree
VALUES (1, NULL), (2, 1), (3,1), (4,2), (5,2), (6,2), (7,3), (8,3),
(9,4), (10,4), (11,7), (12,7), (13,7), (14, 9), (15,11), (16,11);
3 | 7
(2 rows)
+-- this variant tickled a whole-row-variable bug in 8.4devel
+WITH RECURSIVE t(id, path) AS (
+ VALUES(1,ARRAY[]::integer[])
+UNION ALL
+ SELECT tree.id, t.path || tree.id
+ FROM tree JOIN t ON (tree.parent_id = t.id)
+)
+SELECT t1.id, t2.path, t2 FROM t AS t1 JOIN t AS t2 ON
+(t1.id=t2.id);
+ id | path | t2
+----+-------------+--------------------
+ 1 | {} | (1,{})
+ 2 | {2} | (2,{2})
+ 3 | {3} | (3,{3})
+ 4 | {2,4} | (4,"{2,4}")
+ 5 | {2,5} | (5,"{2,5}")
+ 6 | {2,6} | (6,"{2,6}")
+ 7 | {3,7} | (7,"{3,7}")
+ 8 | {3,8} | (8,"{3,8}")
+ 9 | {2,4,9} | (9,"{2,4,9}")
+ 10 | {2,4,10} | (10,"{2,4,10}")
+ 11 | {3,7,11} | (11,"{3,7,11}")
+ 12 | {3,7,12} | (12,"{3,7,12}")
+ 13 | {3,7,13} | (13,"{3,7,13}")
+ 14 | {2,4,9,14} | (14,"{2,4,9,14}")
+ 15 | {3,7,11,15} | (15,"{3,7,11,15}")
+ 16 | {3,7,11,16} | (16,"{3,7,11,16}")
+(16 rows)
+
+--
+-- test cycle detection
+--
+create temp table graph( f int, t int, label text );
+insert into graph values
+ (1, 2, 'arc 1 -> 2'),
+ (1, 3, 'arc 1 -> 3'),
+ (2, 3, 'arc 2 -> 3'),
+ (1, 4, 'arc 1 -> 4'),
+ (4, 5, 'arc 4 -> 5'),
+ (5, 1, 'arc 5 -> 1');
+with recursive search_graph(f, t, label, path, cycle) as (
+ select *, array[row(g.f, g.t)], false from graph g
+ union all
+ select g.*, path || row(g.f, g.t), row(g.f, g.t) = any(path)
+ from graph g, search_graph sg
+ where g.f = sg.t and not cycle
+)
+select * from search_graph;
+ f | t | label | path | cycle
+---+---+------------+-------------------------------------------+-------
+ 1 | 2 | arc 1 -> 2 | {"(1,2)"} | f
+ 1 | 3 | arc 1 -> 3 | {"(1,3)"} | f
+ 2 | 3 | arc 2 -> 3 | {"(2,3)"} | f
+ 1 | 4 | arc 1 -> 4 | {"(1,4)"} | f
+ 4 | 5 | arc 4 -> 5 | {"(4,5)"} | f
+ 5 | 1 | arc 5 -> 1 | {"(5,1)"} | f
+ 1 | 2 | arc 1 -> 2 | {"(5,1)","(1,2)"} | f
+ 1 | 3 | arc 1 -> 3 | {"(5,1)","(1,3)"} | f
+ 1 | 4 | arc 1 -> 4 | {"(5,1)","(1,4)"} | f
+ 2 | 3 | arc 2 -> 3 | {"(1,2)","(2,3)"} | f
+ 4 | 5 | arc 4 -> 5 | {"(1,4)","(4,5)"} | f
+ 5 | 1 | arc 5 -> 1 | {"(4,5)","(5,1)"} | f
+ 1 | 2 | arc 1 -> 2 | {"(4,5)","(5,1)","(1,2)"} | f
+ 1 | 3 | arc 1 -> 3 | {"(4,5)","(5,1)","(1,3)"} | f
+ 1 | 4 | arc 1 -> 4 | {"(4,5)","(5,1)","(1,4)"} | f
+ 2 | 3 | arc 2 -> 3 | {"(5,1)","(1,2)","(2,3)"} | f
+ 4 | 5 | arc 4 -> 5 | {"(5,1)","(1,4)","(4,5)"} | f
+ 5 | 1 | arc 5 -> 1 | {"(1,4)","(4,5)","(5,1)"} | f
+ 1 | 2 | arc 1 -> 2 | {"(1,4)","(4,5)","(5,1)","(1,2)"} | f
+ 1 | 3 | arc 1 -> 3 | {"(1,4)","(4,5)","(5,1)","(1,3)"} | f
+ 1 | 4 | arc 1 -> 4 | {"(1,4)","(4,5)","(5,1)","(1,4)"} | t
+ 2 | 3 | arc 2 -> 3 | {"(4,5)","(5,1)","(1,2)","(2,3)"} | f
+ 4 | 5 | arc 4 -> 5 | {"(4,5)","(5,1)","(1,4)","(4,5)"} | t
+ 5 | 1 | arc 5 -> 1 | {"(5,1)","(1,4)","(4,5)","(5,1)"} | t
+ 2 | 3 | arc 2 -> 3 | {"(1,4)","(4,5)","(5,1)","(1,2)","(2,3)"} | f
+(25 rows)
+
+-- ordering by the path column has same effect as SEARCH DEPTH FIRST
+with recursive search_graph(f, t, label, path, cycle) as (
+ select *, array[row(g.f, g.t)], false from graph g
+ union all
+ select g.*, path || row(g.f, g.t), row(g.f, g.t) = any(path)
+ from graph g, search_graph sg
+ where g.f = sg.t and not cycle
+)
+select * from search_graph order by path;
+ f | t | label | path | cycle
+---+---+------------+-------------------------------------------+-------
+ 1 | 2 | arc 1 -> 2 | {"(1,2)"} | f
+ 2 | 3 | arc 2 -> 3 | {"(1,2)","(2,3)"} | f
+ 1 | 3 | arc 1 -> 3 | {"(1,3)"} | f
+ 1 | 4 | arc 1 -> 4 | {"(1,4)"} | f
+ 4 | 5 | arc 4 -> 5 | {"(1,4)","(4,5)"} | f
+ 5 | 1 | arc 5 -> 1 | {"(1,4)","(4,5)","(5,1)"} | f
+ 1 | 2 | arc 1 -> 2 | {"(1,4)","(4,5)","(5,1)","(1,2)"} | f
+ 2 | 3 | arc 2 -> 3 | {"(1,4)","(4,5)","(5,1)","(1,2)","(2,3)"} | f
+ 1 | 3 | arc 1 -> 3 | {"(1,4)","(4,5)","(5,1)","(1,3)"} | f
+ 1 | 4 | arc 1 -> 4 | {"(1,4)","(4,5)","(5,1)","(1,4)"} | t
+ 2 | 3 | arc 2 -> 3 | {"(2,3)"} | f
+ 4 | 5 | arc 4 -> 5 | {"(4,5)"} | f
+ 5 | 1 | arc 5 -> 1 | {"(4,5)","(5,1)"} | f
+ 1 | 2 | arc 1 -> 2 | {"(4,5)","(5,1)","(1,2)"} | f
+ 2 | 3 | arc 2 -> 3 | {"(4,5)","(5,1)","(1,2)","(2,3)"} | f
+ 1 | 3 | arc 1 -> 3 | {"(4,5)","(5,1)","(1,3)"} | f
+ 1 | 4 | arc 1 -> 4 | {"(4,5)","(5,1)","(1,4)"} | f
+ 4 | 5 | arc 4 -> 5 | {"(4,5)","(5,1)","(1,4)","(4,5)"} | t
+ 5 | 1 | arc 5 -> 1 | {"(5,1)"} | f
+ 1 | 2 | arc 1 -> 2 | {"(5,1)","(1,2)"} | f
+ 2 | 3 | arc 2 -> 3 | {"(5,1)","(1,2)","(2,3)"} | f
+ 1 | 3 | arc 1 -> 3 | {"(5,1)","(1,3)"} | f
+ 1 | 4 | arc 1 -> 4 | {"(5,1)","(1,4)"} | f
+ 4 | 5 | arc 4 -> 5 | {"(5,1)","(1,4)","(4,5)"} | f
+ 5 | 1 | arc 5 -> 1 | {"(5,1)","(1,4)","(4,5)","(5,1)"} | t
+(25 rows)
+
--
-- test multiple WITH queries
--
10
(54 rows)
+--
+-- Test WITH attached to a data-modifying statement
+--
+CREATE TEMPORARY TABLE y (a INTEGER);
+INSERT INTO y SELECT generate_series(1, 10);
+WITH t AS (
+ SELECT a FROM y
+)
+INSERT INTO y
+SELECT a+20 FROM t RETURNING *;
+ a
+----
+ 21
+ 22
+ 23
+ 24
+ 25
+ 26
+ 27
+ 28
+ 29
+ 30
+(10 rows)
+
+SELECT * FROM y;
+ a
+----
+ 1
+ 2
+ 3
+ 4
+ 5
+ 6
+ 7
+ 8
+ 9
+ 10
+ 21
+ 22
+ 23
+ 24
+ 25
+ 26
+ 27
+ 28
+ 29
+ 30
+(20 rows)
+
+WITH t AS (
+ SELECT a FROM y
+)
+UPDATE y SET a = y.a-10 FROM t WHERE y.a > 20 AND t.a = y.a RETURNING y.a;
+ a
+----
+ 11
+ 12
+ 13
+ 14
+ 15
+ 16
+ 17
+ 18
+ 19
+ 20
+(10 rows)
+
+SELECT * FROM y;
+ a
+----
+ 1
+ 2
+ 3
+ 4
+ 5
+ 6
+ 7
+ 8
+ 9
+ 10
+ 11
+ 12
+ 13
+ 14
+ 15
+ 16
+ 17
+ 18
+ 19
+ 20
+(20 rows)
+
+WITH RECURSIVE t(a) AS (
+ SELECT 11
+ UNION ALL
+ SELECT a+1 FROM t WHERE a < 50
+)
+DELETE FROM y USING t WHERE t.a = y.a RETURNING y.a;
+ a
+----
+ 11
+ 12
+ 13
+ 14
+ 15
+ 16
+ 17
+ 18
+ 19
+ 20
+(10 rows)
+
+SELECT * FROM y;
+ a
+----
+ 1
+ 2
+ 3
+ 4
+ 5
+ 6
+ 7
+ 8
+ 9
+ 10
+(10 rows)
+
+DROP TABLE y;
--
-- error cases
--
--- UNION (should be supported someday)
-WITH RECURSIVE x(n) AS (SELECT 1 UNION SELECT n+1 FROM x)
- SELECT * FROM x;
-ERROR: recursive query "x" does not have the form non-recursive-term UNION ALL recursive-term
-LINE 1: WITH RECURSIVE x(n) AS (SELECT 1 UNION SELECT n+1 FROM x)
- ^
-- INTERSECT
WITH RECURSIVE x(n) AS (SELECT 1 INTERSECT SELECT n+1 FROM x)
SELECT * FROM x;
-ERROR: recursive query "x" does not have the form non-recursive-term UNION ALL recursive-term
+ERROR: recursive query "x" does not have the form non-recursive-term UNION [ALL] recursive-term
LINE 1: WITH RECURSIVE x(n) AS (SELECT 1 INTERSECT SELECT n+1 FROM x...
^
WITH RECURSIVE x(n) AS (SELECT 1 INTERSECT ALL SELECT n+1 FROM x)
SELECT * FROM x;
-ERROR: recursive query "x" does not have the form non-recursive-term UNION ALL recursive-term
+ERROR: recursive query "x" does not have the form non-recursive-term UNION [ALL] recursive-term
LINE 1: WITH RECURSIVE x(n) AS (SELECT 1 INTERSECT ALL SELECT n+1 FR...
^
-- EXCEPT
WITH RECURSIVE x(n) AS (SELECT 1 EXCEPT SELECT n+1 FROM x)
SELECT * FROM x;
-ERROR: recursive query "x" does not have the form non-recursive-term UNION ALL recursive-term
+ERROR: recursive query "x" does not have the form non-recursive-term UNION [ALL] recursive-term
LINE 1: WITH RECURSIVE x(n) AS (SELECT 1 EXCEPT SELECT n+1 FROM x)
^
WITH RECURSIVE x(n) AS (SELECT 1 EXCEPT ALL SELECT n+1 FROM x)
SELECT * FROM x;
-ERROR: recursive query "x" does not have the form non-recursive-term UNION ALL recursive-term
+ERROR: recursive query "x" does not have the form non-recursive-term UNION [ALL] recursive-term
LINE 1: WITH RECURSIVE x(n) AS (SELECT 1 EXCEPT ALL SELECT n+1 FROM ...
^
-- no non-recursive term
WITH RECURSIVE x(n) AS (SELECT n FROM x)
SELECT * FROM x;
-ERROR: recursive query "x" does not have the form non-recursive-term UNION ALL recursive-term
+ERROR: recursive query "x" does not have the form non-recursive-term UNION [ALL] recursive-term
LINE 1: WITH RECURSIVE x(n) AS (SELECT n FROM x)
^
-- recursive term in the left hand side (strictly speaking, should allow this)
-- aggregate functions
WITH RECURSIVE x(n) AS (SELECT 1 UNION ALL SELECT count(*) FROM x)
SELECT * FROM x;
-ERROR: aggregates not allowed in a recursive query's recursive term
+ERROR: aggregate functions are not allowed in a recursive query's recursive term
LINE 1: WITH RECURSIVE x(n) AS (SELECT 1 UNION ALL SELECT count(*) F...
^
WITH RECURSIVE x(n) AS (SELECT 1 UNION ALL SELECT sum(n) FROM x)
SELECT * FROM x;
-ERROR: aggregates not allowed in a recursive query's recursive term
+ERROR: aggregate functions are not allowed in a recursive query's recursive term
LINE 1: WITH RECURSIVE x(n) AS (SELECT 1 UNION ALL SELECT sum(n) FRO...
^
-- ORDER BY
LINE 2: (SELECT i::numeric(3,0) FROM (VALUES(1),(2)) t(i)
^
HINT: Cast the output of the non-recursive term to the correct type.
+-- disallow OLD/NEW reference in CTE
+CREATE TEMPORARY TABLE x (n integer);
+CREATE RULE r2 AS ON UPDATE TO x DO INSTEAD
+ WITH t AS (SELECT OLD.*) UPDATE y SET a = t.n FROM t;
+ERROR: cannot refer to OLD within WITH query
+--
+-- test for bug #4902
+--
+with cte(foo) as ( values(42) ) values((select foo from cte));
+ column1
+---------
+ 42
+(1 row)
+
+with cte(foo) as ( select 42 ) select * from ((select foo from cte)) q;
+ foo
+-----
+ 42
+(1 row)
+
+-- test CTE referencing an outer-level variable (to see that changed-parameter
+-- signaling still works properly after fixing this bug)
+select ( with cte(foo) as ( values(f1) )
+ select (select foo from cte) )
+from int4_tbl;
+ foo
+-------------
+ 0
+ 123456
+ -123456
+ 2147483647
+ -2147483647
+(5 rows)
+
+select ( with cte(foo) as ( values(f1) )
+ values((select foo from cte)) )
+from int4_tbl;
+ column1
+-------------
+ 0
+ 123456
+ -123456
+ 2147483647
+ -2147483647
+(5 rows)
+
+--
+-- test for nested-recursive-WITH bug
+--
+WITH RECURSIVE t(j) AS (
+ WITH RECURSIVE s(i) AS (
+ VALUES (1)
+ UNION ALL
+ SELECT i+1 FROM s WHERE i < 10
+ )
+ SELECT i FROM s
+ UNION ALL
+ SELECT j+1 FROM t WHERE j < 10
+)
+SELECT * FROM t;
+ j
+----
+ 1
+ 2
+ 3
+ 4
+ 5
+ 6
+ 7
+ 8
+ 9
+ 10
+ 2
+ 3
+ 4
+ 5
+ 6
+ 7
+ 8
+ 9
+ 10
+ 3
+ 4
+ 5
+ 6
+ 7
+ 8
+ 9
+ 10
+ 4
+ 5
+ 6
+ 7
+ 8
+ 9
+ 10
+ 5
+ 6
+ 7
+ 8
+ 9
+ 10
+ 6
+ 7
+ 8
+ 9
+ 10
+ 7
+ 8
+ 9
+ 10
+ 8
+ 9
+ 10
+ 9
+ 10
+ 10
+(55 rows)
+
+--
+-- test WITH attached to intermediate-level set operation
+--
+WITH outermost(x) AS (
+ SELECT 1
+ UNION (WITH innermost as (SELECT 2)
+ SELECT * FROM innermost
+ UNION SELECT 3)
+)
+SELECT * FROM outermost;
+ x
+---
+ 1
+ 2
+ 3
+(3 rows)
+
+WITH outermost(x) AS (
+ SELECT 1
+ UNION (WITH innermost as (SELECT 2)
+ SELECT * FROM outermost -- fail
+ UNION SELECT * FROM innermost)
+)
+SELECT * FROM outermost;
+ERROR: relation "outermost" does not exist
+LINE 4: SELECT * FROM outermost
+ ^
+DETAIL: There is a WITH item named "outermost", but it cannot be referenced from this part of the query.
+HINT: Use WITH RECURSIVE, or re-order the WITH items to remove forward references.
+WITH RECURSIVE outermost(x) AS (
+ SELECT 1
+ UNION (WITH innermost as (SELECT 2)
+ SELECT * FROM outermost
+ UNION SELECT * FROM innermost)
+)
+SELECT * FROM outermost;
+ x
+---
+ 1
+ 2
+(2 rows)
+
+WITH RECURSIVE outermost(x) AS (
+ WITH innermost as (SELECT 2 FROM outermost) -- fail
+ SELECT * FROM innermost
+ UNION SELECT * from outermost
+)
+SELECT * FROM outermost;
+ERROR: recursive reference to query "outermost" must not appear within a subquery
+LINE 2: WITH innermost as (SELECT 2 FROM outermost)
+ ^
+--
+-- This test will fail with the old implementation of PARAM_EXEC parameter
+-- assignment, because the "q1" Var passed down to A's targetlist subselect
+-- looks exactly like the "A.id" Var passed down to C's subselect, causing
+-- the old code to give them the same runtime PARAM_EXEC slot. But the
+-- lifespans of the two parameters overlap, thanks to B also reading A.
+--
+with
+A as ( select q2 as id, (select q1) as x from int8_tbl ),
+B as ( select id, row_number() over (partition by id) as r from A ),
+C as ( select A.id, array(select B.id from B where B.id = A.id) from A )
+select * from C;
+ id | array
+-------------------+-------------------------------------
+ 456 | {456}
+ 4567890123456789 | {4567890123456789,4567890123456789}
+ 123 | {123}
+ 4567890123456789 | {4567890123456789,4567890123456789}
+ -4567890123456789 | {-4567890123456789}
+(5 rows)
+
+--
+-- Test CTEs read in non-initialization orders
+--
+WITH RECURSIVE
+ tab(id_key,link) AS (VALUES (1,17), (2,17), (3,17), (4,17), (6,17), (5,17)),
+ iter (id_key, row_type, link) AS (
+ SELECT 0, 'base', 17
+ UNION ALL (
+ WITH remaining(id_key, row_type, link, min) AS (
+ SELECT tab.id_key, 'true'::text, iter.link, MIN(tab.id_key) OVER ()
+ FROM tab INNER JOIN iter USING (link)
+ WHERE tab.id_key > iter.id_key
+ ),
+ first_remaining AS (
+ SELECT id_key, row_type, link
+ FROM remaining
+ WHERE id_key=min
+ ),
+ effect AS (
+ SELECT tab.id_key, 'new'::text, tab.link
+ FROM first_remaining e INNER JOIN tab ON e.id_key=tab.id_key
+ WHERE e.row_type = 'false'
+ )
+ SELECT * FROM first_remaining
+ UNION ALL SELECT * FROM effect
+ )
+ )
+SELECT * FROM iter;
+ id_key | row_type | link
+--------+----------+------
+ 0 | base | 17
+ 1 | true | 17
+ 2 | true | 17
+ 3 | true | 17
+ 4 | true | 17
+ 5 | true | 17
+ 6 | true | 17
+(7 rows)
+
+WITH RECURSIVE
+ tab(id_key,link) AS (VALUES (1,17), (2,17), (3,17), (4,17), (6,17), (5,17)),
+ iter (id_key, row_type, link) AS (
+ SELECT 0, 'base', 17
+ UNION (
+ WITH remaining(id_key, row_type, link, min) AS (
+ SELECT tab.id_key, 'true'::text, iter.link, MIN(tab.id_key) OVER ()
+ FROM tab INNER JOIN iter USING (link)
+ WHERE tab.id_key > iter.id_key
+ ),
+ first_remaining AS (
+ SELECT id_key, row_type, link
+ FROM remaining
+ WHERE id_key=min
+ ),
+ effect AS (
+ SELECT tab.id_key, 'new'::text, tab.link
+ FROM first_remaining e INNER JOIN tab ON e.id_key=tab.id_key
+ WHERE e.row_type = 'false'
+ )
+ SELECT * FROM first_remaining
+ UNION ALL SELECT * FROM effect
+ )
+ )
+SELECT * FROM iter;
+ id_key | row_type | link
+--------+----------+------
+ 0 | base | 17
+ 1 | true | 17
+ 2 | true | 17
+ 3 | true | 17
+ 4 | true | 17
+ 5 | true | 17
+ 6 | true | 17
+(7 rows)
+
+--
+-- Data-modifying statements in WITH
+--
+-- INSERT ... RETURNING
+WITH t AS (
+ INSERT INTO y
+ VALUES
+ (11),
+ (12),
+ (13),
+ (14),
+ (15),
+ (16),
+ (17),
+ (18),
+ (19),
+ (20)
+ RETURNING *
+)
+SELECT * FROM t;
+ a
+----
+ 11
+ 12
+ 13
+ 14
+ 15
+ 16
+ 17
+ 18
+ 19
+ 20
+(10 rows)
+
+SELECT * FROM y;
+ a
+----
+ 1
+ 2
+ 3
+ 4
+ 5
+ 6
+ 7
+ 8
+ 9
+ 10
+ 11
+ 12
+ 13
+ 14
+ 15
+ 16
+ 17
+ 18
+ 19
+ 20
+(20 rows)
+
+-- UPDATE ... RETURNING
+WITH t AS (
+ UPDATE y
+ SET a=a+1
+ RETURNING *
+)
+SELECT * FROM t;
+ a
+----
+ 2
+ 3
+ 4
+ 5
+ 6
+ 7
+ 8
+ 9
+ 10
+ 11
+ 12
+ 13
+ 14
+ 15
+ 16
+ 17
+ 18
+ 19
+ 20
+ 21
+(20 rows)
+
+SELECT * FROM y;
+ a
+----
+ 2
+ 3
+ 4
+ 5
+ 6
+ 7
+ 8
+ 9
+ 10
+ 11
+ 12
+ 13
+ 14
+ 15
+ 16
+ 17
+ 18
+ 19
+ 20
+ 21
+(20 rows)
+
+-- DELETE ... RETURNING
+WITH t AS (
+ DELETE FROM y
+ WHERE a <= 10
+ RETURNING *
+)
+SELECT * FROM t;
+ a
+----
+ 2
+ 3
+ 4
+ 5
+ 6
+ 7
+ 8
+ 9
+ 10
+(9 rows)
+
+SELECT * FROM y;
+ a
+----
+ 11
+ 12
+ 13
+ 14
+ 15
+ 16
+ 17
+ 18
+ 19
+ 20
+ 21
+(11 rows)
+
+-- forward reference
+WITH RECURSIVE t AS (
+ INSERT INTO y
+ SELECT a+5 FROM t2 WHERE a > 5
+ RETURNING *
+), t2 AS (
+ UPDATE y SET a=a-11 RETURNING *
+)
+SELECT * FROM t
+UNION ALL
+SELECT * FROM t2;
+ a
+----
+ 11
+ 12
+ 13
+ 14
+ 15
+ 0
+ 1
+ 2
+ 3
+ 4
+ 5
+ 6
+ 7
+ 8
+ 9
+ 10
+(16 rows)
+
+SELECT * FROM y;
+ a
+----
+ 0
+ 1
+ 2
+ 3
+ 4
+ 5
+ 6
+ 11
+ 7
+ 12
+ 8
+ 13
+ 9
+ 14
+ 10
+ 15
+(16 rows)
+
+-- unconditional DO INSTEAD rule
+CREATE RULE y_rule AS ON DELETE TO y DO INSTEAD
+ INSERT INTO y VALUES(42) RETURNING *;
+WITH t AS (
+ DELETE FROM y RETURNING *
+)
+SELECT * FROM t;
+ a
+----
+ 42
+(1 row)
+
+SELECT * FROM y;
+ a
+----
+ 0
+ 1
+ 2
+ 3
+ 4
+ 5
+ 6
+ 11
+ 7
+ 12
+ 8
+ 13
+ 9
+ 14
+ 10
+ 15
+ 42
+(17 rows)
+
+DROP RULE y_rule ON y;
+-- check merging of outer CTE with CTE in a rule action
+CREATE TEMP TABLE bug6051 AS
+ select i from generate_series(1,3) as t(i);
+SELECT * FROM bug6051;
+ i
+---
+ 1
+ 2
+ 3
+(3 rows)
+
+WITH t1 AS ( DELETE FROM bug6051 RETURNING * )
+INSERT INTO bug6051 SELECT * FROM t1;
+SELECT * FROM bug6051;
+ i
+---
+ 1
+ 2
+ 3
+(3 rows)
+
+CREATE TEMP TABLE bug6051_2 (i int);
+CREATE RULE bug6051_ins AS ON INSERT TO bug6051 DO INSTEAD
+ INSERT INTO bug6051_2
+ SELECT NEW.i;
+WITH t1 AS ( DELETE FROM bug6051 RETURNING * )
+INSERT INTO bug6051 SELECT * FROM t1;
+SELECT * FROM bug6051;
+ i
+---
+(0 rows)
+
+SELECT * FROM bug6051_2;
+ i
+---
+ 1
+ 2
+ 3
+(3 rows)
+
+-- a truly recursive CTE in the same list
+WITH RECURSIVE t(a) AS (
+ SELECT 0
+ UNION ALL
+ SELECT a+1 FROM t WHERE a+1 < 5
+), t2 as (
+ INSERT INTO y
+ SELECT * FROM t RETURNING *
+)
+SELECT * FROM t2 JOIN y USING (a) ORDER BY a;
+ a
+---
+ 0
+ 1
+ 2
+ 3
+ 4
+(5 rows)
+
+SELECT * FROM y;
+ a
+----
+ 0
+ 1
+ 2
+ 3
+ 4
+ 5
+ 6
+ 11
+ 7
+ 12
+ 8
+ 13
+ 9
+ 14
+ 10
+ 15
+ 42
+ 0
+ 1
+ 2
+ 3
+ 4
+(22 rows)
+
+-- data-modifying WITH in a modifying statement
+WITH t AS (
+ DELETE FROM y
+ WHERE a <= 10
+ RETURNING *
+)
+INSERT INTO y SELECT -a FROM t RETURNING *;
+ a
+-----
+ 0
+ -1
+ -2
+ -3
+ -4
+ -5
+ -6
+ -7
+ -8
+ -9
+ -10
+ 0
+ -1
+ -2
+ -3
+ -4
+(16 rows)
+
+SELECT * FROM y;
+ a
+-----
+ 11
+ 12
+ 13
+ 14
+ 15
+ 42
+ 0
+ -1
+ -2
+ -3
+ -4
+ -5
+ -6
+ -7
+ -8
+ -9
+ -10
+ 0
+ -1
+ -2
+ -3
+ -4
+(22 rows)
+
+-- check that WITH query is run to completion even if outer query isn't
+WITH t AS (
+ UPDATE y SET a = a * 100 RETURNING *
+)
+SELECT * FROM t LIMIT 10;
+ a
+------
+ 1100
+ 1200
+ 1300
+ 1400
+ 1500
+ 4200
+ 0
+ -100
+ -200
+ -300
+(10 rows)
+
+SELECT * FROM y;
+ a
+-------
+ 1100
+ 1200
+ 1300
+ 1400
+ 1500
+ 4200
+ 0
+ -100
+ -200
+ -300
+ -400
+ -500
+ -600
+ -700
+ -800
+ -900
+ -1000
+ 0
+ -100
+ -200
+ -300
+ -400
+(22 rows)
+
+-- data-modifying WITH containing INSERT...ON CONFLICT DO UPDATE
+CREATE TABLE z AS SELECT i AS k, (i || ' v')::text v FROM generate_series(1, 16, 3) i;
+ALTER TABLE z ADD UNIQUE (k);
+WITH t AS (
+ INSERT INTO z SELECT i, 'insert'
+ FROM generate_series(0, 16) i
+ ON CONFLICT (k) DO UPDATE SET v = z.v || ', now update'
+ RETURNING *
+)
+SELECT * FROM t JOIN y ON t.k = y.a ORDER BY a, k;
+ k | v | a
+---+--------+---
+ 0 | insert | 0
+ 0 | insert | 0
+(2 rows)
+
+-- Test EXCLUDED.* reference within CTE
+WITH aa AS (
+ INSERT INTO z VALUES(1, 5) ON CONFLICT (k) DO UPDATE SET v = EXCLUDED.v
+ WHERE z.k != EXCLUDED.k
+ RETURNING *
+)
+SELECT * FROM aa;
+ k | v
+---+---
+(0 rows)
+
+-- New query/snapshot demonstrates side-effects of previous query.
+SELECT * FROM z ORDER BY k;
+ k | v
+----+------------------
+ 0 | insert
+ 1 | 1 v, now update
+ 2 | insert
+ 3 | insert
+ 4 | 4 v, now update
+ 5 | insert
+ 6 | insert
+ 7 | 7 v, now update
+ 8 | insert
+ 9 | insert
+ 10 | 10 v, now update
+ 11 | insert
+ 12 | insert
+ 13 | 13 v, now update
+ 14 | insert
+ 15 | insert
+ 16 | 16 v, now update
+(17 rows)
+
+--
+-- Ensure subqueries within the update clause work, even if they
+-- reference outside values
+--
+WITH aa AS (SELECT 1 a, 2 b)
+INSERT INTO z VALUES(1, 'insert')
+ON CONFLICT (k) DO UPDATE SET v = (SELECT b || ' update' FROM aa WHERE a = 1 LIMIT 1);
+WITH aa AS (SELECT 1 a, 2 b)
+INSERT INTO z VALUES(1, 'insert')
+ON CONFLICT (k) DO UPDATE SET v = ' update' WHERE z.k = (SELECT a FROM aa);
+WITH aa AS (SELECT 1 a, 2 b)
+INSERT INTO z VALUES(1, 'insert')
+ON CONFLICT (k) DO UPDATE SET v = (SELECT b || ' update' FROM aa WHERE a = 1 LIMIT 1);
+WITH aa AS (SELECT 'a' a, 'b' b UNION ALL SELECT 'a' a, 'b' b)
+INSERT INTO z VALUES(1, 'insert')
+ON CONFLICT (k) DO UPDATE SET v = (SELECT b || ' update' FROM aa WHERE a = 'a' LIMIT 1);
+WITH aa AS (SELECT 1 a, 2 b)
+INSERT INTO z VALUES(1, (SELECT b || ' insert' FROM aa WHERE a = 1 ))
+ON CONFLICT (k) DO UPDATE SET v = (SELECT b || ' update' FROM aa WHERE a = 1 LIMIT 1);
+-- This shows an attempt to update an invisible row, which should really be
+-- reported as a cardinality violation, but it doesn't seem worth fixing:
+WITH simpletup AS (
+ SELECT 2 k, 'Green' v),
+upsert_cte AS (
+ INSERT INTO z VALUES(2, 'Blue') ON CONFLICT (k) DO
+ UPDATE SET (k, v) = (SELECT k, v FROM simpletup WHERE simpletup.k = z.k)
+ RETURNING k, v)
+INSERT INTO z VALUES(2, 'Red') ON CONFLICT (k) DO
+UPDATE SET (k, v) = (SELECT k, v FROM upsert_cte WHERE upsert_cte.k = z.k)
+RETURNING k, v;
+ERROR: attempted to update invisible tuple
+DROP TABLE z;
+-- check that run to completion happens in proper ordering
+TRUNCATE TABLE y;
+INSERT INTO y SELECT generate_series(1, 3);
+CREATE TEMPORARY TABLE yy (a INTEGER);
+WITH RECURSIVE t1 AS (
+ INSERT INTO y SELECT * FROM y RETURNING *
+), t2 AS (
+ INSERT INTO yy SELECT * FROM t1 RETURNING *
+)
+SELECT 1;
+ ?column?
+----------
+ 1
+(1 row)
+
+SELECT * FROM y;
+ a
+---
+ 1
+ 2
+ 3
+ 1
+ 2
+ 3
+(6 rows)
+
+SELECT * FROM yy;
+ a
+---
+ 1
+ 2
+ 3
+(3 rows)
+
+WITH RECURSIVE t1 AS (
+ INSERT INTO yy SELECT * FROM t2 RETURNING *
+), t2 AS (
+ INSERT INTO y SELECT * FROM y RETURNING *
+)
+SELECT 1;
+ ?column?
+----------
+ 1
+(1 row)
+
+SELECT * FROM y;
+ a
+---
+ 1
+ 2
+ 3
+ 1
+ 2
+ 3
+ 1
+ 2
+ 3
+ 1
+ 2
+ 3
+(12 rows)
+
+SELECT * FROM yy;
+ a
+---
+ 1
+ 2
+ 3
+ 1
+ 2
+ 3
+ 1
+ 2
+ 3
+(9 rows)
+
+-- triggers
+TRUNCATE TABLE y;
+INSERT INTO y SELECT generate_series(1, 10);
+CREATE FUNCTION y_trigger() RETURNS trigger AS $$
+begin
+ raise notice 'y_trigger: a = %', new.a;
+ return new;
+end;
+$$ LANGUAGE plpgsql;
+CREATE TRIGGER y_trig BEFORE INSERT ON y FOR EACH ROW
+ EXECUTE PROCEDURE y_trigger();
+WITH t AS (
+ INSERT INTO y
+ VALUES
+ (21),
+ (22),
+ (23)
+ RETURNING *
+)
+SELECT * FROM t;
+NOTICE: y_trigger: a = 21
+NOTICE: y_trigger: a = 22
+NOTICE: y_trigger: a = 23
+ a
+----
+ 21
+ 22
+ 23
+(3 rows)
+
+SELECT * FROM y;
+ a
+----
+ 1
+ 2
+ 3
+ 4
+ 5
+ 6
+ 7
+ 8
+ 9
+ 10
+ 21
+ 22
+ 23
+(13 rows)
+
+DROP TRIGGER y_trig ON y;
+CREATE TRIGGER y_trig AFTER INSERT ON y FOR EACH ROW
+ EXECUTE PROCEDURE y_trigger();
+WITH t AS (
+ INSERT INTO y
+ VALUES
+ (31),
+ (32),
+ (33)
+ RETURNING *
+)
+SELECT * FROM t LIMIT 1;
+NOTICE: y_trigger: a = 31
+NOTICE: y_trigger: a = 32
+NOTICE: y_trigger: a = 33
+ a
+----
+ 31
+(1 row)
+
+SELECT * FROM y;
+ a
+----
+ 1
+ 2
+ 3
+ 4
+ 5
+ 6
+ 7
+ 8
+ 9
+ 10
+ 21
+ 22
+ 23
+ 31
+ 32
+ 33
+(16 rows)
+
+DROP TRIGGER y_trig ON y;
+CREATE OR REPLACE FUNCTION y_trigger() RETURNS trigger AS $$
+begin
+ raise notice 'y_trigger';
+ return null;
+end;
+$$ LANGUAGE plpgsql;
+CREATE TRIGGER y_trig AFTER INSERT ON y FOR EACH STATEMENT
+ EXECUTE PROCEDURE y_trigger();
+WITH t AS (
+ INSERT INTO y
+ VALUES
+ (41),
+ (42),
+ (43)
+ RETURNING *
+)
+SELECT * FROM t;
+NOTICE: y_trigger
+ a
+----
+ 41
+ 42
+ 43
+(3 rows)
+
+SELECT * FROM y;
+ a
+----
+ 1
+ 2
+ 3
+ 4
+ 5
+ 6
+ 7
+ 8
+ 9
+ 10
+ 21
+ 22
+ 23
+ 31
+ 32
+ 33
+ 41
+ 42
+ 43
+(19 rows)
+
+DROP TRIGGER y_trig ON y;
+DROP FUNCTION y_trigger();
+-- WITH attached to inherited UPDATE or DELETE
+CREATE TEMP TABLE parent ( id int, val text );
+CREATE TEMP TABLE child1 ( ) INHERITS ( parent );
+CREATE TEMP TABLE child2 ( ) INHERITS ( parent );
+INSERT INTO parent VALUES ( 1, 'p1' );
+INSERT INTO child1 VALUES ( 11, 'c11' ),( 12, 'c12' );
+INSERT INTO child2 VALUES ( 23, 'c21' ),( 24, 'c22' );
+WITH rcte AS ( SELECT sum(id) AS totalid FROM parent )
+UPDATE parent SET id = id + totalid FROM rcte;
+SELECT * FROM parent;
+ id | val
+----+-----
+ 72 | p1
+ 82 | c11
+ 83 | c12
+ 94 | c21
+ 95 | c22
+(5 rows)
+
+WITH wcte AS ( INSERT INTO child1 VALUES ( 42, 'new' ) RETURNING id AS newid )
+UPDATE parent SET id = id + newid FROM wcte;
+SELECT * FROM parent;
+ id | val
+-----+-----
+ 114 | p1
+ 42 | new
+ 124 | c11
+ 125 | c12
+ 136 | c21
+ 137 | c22
+(6 rows)
+
+WITH rcte AS ( SELECT max(id) AS maxid FROM parent )
+DELETE FROM parent USING rcte WHERE id = maxid;
+SELECT * FROM parent;
+ id | val
+-----+-----
+ 114 | p1
+ 42 | new
+ 124 | c11
+ 125 | c12
+ 136 | c21
+(5 rows)
+
+WITH wcte AS ( INSERT INTO child2 VALUES ( 42, 'new2' ) RETURNING id AS newid )
+DELETE FROM parent USING wcte WHERE id = newid;
+SELECT * FROM parent;
+ id | val
+-----+------
+ 114 | p1
+ 124 | c11
+ 125 | c12
+ 136 | c21
+ 42 | new2
+(5 rows)
+
+-- check EXPLAIN VERBOSE for a wCTE with RETURNING
+EXPLAIN (VERBOSE, COSTS OFF)
+WITH wcte AS ( INSERT INTO int8_tbl VALUES ( 42, 47 ) RETURNING q2 )
+DELETE FROM a USING wcte WHERE aa = q2;
+ QUERY PLAN
+----------------------------------------------------
+ Delete on public.a
+ Delete on public.a
+ Delete on public.b
+ Delete on public.c
+ Delete on public.d
+ CTE wcte
+ -> Insert on public.int8_tbl
+ Output: int8_tbl.q2
+ -> Result
+ Output: '42'::bigint, '47'::bigint
+ -> Nested Loop
+ Output: a.ctid, wcte.*
+ Join Filter: (a.aa = wcte.q2)
+ -> Seq Scan on public.a
+ Output: a.ctid, a.aa
+ -> CTE Scan on wcte
+ Output: wcte.*, wcte.q2
+ -> Nested Loop
+ Output: b.ctid, wcte.*
+ Join Filter: (b.aa = wcte.q2)
+ -> Seq Scan on public.b
+ Output: b.ctid, b.aa
+ -> CTE Scan on wcte
+ Output: wcte.*, wcte.q2
+ -> Nested Loop
+ Output: c.ctid, wcte.*
+ Join Filter: (c.aa = wcte.q2)
+ -> Seq Scan on public.c
+ Output: c.ctid, c.aa
+ -> CTE Scan on wcte
+ Output: wcte.*, wcte.q2
+ -> Nested Loop
+ Output: d.ctid, wcte.*
+ Join Filter: (d.aa = wcte.q2)
+ -> Seq Scan on public.d
+ Output: d.ctid, d.aa
+ -> CTE Scan on wcte
+ Output: wcte.*, wcte.q2
+(38 rows)
+
+-- error cases
+-- data-modifying WITH tries to use its own output
+WITH RECURSIVE t AS (
+ INSERT INTO y
+ SELECT * FROM t
+)
+VALUES(FALSE);
+ERROR: recursive query "t" must not contain data-modifying statements
+LINE 1: WITH RECURSIVE t AS (
+ ^
+-- no RETURNING in a referenced data-modifying WITH
+WITH t AS (
+ INSERT INTO y VALUES(0)
+)
+SELECT * FROM t;
+ERROR: WITH query "t" does not have a RETURNING clause
+LINE 4: SELECT * FROM t;
+ ^
+-- data-modifying WITH allowed only at the top level
+SELECT * FROM (
+ WITH t AS (UPDATE y SET a=a+1 RETURNING *)
+ SELECT * FROM t
+) ss;
+ERROR: WITH clause containing a data-modifying statement must be at the top level
+LINE 2: WITH t AS (UPDATE y SET a=a+1 RETURNING *)
+ ^
+-- most variants of rules aren't allowed
+CREATE RULE y_rule AS ON INSERT TO y WHERE a=0 DO INSTEAD DELETE FROM y;
+WITH t AS (
+ INSERT INTO y VALUES(0)
+)
+VALUES(FALSE);
+ERROR: conditional DO INSTEAD rules are not supported for data-modifying statements in WITH
+DROP RULE y_rule ON y;
+-- check that parser lookahead for WITH doesn't cause any odd behavior
+create table foo (with baz); -- fail, WITH is a reserved word
+ERROR: syntax error at or near "with"
+LINE 1: create table foo (with baz);
+ ^
+create table foo (with ordinality); -- fail, WITH is a reserved word
+ERROR: syntax error at or near "with"
+LINE 1: create table foo (with ordinality);
+ ^
+with ordinality as (select 1 as x) select * from ordinality;
+ x
+---
+ 1
+(1 row)
+
projects / postgresql / blobdiff