2 -- Tests for common table expressions (WITH query, ... SELECT ...)
5 WITH q1(x,y) AS (SELECT 1,2)
6 SELECT * FROM q1, q1 AS q2;
12 -- Multiple uses are evaluated only once
13 SELECT count(*) FROM (
14 WITH q1(x) AS (SELECT random() FROM generate_series(1, 5))
26 WITH RECURSIVE t(n) AS (
29 SELECT n+1 FROM t WHERE n < 100
37 WITH RECURSIVE t(n) AS (
40 SELECT n+1 FROM t WHERE n < 5
53 CREATE RECURSIVE VIEW nums (n) AS
56 SELECT n+1 FROM nums WHERE n < 5;
67 CREATE OR REPLACE RECURSIVE VIEW nums (n) AS
70 SELECT n+1 FROM nums WHERE n < 6;
82 -- This is an infinite loop with UNION ALL, but not with UNION
83 WITH RECURSIVE t(n) AS (
94 -- This'd be an infinite loop, but outside query reads only as much as needed
95 WITH RECURSIVE t(n) AS (
99 SELECT * FROM t LIMIT 10;
114 -- UNION case should have same property
115 WITH RECURSIVE t(n) AS (
119 SELECT * FROM t LIMIT 10;
134 -- Test behavior with an unknown-type literal in the WITH
135 WITH q AS (SELECT 'foo' AS x)
136 SELECT x, x IS OF (unknown) as is_unknown FROM q;
142 WITH RECURSIVE t(n) AS (
145 SELECT n || ' bar' FROM t WHERE length(n) < 20
147 SELECT n, n IS OF (text) as is_text FROM t;
149 -------------------------+---------
154 foo bar bar bar bar | t
155 foo bar bar bar bar bar | t
159 -- Some examples with a tree
161 -- department structure represented here is as follows:
163 -- ROOT-+->A-+->B-+->C
167 CREATE TEMP TABLE department (
168 id INTEGER PRIMARY KEY, -- department ID
169 parent_department INTEGER REFERENCES department, -- upper department ID
170 name TEXT -- department name
172 INSERT INTO department VALUES (0, NULL, 'ROOT');
173 INSERT INTO department VALUES (1, 0, 'A');
174 INSERT INTO department VALUES (2, 1, 'B');
175 INSERT INTO department VALUES (3, 2, 'C');
176 INSERT INTO department VALUES (4, 2, 'D');
177 INSERT INTO department VALUES (5, 0, 'E');
178 INSERT INTO department VALUES (6, 4, 'F');
179 INSERT INTO department VALUES (7, 5, 'G');
180 -- extract all departments under 'A'. Result should be A, B, C, D and F
181 WITH RECURSIVE subdepartment AS
183 -- non recursive term
184 SELECT name as root_name, * FROM department WHERE name = 'A'
187 SELECT sd.root_name, d.* FROM department AS d, subdepartment AS sd
188 WHERE d.parent_department = sd.id
190 SELECT * FROM subdepartment ORDER BY name;
191 root_name | id | parent_department | name
192 -----------+----+-------------------+------
200 -- extract all departments under 'A' with "level" number
201 WITH RECURSIVE subdepartment(level, id, parent_department, name) AS
203 -- non recursive term
204 SELECT 1, * FROM department WHERE name = 'A'
207 SELECT sd.level + 1, d.* FROM department AS d, subdepartment AS sd
208 WHERE d.parent_department = sd.id
210 SELECT * FROM subdepartment ORDER BY name;
211 level | id | parent_department | name
212 -------+----+-------------------+------
220 -- extract all departments under 'A' with "level" number.
221 -- Only shows level 2 or more
222 WITH RECURSIVE subdepartment(level, id, parent_department, name) AS
224 -- non recursive term
225 SELECT 1, * FROM department WHERE name = 'A'
228 SELECT sd.level + 1, d.* FROM department AS d, subdepartment AS sd
229 WHERE d.parent_department = sd.id
231 SELECT * FROM subdepartment WHERE level >= 2 ORDER BY name;
232 level | id | parent_department | name
233 -------+----+-------------------+------
240 -- "RECURSIVE" is ignored if the query has no self-reference
241 WITH RECURSIVE subdepartment AS
243 -- note lack of recursive UNION structure
244 SELECT * FROM department WHERE name = 'A'
246 SELECT * FROM subdepartment ORDER BY name;
247 id | parent_department | name
248 ----+-------------------+------
253 SELECT count(*) FROM (
254 WITH RECURSIVE t(n) AS (
255 SELECT 1 UNION ALL SELECT n + 1 FROM t WHERE n < 500
257 SELECT * FROM t) AS t WHERE n < (
258 SELECT count(*) FROM (
259 WITH RECURSIVE t(n) AS (
260 SELECT 1 UNION ALL SELECT n + 1 FROM t WHERE n < 100
262 SELECT * FROM t WHERE n < 50000
263 ) AS t WHERE n < 100);
269 -- use same CTE twice at different subquery levels
271 SELECT hundred, sum(ten) FROM tenk1 GROUP BY hundred
273 SELECT count(*) FROM q1 WHERE y > (SELECT sum(y)/100 FROM q1 qsub);
280 CREATE TEMPORARY VIEW vsubdepartment AS
281 WITH RECURSIVE subdepartment AS
283 -- non recursive term
284 SELECT * FROM department WHERE name = 'A'
287 SELECT d.* FROM department AS d, subdepartment AS sd
288 WHERE d.parent_department = sd.id
290 SELECT * FROM subdepartment;
291 SELECT * FROM vsubdepartment ORDER BY name;
292 id | parent_department | name
293 ----+-------------------+------
301 -- Check reverse listing
302 SELECT pg_get_viewdef('vsubdepartment'::regclass);
304 ---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
305 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;
308 SELECT pg_get_viewdef('vsubdepartment'::regclass, true);
310 -------------------------------------------------------------------------------
311 WITH RECURSIVE subdepartment AS ( +
312 SELECT department.id, department.parent_department, +
315 WHERE department.name = 'A'::text +
317 SELECT d.id, d.parent_department, d.name +
318 FROM department d, subdepartment sd +
319 WHERE d.parent_department = sd.id +
321 SELECT subdepartment.id, subdepartment.parent_department, subdepartment.name+
325 -- Another reverse-listing example
326 CREATE VIEW sums_1_100 AS
327 WITH RECURSIVE t(n) AS (
330 SELECT n+1 FROM t WHERE n < 100
332 SELECT sum(n) FROM t;
334 View "public.sums_1_100"
335 Column | Type | Modifiers | Storage | Description
336 --------+--------+-----------+---------+-------------
337 sum | bigint | | plain |
339 WITH RECURSIVE t(n) AS (
346 SELECT sum(t.n) AS sum
349 -- corner case in which sub-WITH gets initialized first
350 with recursive q as (
351 select * from department
353 (with x as (select * from q)
356 select * from q limit 24;
357 id | parent_department | name
358 ----+-------------------+------
385 with recursive q as (
386 select * from department
388 (with recursive x as (
389 select * from department
391 (select * from q union all select * from x)
395 select * from q limit 32;
396 id | parent_department | name
397 ----+-------------------+------
432 -- recursive term has sub-UNION
433 WITH RECURSIVE t(i,j) AS (
436 SELECT t2.i, t.j+1 FROM
437 (SELECT 2 AS i UNION ALL SELECT 3 AS i) AS t2
438 JOIN t ON (t2.i = t.i+1))
448 -- different tree example
450 CREATE TEMPORARY TABLE tree(
451 id INTEGER PRIMARY KEY,
452 parent_id INTEGER REFERENCES tree(id)
455 VALUES (1, NULL), (2, 1), (3,1), (4,2), (5,2), (6,2), (7,3), (8,3),
456 (9,4), (10,4), (11,7), (12,7), (13,7), (14, 9), (15,11), (16,11);
458 -- get all paths from "second level" nodes to leaf nodes
460 WITH RECURSIVE t(id, path) AS (
461 VALUES(1,ARRAY[]::integer[])
463 SELECT tree.id, t.path || tree.id
464 FROM tree JOIN t ON (tree.parent_id = t.id)
466 SELECT t1.*, t2.* FROM t AS t1 JOIN t AS t2 ON
467 (t1.path[1] = t2.path[1] AND
468 array_upper(t1.path,1) = 1 AND
469 array_upper(t2.path,1) > 1)
470 ORDER BY t1.id, t2.id;
471 id | path | id | path
472 ----+------+----+-------------
476 2 | {2} | 9 | {2,4,9}
477 2 | {2} | 10 | {2,4,10}
478 2 | {2} | 14 | {2,4,9,14}
481 3 | {3} | 11 | {3,7,11}
482 3 | {3} | 12 | {3,7,12}
483 3 | {3} | 13 | {3,7,13}
484 3 | {3} | 15 | {3,7,11,15}
485 3 | {3} | 16 | {3,7,11,16}
489 WITH RECURSIVE t(id, path) AS (
490 VALUES(1,ARRAY[]::integer[])
492 SELECT tree.id, t.path || tree.id
493 FROM tree JOIN t ON (tree.parent_id = t.id)
495 SELECT t1.id, count(t2.*) FROM t AS t1 JOIN t AS t2 ON
496 (t1.path[1] = t2.path[1] AND
497 array_upper(t1.path,1) = 1 AND
498 array_upper(t2.path,1) > 1)
507 -- this variant tickled a whole-row-variable bug in 8.4devel
508 WITH RECURSIVE t(id, path) AS (
509 VALUES(1,ARRAY[]::integer[])
511 SELECT tree.id, t.path || tree.id
512 FROM tree JOIN t ON (tree.parent_id = t.id)
514 SELECT t1.id, t2.path, t2 FROM t AS t1 JOIN t AS t2 ON
517 ----+-------------+--------------------
521 4 | {2,4} | (4,"{2,4}")
522 5 | {2,5} | (5,"{2,5}")
523 6 | {2,6} | (6,"{2,6}")
524 7 | {3,7} | (7,"{3,7}")
525 8 | {3,8} | (8,"{3,8}")
526 9 | {2,4,9} | (9,"{2,4,9}")
527 10 | {2,4,10} | (10,"{2,4,10}")
528 11 | {3,7,11} | (11,"{3,7,11}")
529 12 | {3,7,12} | (12,"{3,7,12}")
530 13 | {3,7,13} | (13,"{3,7,13}")
531 14 | {2,4,9,14} | (14,"{2,4,9,14}")
532 15 | {3,7,11,15} | (15,"{3,7,11,15}")
533 16 | {3,7,11,16} | (16,"{3,7,11,16}")
537 -- test cycle detection
539 create temp table graph( f int, t int, label text );
540 insert into graph values
541 (1, 2, 'arc 1 -> 2'),
542 (1, 3, 'arc 1 -> 3'),
543 (2, 3, 'arc 2 -> 3'),
544 (1, 4, 'arc 1 -> 4'),
545 (4, 5, 'arc 4 -> 5'),
546 (5, 1, 'arc 5 -> 1');
547 with recursive search_graph(f, t, label, path, cycle) as (
548 select *, array[row(g.f, g.t)], false from graph g
550 select g.*, path || row(g.f, g.t), row(g.f, g.t) = any(path)
551 from graph g, search_graph sg
552 where g.f = sg.t and not cycle
554 select * from search_graph;
555 f | t | label | path | cycle
556 ---+---+------------+-------------------------------------------+-------
557 1 | 2 | arc 1 -> 2 | {"(1,2)"} | f
558 1 | 3 | arc 1 -> 3 | {"(1,3)"} | f
559 2 | 3 | arc 2 -> 3 | {"(2,3)"} | f
560 1 | 4 | arc 1 -> 4 | {"(1,4)"} | f
561 4 | 5 | arc 4 -> 5 | {"(4,5)"} | f
562 5 | 1 | arc 5 -> 1 | {"(5,1)"} | f
563 1 | 2 | arc 1 -> 2 | {"(5,1)","(1,2)"} | f
564 1 | 3 | arc 1 -> 3 | {"(5,1)","(1,3)"} | f
565 1 | 4 | arc 1 -> 4 | {"(5,1)","(1,4)"} | f
566 2 | 3 | arc 2 -> 3 | {"(1,2)","(2,3)"} | f
567 4 | 5 | arc 4 -> 5 | {"(1,4)","(4,5)"} | f
568 5 | 1 | arc 5 -> 1 | {"(4,5)","(5,1)"} | f
569 1 | 2 | arc 1 -> 2 | {"(4,5)","(5,1)","(1,2)"} | f
570 1 | 3 | arc 1 -> 3 | {"(4,5)","(5,1)","(1,3)"} | f
571 1 | 4 | arc 1 -> 4 | {"(4,5)","(5,1)","(1,4)"} | f
572 2 | 3 | arc 2 -> 3 | {"(5,1)","(1,2)","(2,3)"} | f
573 4 | 5 | arc 4 -> 5 | {"(5,1)","(1,4)","(4,5)"} | f
574 5 | 1 | arc 5 -> 1 | {"(1,4)","(4,5)","(5,1)"} | f
575 1 | 2 | arc 1 -> 2 | {"(1,4)","(4,5)","(5,1)","(1,2)"} | f
576 1 | 3 | arc 1 -> 3 | {"(1,4)","(4,5)","(5,1)","(1,3)"} | f
577 1 | 4 | arc 1 -> 4 | {"(1,4)","(4,5)","(5,1)","(1,4)"} | t
578 2 | 3 | arc 2 -> 3 | {"(4,5)","(5,1)","(1,2)","(2,3)"} | f
579 4 | 5 | arc 4 -> 5 | {"(4,5)","(5,1)","(1,4)","(4,5)"} | t
580 5 | 1 | arc 5 -> 1 | {"(5,1)","(1,4)","(4,5)","(5,1)"} | t
581 2 | 3 | arc 2 -> 3 | {"(1,4)","(4,5)","(5,1)","(1,2)","(2,3)"} | f
584 -- ordering by the path column has same effect as SEARCH DEPTH FIRST
585 with recursive search_graph(f, t, label, path, cycle) as (
586 select *, array[row(g.f, g.t)], false from graph g
588 select g.*, path || row(g.f, g.t), row(g.f, g.t) = any(path)
589 from graph g, search_graph sg
590 where g.f = sg.t and not cycle
592 select * from search_graph order by path;
593 f | t | label | path | cycle
594 ---+---+------------+-------------------------------------------+-------
595 1 | 2 | arc 1 -> 2 | {"(1,2)"} | f
596 2 | 3 | arc 2 -> 3 | {"(1,2)","(2,3)"} | f
597 1 | 3 | arc 1 -> 3 | {"(1,3)"} | f
598 1 | 4 | arc 1 -> 4 | {"(1,4)"} | f
599 4 | 5 | arc 4 -> 5 | {"(1,4)","(4,5)"} | f
600 5 | 1 | arc 5 -> 1 | {"(1,4)","(4,5)","(5,1)"} | f
601 1 | 2 | arc 1 -> 2 | {"(1,4)","(4,5)","(5,1)","(1,2)"} | f
602 2 | 3 | arc 2 -> 3 | {"(1,4)","(4,5)","(5,1)","(1,2)","(2,3)"} | f
603 1 | 3 | arc 1 -> 3 | {"(1,4)","(4,5)","(5,1)","(1,3)"} | f
604 1 | 4 | arc 1 -> 4 | {"(1,4)","(4,5)","(5,1)","(1,4)"} | t
605 2 | 3 | arc 2 -> 3 | {"(2,3)"} | f
606 4 | 5 | arc 4 -> 5 | {"(4,5)"} | f
607 5 | 1 | arc 5 -> 1 | {"(4,5)","(5,1)"} | f
608 1 | 2 | arc 1 -> 2 | {"(4,5)","(5,1)","(1,2)"} | f
609 2 | 3 | arc 2 -> 3 | {"(4,5)","(5,1)","(1,2)","(2,3)"} | f
610 1 | 3 | arc 1 -> 3 | {"(4,5)","(5,1)","(1,3)"} | f
611 1 | 4 | arc 1 -> 4 | {"(4,5)","(5,1)","(1,4)"} | f
612 4 | 5 | arc 4 -> 5 | {"(4,5)","(5,1)","(1,4)","(4,5)"} | t
613 5 | 1 | arc 5 -> 1 | {"(5,1)"} | f
614 1 | 2 | arc 1 -> 2 | {"(5,1)","(1,2)"} | f
615 2 | 3 | arc 2 -> 3 | {"(5,1)","(1,2)","(2,3)"} | f
616 1 | 3 | arc 1 -> 3 | {"(5,1)","(1,3)"} | f
617 1 | 4 | arc 1 -> 4 | {"(5,1)","(1,4)"} | f
618 4 | 5 | arc 4 -> 5 | {"(5,1)","(1,4)","(4,5)"} | f
619 5 | 1 | arc 5 -> 1 | {"(5,1)","(1,4)","(4,5)","(5,1)"} | t
623 -- test multiple WITH queries
626 y (id) AS (VALUES (1)),
627 x (id) AS (SELECT * FROM y UNION ALL SELECT id+1 FROM x WHERE id < 5)
638 -- forward reference OK
640 x(id) AS (SELECT * FROM y UNION ALL SELECT id+1 FROM x WHERE id < 5),
641 y(id) AS (values (1))
654 (VALUES (1) UNION ALL SELECT id+1 FROM x WHERE id < 5),
656 (VALUES (1) UNION ALL SELECT id+1 FROM y WHERE id < 10)
657 SELECT y.*, x.* FROM y LEFT JOIN x USING (id);
674 (VALUES (1) UNION ALL SELECT id+1 FROM x WHERE id < 5),
676 (VALUES (1) UNION ALL SELECT id+1 FROM x WHERE id < 10)
677 SELECT y.*, x.* FROM y LEFT JOIN x USING (id);
690 (SELECT 1 UNION ALL SELECT id+1 FROM x WHERE id < 3 ),
692 (SELECT * FROM x UNION ALL SELECT * FROM x),
694 (SELECT * FROM x UNION ALL SELECT id+1 FROM z WHERE id < 10)
729 (SELECT 1 UNION ALL SELECT id+1 FROM x WHERE id < 3 ),
731 (SELECT * FROM x UNION ALL SELECT * FROM x),
733 (SELECT * FROM y UNION ALL SELECT id+1 FROM z WHERE id < 10)
794 -- Test WITH attached to a data-modifying statement
796 CREATE TEMPORARY TABLE y (a INTEGER);
797 INSERT INTO y SELECT generate_series(1, 10);
802 SELECT a+20 FROM t RETURNING *;
845 UPDATE y SET a = y.a-10 FROM t WHERE y.a > 20 AND t.a = y.a RETURNING y.a;
885 WITH RECURSIVE t(a) AS (
888 SELECT a+1 FROM t WHERE a < 50
890 DELETE FROM y USING t WHERE t.a = y.a RETURNING y.a;
925 WITH RECURSIVE x(n) AS (SELECT 1 INTERSECT SELECT n+1 FROM x)
927 ERROR: recursive query "x" does not have the form non-recursive-term UNION [ALL] recursive-term
928 LINE 1: WITH RECURSIVE x(n) AS (SELECT 1 INTERSECT SELECT n+1 FROM x...
930 WITH RECURSIVE x(n) AS (SELECT 1 INTERSECT ALL SELECT n+1 FROM x)
932 ERROR: recursive query "x" does not have the form non-recursive-term UNION [ALL] recursive-term
933 LINE 1: WITH RECURSIVE x(n) AS (SELECT 1 INTERSECT ALL SELECT n+1 FR...
936 WITH RECURSIVE x(n) AS (SELECT 1 EXCEPT SELECT n+1 FROM x)
938 ERROR: recursive query "x" does not have the form non-recursive-term UNION [ALL] recursive-term
939 LINE 1: WITH RECURSIVE x(n) AS (SELECT 1 EXCEPT SELECT n+1 FROM x)
941 WITH RECURSIVE x(n) AS (SELECT 1 EXCEPT ALL SELECT n+1 FROM x)
943 ERROR: recursive query "x" does not have the form non-recursive-term UNION [ALL] recursive-term
944 LINE 1: WITH RECURSIVE x(n) AS (SELECT 1 EXCEPT ALL SELECT n+1 FROM ...
946 -- no non-recursive term
947 WITH RECURSIVE x(n) AS (SELECT n FROM x)
949 ERROR: recursive query "x" does not have the form non-recursive-term UNION [ALL] recursive-term
950 LINE 1: WITH RECURSIVE x(n) AS (SELECT n FROM x)
952 -- recursive term in the left hand side (strictly speaking, should allow this)
953 WITH RECURSIVE x(n) AS (SELECT n FROM x UNION ALL SELECT 1)
955 ERROR: recursive reference to query "x" must not appear within its non-recursive term
956 LINE 1: WITH RECURSIVE x(n) AS (SELECT n FROM x UNION ALL SELECT 1)
958 CREATE TEMPORARY TABLE y (a INTEGER);
959 INSERT INTO y SELECT generate_series(1, 10);
961 WITH RECURSIVE x(n) AS (SELECT a FROM y WHERE a = 1
963 SELECT x.n+1 FROM y LEFT JOIN x ON x.n = y.a WHERE n < 10)
965 ERROR: recursive reference to query "x" must not appear within an outer join
966 LINE 3: SELECT x.n+1 FROM y LEFT JOIN x ON x.n = y.a WHERE n < 10)
969 WITH RECURSIVE x(n) AS (SELECT a FROM y WHERE a = 1
971 SELECT x.n+1 FROM x RIGHT JOIN y ON x.n = y.a WHERE n < 10)
973 ERROR: recursive reference to query "x" must not appear within an outer join
974 LINE 3: SELECT x.n+1 FROM x RIGHT JOIN y ON x.n = y.a WHERE n < 10)
977 WITH RECURSIVE x(n) AS (SELECT a FROM y WHERE a = 1
979 SELECT x.n+1 FROM x FULL JOIN y ON x.n = y.a WHERE n < 10)
981 ERROR: recursive reference to query "x" must not appear within an outer join
982 LINE 3: SELECT x.n+1 FROM x FULL JOIN y ON x.n = y.a WHERE n < 10)
985 WITH RECURSIVE x(n) AS (SELECT 1 UNION ALL SELECT n+1 FROM x
986 WHERE n IN (SELECT * FROM x))
988 ERROR: recursive reference to query "x" must not appear within a subquery
989 LINE 2: WHERE n IN (SELECT * FROM x))
991 -- aggregate functions
992 WITH RECURSIVE x(n) AS (SELECT 1 UNION ALL SELECT count(*) FROM x)
994 ERROR: aggregate functions are not allowed in a recursive query's recursive term
995 LINE 1: WITH RECURSIVE x(n) AS (SELECT 1 UNION ALL SELECT count(*) F...
997 WITH RECURSIVE x(n) AS (SELECT 1 UNION ALL SELECT sum(n) FROM x)
999 ERROR: aggregate functions are not allowed in a recursive query's recursive term
1000 LINE 1: WITH RECURSIVE x(n) AS (SELECT 1 UNION ALL SELECT sum(n) FRO...
1003 WITH RECURSIVE x(n) AS (SELECT 1 UNION ALL SELECT n+1 FROM x ORDER BY 1)
1005 ERROR: ORDER BY in a recursive query is not implemented
1006 LINE 1: ...VE x(n) AS (SELECT 1 UNION ALL SELECT n+1 FROM x ORDER BY 1)
1009 WITH RECURSIVE x(n) AS (SELECT 1 UNION ALL SELECT n+1 FROM x LIMIT 10 OFFSET 1)
1011 ERROR: OFFSET in a recursive query is not implemented
1012 LINE 1: ... AS (SELECT 1 UNION ALL SELECT n+1 FROM x LIMIT 10 OFFSET 1)
1015 WITH RECURSIVE x(n) AS (SELECT 1 UNION ALL SELECT n+1 FROM x FOR UPDATE)
1017 ERROR: FOR UPDATE/SHARE in a recursive query is not implemented
1018 -- target list has a recursive query name
1019 WITH RECURSIVE x(id) AS (values (1)
1021 SELECT (SELECT * FROM x) FROM x WHERE id < 5
1023 ERROR: recursive reference to query "x" must not appear within a subquery
1024 LINE 3: SELECT (SELECT * FROM x) FROM x WHERE id < 5
1026 -- mutual recursive query (not implemented)
1028 x (id) AS (SELECT 1 UNION ALL SELECT id+1 FROM y WHERE id < 5),
1029 y (id) AS (SELECT 1 UNION ALL SELECT id+1 FROM x WHERE id < 5)
1031 ERROR: mutual recursion between WITH items is not implemented
1032 LINE 2: x (id) AS (SELECT 1 UNION ALL SELECT id+1 FROM y WHERE id ...
1034 -- non-linear recursion is not allowed
1035 WITH RECURSIVE foo(i) AS
1038 (SELECT i+1 FROM foo WHERE i < 10
1040 SELECT i+1 FROM foo WHERE i < 5)
1041 ) SELECT * FROM foo;
1042 ERROR: recursive reference to query "foo" must not appear more than once
1043 LINE 6: SELECT i+1 FROM foo WHERE i < 5)
1045 WITH RECURSIVE foo(i) AS
1049 (SELECT i+1 FROM foo WHERE i < 10
1051 SELECT i+1 FROM foo WHERE i < 5) AS t
1052 ) SELECT * FROM foo;
1053 ERROR: recursive reference to query "foo" must not appear more than once
1054 LINE 7: SELECT i+1 FROM foo WHERE i < 5) AS t
1056 WITH RECURSIVE foo(i) AS
1059 (SELECT i+1 FROM foo WHERE i < 10
1061 SELECT i+1 FROM foo WHERE i < 5)
1062 ) SELECT * FROM foo;
1063 ERROR: recursive reference to query "foo" must not appear within EXCEPT
1064 LINE 6: SELECT i+1 FROM foo WHERE i < 5)
1066 WITH RECURSIVE foo(i) AS
1069 (SELECT i+1 FROM foo WHERE i < 10
1071 SELECT i+1 FROM foo WHERE i < 5)
1072 ) SELECT * FROM foo;
1073 ERROR: recursive reference to query "foo" must not appear more than once
1074 LINE 6: SELECT i+1 FROM foo WHERE i < 5)
1076 -- Wrong type induced from non-recursive term
1077 WITH RECURSIVE foo(i) AS
1078 (SELECT i FROM (VALUES(1),(2)) t(i)
1080 SELECT (i+1)::numeric(10,0) FROM foo WHERE i < 10)
1082 ERROR: recursive query "foo" column 1 has type integer in non-recursive term but type numeric overall
1083 LINE 2: (SELECT i FROM (VALUES(1),(2)) t(i)
1085 HINT: Cast the output of the non-recursive term to the correct type.
1086 -- rejects different typmod, too (should we allow this?)
1087 WITH RECURSIVE foo(i) AS
1088 (SELECT i::numeric(3,0) FROM (VALUES(1),(2)) t(i)
1090 SELECT (i+1)::numeric(10,0) FROM foo WHERE i < 10)
1092 ERROR: recursive query "foo" column 1 has type numeric(3,0) in non-recursive term but type numeric overall
1093 LINE 2: (SELECT i::numeric(3,0) FROM (VALUES(1),(2)) t(i)
1095 HINT: Cast the output of the non-recursive term to the correct type.
1096 -- disallow OLD/NEW reference in CTE
1097 CREATE TEMPORARY TABLE x (n integer);
1098 CREATE RULE r2 AS ON UPDATE TO x DO INSTEAD
1099 WITH t AS (SELECT OLD.*) UPDATE y SET a = t.n FROM t;
1100 ERROR: cannot refer to OLD within WITH query
1102 -- test for bug #4902
1104 with cte(foo) as ( values(42) ) values((select foo from cte));
1110 with cte(foo) as ( select 42 ) select * from ((select foo from cte)) q;
1116 -- test CTE referencing an outer-level variable (to see that changed-parameter
1117 -- signaling still works properly after fixing this bug)
1118 select ( with cte(foo) as ( values(f1) )
1119 select (select foo from cte) )
1130 select ( with cte(foo) as ( values(f1) )
1131 values((select foo from cte)) )
1143 -- test for nested-recursive-WITH bug
1145 WITH RECURSIVE t(j) AS (
1146 WITH RECURSIVE s(i) AS (
1149 SELECT i+1 FROM s WHERE i < 10
1153 SELECT j+1 FROM t WHERE j < 10
1216 -- test WITH attached to intermediate-level set operation
1218 WITH outermost(x) AS (
1220 UNION (WITH innermost as (SELECT 2)
1221 SELECT * FROM innermost
1224 SELECT * FROM outermost;
1232 WITH outermost(x) AS (
1234 UNION (WITH innermost as (SELECT 2)
1235 SELECT * FROM outermost -- fail
1236 UNION SELECT * FROM innermost)
1238 SELECT * FROM outermost;
1239 ERROR: relation "outermost" does not exist
1240 LINE 4: SELECT * FROM outermost
1242 DETAIL: There is a WITH item named "outermost", but it cannot be referenced from this part of the query.
1243 HINT: Use WITH RECURSIVE, or re-order the WITH items to remove forward references.
1244 WITH RECURSIVE outermost(x) AS (
1246 UNION (WITH innermost as (SELECT 2)
1247 SELECT * FROM outermost
1248 UNION SELECT * FROM innermost)
1250 SELECT * FROM outermost;
1257 WITH RECURSIVE outermost(x) AS (
1258 WITH innermost as (SELECT 2 FROM outermost) -- fail
1259 SELECT * FROM innermost
1260 UNION SELECT * from outermost
1262 SELECT * FROM outermost;
1263 ERROR: recursive reference to query "outermost" must not appear within a subquery
1264 LINE 2: WITH innermost as (SELECT 2 FROM outermost)
1267 -- This test will fail with the old implementation of PARAM_EXEC parameter
1268 -- assignment, because the "q1" Var passed down to A's targetlist subselect
1269 -- looks exactly like the "A.id" Var passed down to C's subselect, causing
1270 -- the old code to give them the same runtime PARAM_EXEC slot. But the
1271 -- lifespans of the two parameters overlap, thanks to B also reading A.
1274 A as ( select q2 as id, (select q1) as x from int8_tbl ),
1275 B as ( select id, row_number() over (partition by id) as r from A ),
1276 C as ( select A.id, array(select B.id from B where B.id = A.id) from A )
1279 -------------------+-------------------------------------
1281 4567890123456789 | {4567890123456789,4567890123456789}
1283 4567890123456789 | {4567890123456789,4567890123456789}
1284 -4567890123456789 | {-4567890123456789}
1288 -- Test CTEs read in non-initialization orders
1291 tab(id_key,link) AS (VALUES (1,17), (2,17), (3,17), (4,17), (6,17), (5,17)),
1292 iter (id_key, row_type, link) AS (
1293 SELECT 0, 'base', 17
1295 WITH remaining(id_key, row_type, link, min) AS (
1296 SELECT tab.id_key, 'true'::text, iter.link, MIN(tab.id_key) OVER ()
1297 FROM tab INNER JOIN iter USING (link)
1298 WHERE tab.id_key > iter.id_key
1300 first_remaining AS (
1301 SELECT id_key, row_type, link
1306 SELECT tab.id_key, 'new'::text, tab.link
1307 FROM first_remaining e INNER JOIN tab ON e.id_key=tab.id_key
1308 WHERE e.row_type = 'false'
1310 SELECT * FROM first_remaining
1311 UNION ALL SELECT * FROM effect
1315 id_key | row_type | link
1316 --------+----------+------
1327 tab(id_key,link) AS (VALUES (1,17), (2,17), (3,17), (4,17), (6,17), (5,17)),
1328 iter (id_key, row_type, link) AS (
1329 SELECT 0, 'base', 17
1331 WITH remaining(id_key, row_type, link, min) AS (
1332 SELECT tab.id_key, 'true'::text, iter.link, MIN(tab.id_key) OVER ()
1333 FROM tab INNER JOIN iter USING (link)
1334 WHERE tab.id_key > iter.id_key
1336 first_remaining AS (
1337 SELECT id_key, row_type, link
1342 SELECT tab.id_key, 'new'::text, tab.link
1343 FROM first_remaining e INNER JOIN tab ON e.id_key=tab.id_key
1344 WHERE e.row_type = 'false'
1346 SELECT * FROM first_remaining
1347 UNION ALL SELECT * FROM effect
1351 id_key | row_type | link
1352 --------+----------+------
1363 -- Data-modifying statements in WITH
1365 -- INSERT ... RETURNING
1421 -- UPDATE ... RETURNING
1477 -- DELETE ... RETURNING
1513 -- forward reference
1514 WITH RECURSIVE t AS (
1516 SELECT a+5 FROM t2 WHERE a > 5
1519 UPDATE y SET a=a-11 RETURNING *
1565 -- unconditional DO INSTEAD rule
1566 CREATE RULE y_rule AS ON DELETE TO y DO INSTEAD
1567 INSERT INTO y VALUES(42) RETURNING *;
1569 DELETE FROM y RETURNING *
1599 DROP RULE y_rule ON y;
1600 -- check merging of outer CTE with CTE in a rule action
1601 CREATE TEMP TABLE bug6051 AS
1602 select i from generate_series(1,3) as t(i);
1603 SELECT * FROM bug6051;
1611 WITH t1 AS ( DELETE FROM bug6051 RETURNING * )
1612 INSERT INTO bug6051 SELECT * FROM t1;
1613 SELECT * FROM bug6051;
1621 CREATE TEMP TABLE bug6051_2 (i int);
1622 CREATE RULE bug6051_ins AS ON INSERT TO bug6051 DO INSTEAD
1623 INSERT INTO bug6051_2
1625 WITH t1 AS ( DELETE FROM bug6051 RETURNING * )
1626 INSERT INTO bug6051 SELECT * FROM t1;
1627 SELECT * FROM bug6051;
1632 SELECT * FROM bug6051_2;
1640 -- a truly recursive CTE in the same list
1641 WITH RECURSIVE t(a) AS (
1644 SELECT a+1 FROM t WHERE a+1 < 5
1647 SELECT * FROM t RETURNING *
1649 SELECT * FROM t2 JOIN y USING (a) ORDER BY a;
1686 -- data-modifying WITH in a modifying statement
1692 INSERT INTO y SELECT -a FROM t RETURNING *;
1740 -- check that WITH query is run to completion even if outer query isn't
1742 UPDATE y SET a = a * 100 RETURNING *
1744 SELECT * FROM t LIMIT 10;
1786 -- check that run to completion happens in proper ordering
1788 INSERT INTO y SELECT generate_series(1, 3);
1789 CREATE TEMPORARY TABLE yy (a INTEGER);
1790 WITH RECURSIVE t1 AS (
1791 INSERT INTO y SELECT * FROM y RETURNING *
1793 INSERT INTO yy SELECT * FROM t1 RETURNING *
1820 WITH RECURSIVE t1 AS (
1821 INSERT INTO yy SELECT * FROM t2 RETURNING *
1823 INSERT INTO y SELECT * FROM y RETURNING *
1864 INSERT INTO y SELECT generate_series(1, 10);
1865 CREATE FUNCTION y_trigger() RETURNS trigger AS $$
1867 raise notice 'y_trigger: a = %', new.a;
1870 $$ LANGUAGE plpgsql;
1871 CREATE TRIGGER y_trig BEFORE INSERT ON y FOR EACH ROW
1872 EXECUTE PROCEDURE y_trigger();
1882 NOTICE: y_trigger: a = 21
1883 NOTICE: y_trigger: a = 22
1884 NOTICE: y_trigger: a = 23
1910 DROP TRIGGER y_trig ON y;
1911 CREATE TRIGGER y_trig AFTER INSERT ON y FOR EACH ROW
1912 EXECUTE PROCEDURE y_trigger();
1921 SELECT * FROM t LIMIT 1;
1922 NOTICE: y_trigger: a = 31
1923 NOTICE: y_trigger: a = 32
1924 NOTICE: y_trigger: a = 33
1951 DROP TRIGGER y_trig ON y;
1952 CREATE OR REPLACE FUNCTION y_trigger() RETURNS trigger AS $$
1954 raise notice 'y_trigger';
1957 $$ LANGUAGE plpgsql;
1958 CREATE TRIGGER y_trig AFTER INSERT ON y FOR EACH STATEMENT
1959 EXECUTE PROCEDURE y_trigger();
2001 DROP TRIGGER y_trig ON y;
2002 DROP FUNCTION y_trigger();
2003 -- WITH attached to inherited UPDATE or DELETE
2004 CREATE TEMP TABLE parent ( id int, val text );
2005 CREATE TEMP TABLE child1 ( ) INHERITS ( parent );
2006 CREATE TEMP TABLE child2 ( ) INHERITS ( parent );
2007 INSERT INTO parent VALUES ( 1, 'p1' );
2008 INSERT INTO child1 VALUES ( 11, 'c11' ),( 12, 'c12' );
2009 INSERT INTO child2 VALUES ( 23, 'c21' ),( 24, 'c22' );
2010 WITH rcte AS ( SELECT sum(id) AS totalid FROM parent )
2011 UPDATE parent SET id = id + totalid FROM rcte;
2012 SELECT * FROM parent;
2022 WITH wcte AS ( INSERT INTO child1 VALUES ( 42, 'new' ) RETURNING id AS newid )
2023 UPDATE parent SET id = id + newid FROM wcte;
2024 SELECT * FROM parent;
2035 WITH rcte AS ( SELECT max(id) AS maxid FROM parent )
2036 DELETE FROM parent USING rcte WHERE id = maxid;
2037 SELECT * FROM parent;
2047 WITH wcte AS ( INSERT INTO child2 VALUES ( 42, 'new2' ) RETURNING id AS newid )
2048 DELETE FROM parent USING wcte WHERE id = newid;
2049 SELECT * FROM parent;
2059 -- check EXPLAIN VERBOSE for a wCTE with RETURNING
2060 EXPLAIN (VERBOSE, COSTS OFF)
2061 WITH wcte AS ( INSERT INTO int8_tbl VALUES ( 42, 47 ) RETURNING q2 )
2062 DELETE FROM a USING wcte WHERE aa = q2;
2064 ------------------------------------------------
2067 -> Insert on public.int8_tbl
2070 Output: 42::bigint, 47::bigint
2072 Output: a.ctid, wcte.*
2073 Join Filter: (a.aa = wcte.q2)
2074 -> Seq Scan on public.a
2075 Output: a.ctid, a.aa
2077 Output: wcte.*, wcte.q2
2079 Output: b.ctid, wcte.*
2080 Join Filter: (b.aa = wcte.q2)
2081 -> Seq Scan on public.b
2082 Output: b.ctid, b.aa
2084 Output: wcte.*, wcte.q2
2086 Output: c.ctid, wcte.*
2087 Join Filter: (c.aa = wcte.q2)
2088 -> Seq Scan on public.c
2089 Output: c.ctid, c.aa
2091 Output: wcte.*, wcte.q2
2093 Output: d.ctid, wcte.*
2094 Join Filter: (d.aa = wcte.q2)
2095 -> Seq Scan on public.d
2096 Output: d.ctid, d.aa
2098 Output: wcte.*, wcte.q2
2102 -- data-modifying WITH tries to use its own output
2103 WITH RECURSIVE t AS (
2108 ERROR: recursive query "t" must not contain data-modifying statements
2109 LINE 1: WITH RECURSIVE t AS (
2111 -- no RETURNING in a referenced data-modifying WITH
2113 INSERT INTO y VALUES(0)
2116 ERROR: WITH query "t" does not have a RETURNING clause
2117 LINE 4: SELECT * FROM t;
2119 -- data-modifying WITH allowed only at the top level
2121 WITH t AS (UPDATE y SET a=a+1 RETURNING *)
2124 ERROR: WITH clause containing a data-modifying statement must be at the top level
2125 LINE 2: WITH t AS (UPDATE y SET a=a+1 RETURNING *)
2127 -- most variants of rules aren't allowed
2128 CREATE RULE y_rule AS ON INSERT TO y WHERE a=0 DO INSTEAD DELETE FROM y;
2130 INSERT INTO y VALUES(0)
2133 ERROR: conditional DO INSTEAD rules are not supported for data-modifying statements in WITH
2134 DROP RULE y_rule ON y;