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
52 -- This is an infinite loop with UNION ALL, but not with UNION
53 WITH RECURSIVE t(n) AS (
64 -- This'd be an infinite loop, but outside query reads only as much as needed
65 WITH RECURSIVE t(n) AS (
69 SELECT * FROM t LIMIT 10;
84 -- UNION case should have same property
85 WITH RECURSIVE t(n) AS (
89 SELECT * FROM t LIMIT 10;
104 -- Test behavior with an unknown-type literal in the WITH
105 WITH q AS (SELECT 'foo' AS x)
106 SELECT x, x IS OF (unknown) as is_unknown FROM q;
112 WITH RECURSIVE t(n) AS (
115 SELECT n || ' bar' FROM t WHERE length(n) < 20
117 SELECT n, n IS OF (text) as is_text FROM t;
119 -------------------------+---------
124 foo bar bar bar bar | t
125 foo bar bar bar bar bar | t
129 -- Some examples with a tree
131 -- department structure represented here is as follows:
133 -- ROOT-+->A-+->B-+->C
137 CREATE TEMP TABLE department (
138 id INTEGER PRIMARY KEY, -- department ID
139 parent_department INTEGER REFERENCES department, -- upper department ID
140 name TEXT -- department name
142 INSERT INTO department VALUES (0, NULL, 'ROOT');
143 INSERT INTO department VALUES (1, 0, 'A');
144 INSERT INTO department VALUES (2, 1, 'B');
145 INSERT INTO department VALUES (3, 2, 'C');
146 INSERT INTO department VALUES (4, 2, 'D');
147 INSERT INTO department VALUES (5, 0, 'E');
148 INSERT INTO department VALUES (6, 4, 'F');
149 INSERT INTO department VALUES (7, 5, 'G');
150 -- extract all departments under 'A'. Result should be A, B, C, D and F
151 WITH RECURSIVE subdepartment AS
153 -- non recursive term
154 SELECT name as root_name, * FROM department WHERE name = 'A'
157 SELECT sd.root_name, d.* FROM department AS d, subdepartment AS sd
158 WHERE d.parent_department = sd.id
160 SELECT * FROM subdepartment ORDER BY name;
161 root_name | id | parent_department | name
162 -----------+----+-------------------+------
170 -- extract all departments under 'A' with "level" number
171 WITH RECURSIVE subdepartment(level, id, parent_department, name) AS
173 -- non recursive term
174 SELECT 1, * FROM department WHERE name = 'A'
177 SELECT sd.level + 1, d.* FROM department AS d, subdepartment AS sd
178 WHERE d.parent_department = sd.id
180 SELECT * FROM subdepartment ORDER BY name;
181 level | id | parent_department | name
182 -------+----+-------------------+------
190 -- extract all departments under 'A' with "level" number.
191 -- Only shows level 2 or more
192 WITH RECURSIVE subdepartment(level, id, parent_department, name) AS
194 -- non recursive term
195 SELECT 1, * FROM department WHERE name = 'A'
198 SELECT sd.level + 1, d.* FROM department AS d, subdepartment AS sd
199 WHERE d.parent_department = sd.id
201 SELECT * FROM subdepartment WHERE level >= 2 ORDER BY name;
202 level | id | parent_department | name
203 -------+----+-------------------+------
210 -- "RECURSIVE" is ignored if the query has no self-reference
211 WITH RECURSIVE subdepartment AS
213 -- note lack of recursive UNION structure
214 SELECT * FROM department WHERE name = 'A'
216 SELECT * FROM subdepartment ORDER BY name;
217 id | parent_department | name
218 ----+-------------------+------
223 SELECT count(*) FROM (
224 WITH RECURSIVE t(n) AS (
225 SELECT 1 UNION ALL SELECT n + 1 FROM t WHERE n < 500
227 SELECT * FROM t) AS t WHERE n < (
228 SELECT count(*) FROM (
229 WITH RECURSIVE t(n) AS (
230 SELECT 1 UNION ALL SELECT n + 1 FROM t WHERE n < 100
232 SELECT * FROM t WHERE n < 50000
233 ) AS t WHERE n < 100);
239 -- use same CTE twice at different subquery levels
241 SELECT hundred, sum(ten) FROM tenk1 GROUP BY hundred
243 SELECT count(*) FROM q1 WHERE y > (SELECT sum(y)/100 FROM q1 qsub);
250 CREATE TEMPORARY VIEW vsubdepartment AS
251 WITH RECURSIVE subdepartment AS
253 -- non recursive term
254 SELECT * FROM department WHERE name = 'A'
257 SELECT d.* FROM department AS d, subdepartment AS sd
258 WHERE d.parent_department = sd.id
260 SELECT * FROM subdepartment;
261 SELECT * FROM vsubdepartment ORDER BY name;
262 id | parent_department | name
263 ----+-------------------+------
271 -- Check reverse listing
272 SELECT pg_get_viewdef('vsubdepartment'::regclass);
274 ---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
275 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;
278 SELECT pg_get_viewdef('vsubdepartment'::regclass, true);
280 -------------------------------------------------------------------------------
281 WITH RECURSIVE subdepartment AS ( +
282 SELECT department.id, department.parent_department, +
285 WHERE department.name = 'A'::text +
287 SELECT d.id, d.parent_department, d.name +
288 FROM department d, subdepartment sd +
289 WHERE d.parent_department = sd.id +
291 SELECT subdepartment.id, subdepartment.parent_department, subdepartment.name+
295 -- Another reverse-listing example
296 CREATE VIEW sums_1_100 AS
297 WITH RECURSIVE t(n) AS (
300 SELECT n+1 FROM t WHERE n < 100
302 SELECT sum(n) FROM t;
304 View "public.sums_1_100"
305 Column | Type | Modifiers | Storage | Description
306 --------+--------+-----------+---------+-------------
307 sum | bigint | | plain |
309 WITH RECURSIVE t(n) AS (
316 SELECT sum(t.n) AS sum
319 -- corner case in which sub-WITH gets initialized first
320 with recursive q as (
321 select * from department
323 (with x as (select * from q)
326 select * from q limit 24;
327 id | parent_department | name
328 ----+-------------------+------
355 with recursive q as (
356 select * from department
358 (with recursive x as (
359 select * from department
361 (select * from q union all select * from x)
365 select * from q limit 32;
366 id | parent_department | name
367 ----+-------------------+------
402 -- recursive term has sub-UNION
403 WITH RECURSIVE t(i,j) AS (
406 SELECT t2.i, t.j+1 FROM
407 (SELECT 2 AS i UNION ALL SELECT 3 AS i) AS t2
408 JOIN t ON (t2.i = t.i+1))
418 -- different tree example
420 CREATE TEMPORARY TABLE tree(
421 id INTEGER PRIMARY KEY,
422 parent_id INTEGER REFERENCES tree(id)
425 VALUES (1, NULL), (2, 1), (3,1), (4,2), (5,2), (6,2), (7,3), (8,3),
426 (9,4), (10,4), (11,7), (12,7), (13,7), (14, 9), (15,11), (16,11);
428 -- get all paths from "second level" nodes to leaf nodes
430 WITH RECURSIVE t(id, path) AS (
431 VALUES(1,ARRAY[]::integer[])
433 SELECT tree.id, t.path || tree.id
434 FROM tree JOIN t ON (tree.parent_id = t.id)
436 SELECT t1.*, t2.* FROM t AS t1 JOIN t AS t2 ON
437 (t1.path[1] = t2.path[1] AND
438 array_upper(t1.path,1) = 1 AND
439 array_upper(t2.path,1) > 1)
440 ORDER BY t1.id, t2.id;
441 id | path | id | path
442 ----+------+----+-------------
446 2 | {2} | 9 | {2,4,9}
447 2 | {2} | 10 | {2,4,10}
448 2 | {2} | 14 | {2,4,9,14}
451 3 | {3} | 11 | {3,7,11}
452 3 | {3} | 12 | {3,7,12}
453 3 | {3} | 13 | {3,7,13}
454 3 | {3} | 15 | {3,7,11,15}
455 3 | {3} | 16 | {3,7,11,16}
459 WITH RECURSIVE t(id, path) AS (
460 VALUES(1,ARRAY[]::integer[])
462 SELECT tree.id, t.path || tree.id
463 FROM tree JOIN t ON (tree.parent_id = t.id)
465 SELECT t1.id, count(t2.*) FROM t AS t1 JOIN t AS t2 ON
466 (t1.path[1] = t2.path[1] AND
467 array_upper(t1.path,1) = 1 AND
468 array_upper(t2.path,1) > 1)
477 -- this variant tickled a whole-row-variable bug in 8.4devel
478 WITH RECURSIVE t(id, path) AS (
479 VALUES(1,ARRAY[]::integer[])
481 SELECT tree.id, t.path || tree.id
482 FROM tree JOIN t ON (tree.parent_id = t.id)
484 SELECT t1.id, t2.path, t2 FROM t AS t1 JOIN t AS t2 ON
487 ----+-------------+--------------------
491 4 | {2,4} | (4,"{2,4}")
492 5 | {2,5} | (5,"{2,5}")
493 6 | {2,6} | (6,"{2,6}")
494 7 | {3,7} | (7,"{3,7}")
495 8 | {3,8} | (8,"{3,8}")
496 9 | {2,4,9} | (9,"{2,4,9}")
497 10 | {2,4,10} | (10,"{2,4,10}")
498 11 | {3,7,11} | (11,"{3,7,11}")
499 12 | {3,7,12} | (12,"{3,7,12}")
500 13 | {3,7,13} | (13,"{3,7,13}")
501 14 | {2,4,9,14} | (14,"{2,4,9,14}")
502 15 | {3,7,11,15} | (15,"{3,7,11,15}")
503 16 | {3,7,11,16} | (16,"{3,7,11,16}")
507 -- test cycle detection
509 create temp table graph( f int, t int, label text );
510 insert into graph values
511 (1, 2, 'arc 1 -> 2'),
512 (1, 3, 'arc 1 -> 3'),
513 (2, 3, 'arc 2 -> 3'),
514 (1, 4, 'arc 1 -> 4'),
515 (4, 5, 'arc 4 -> 5'),
516 (5, 1, 'arc 5 -> 1');
517 with recursive search_graph(f, t, label, path, cycle) as (
518 select *, array[row(g.f, g.t)], false from graph g
520 select g.*, path || row(g.f, g.t), row(g.f, g.t) = any(path)
521 from graph g, search_graph sg
522 where g.f = sg.t and not cycle
524 select * from search_graph;
525 f | t | label | path | cycle
526 ---+---+------------+-------------------------------------------+-------
527 1 | 2 | arc 1 -> 2 | {"(1,2)"} | f
528 1 | 3 | arc 1 -> 3 | {"(1,3)"} | f
529 2 | 3 | arc 2 -> 3 | {"(2,3)"} | f
530 1 | 4 | arc 1 -> 4 | {"(1,4)"} | f
531 4 | 5 | arc 4 -> 5 | {"(4,5)"} | f
532 5 | 1 | arc 5 -> 1 | {"(5,1)"} | f
533 1 | 2 | arc 1 -> 2 | {"(5,1)","(1,2)"} | f
534 1 | 3 | arc 1 -> 3 | {"(5,1)","(1,3)"} | f
535 1 | 4 | arc 1 -> 4 | {"(5,1)","(1,4)"} | f
536 2 | 3 | arc 2 -> 3 | {"(1,2)","(2,3)"} | f
537 4 | 5 | arc 4 -> 5 | {"(1,4)","(4,5)"} | f
538 5 | 1 | arc 5 -> 1 | {"(4,5)","(5,1)"} | f
539 1 | 2 | arc 1 -> 2 | {"(4,5)","(5,1)","(1,2)"} | f
540 1 | 3 | arc 1 -> 3 | {"(4,5)","(5,1)","(1,3)"} | f
541 1 | 4 | arc 1 -> 4 | {"(4,5)","(5,1)","(1,4)"} | f
542 2 | 3 | arc 2 -> 3 | {"(5,1)","(1,2)","(2,3)"} | f
543 4 | 5 | arc 4 -> 5 | {"(5,1)","(1,4)","(4,5)"} | f
544 5 | 1 | arc 5 -> 1 | {"(1,4)","(4,5)","(5,1)"} | f
545 1 | 2 | arc 1 -> 2 | {"(1,4)","(4,5)","(5,1)","(1,2)"} | f
546 1 | 3 | arc 1 -> 3 | {"(1,4)","(4,5)","(5,1)","(1,3)"} | f
547 1 | 4 | arc 1 -> 4 | {"(1,4)","(4,5)","(5,1)","(1,4)"} | t
548 2 | 3 | arc 2 -> 3 | {"(4,5)","(5,1)","(1,2)","(2,3)"} | f
549 4 | 5 | arc 4 -> 5 | {"(4,5)","(5,1)","(1,4)","(4,5)"} | t
550 5 | 1 | arc 5 -> 1 | {"(5,1)","(1,4)","(4,5)","(5,1)"} | t
551 2 | 3 | arc 2 -> 3 | {"(1,4)","(4,5)","(5,1)","(1,2)","(2,3)"} | f
554 -- ordering by the path column has same effect as SEARCH DEPTH FIRST
555 with recursive search_graph(f, t, label, path, cycle) as (
556 select *, array[row(g.f, g.t)], false from graph g
558 select g.*, path || row(g.f, g.t), row(g.f, g.t) = any(path)
559 from graph g, search_graph sg
560 where g.f = sg.t and not cycle
562 select * from search_graph order by path;
563 f | t | label | path | cycle
564 ---+---+------------+-------------------------------------------+-------
565 1 | 2 | arc 1 -> 2 | {"(1,2)"} | f
566 2 | 3 | arc 2 -> 3 | {"(1,2)","(2,3)"} | f
567 1 | 3 | arc 1 -> 3 | {"(1,3)"} | f
568 1 | 4 | arc 1 -> 4 | {"(1,4)"} | f
569 4 | 5 | arc 4 -> 5 | {"(1,4)","(4,5)"} | f
570 5 | 1 | arc 5 -> 1 | {"(1,4)","(4,5)","(5,1)"} | f
571 1 | 2 | arc 1 -> 2 | {"(1,4)","(4,5)","(5,1)","(1,2)"} | f
572 2 | 3 | arc 2 -> 3 | {"(1,4)","(4,5)","(5,1)","(1,2)","(2,3)"} | f
573 1 | 3 | arc 1 -> 3 | {"(1,4)","(4,5)","(5,1)","(1,3)"} | f
574 1 | 4 | arc 1 -> 4 | {"(1,4)","(4,5)","(5,1)","(1,4)"} | t
575 2 | 3 | arc 2 -> 3 | {"(2,3)"} | f
576 4 | 5 | arc 4 -> 5 | {"(4,5)"} | f
577 5 | 1 | arc 5 -> 1 | {"(4,5)","(5,1)"} | f
578 1 | 2 | arc 1 -> 2 | {"(4,5)","(5,1)","(1,2)"} | f
579 2 | 3 | arc 2 -> 3 | {"(4,5)","(5,1)","(1,2)","(2,3)"} | f
580 1 | 3 | arc 1 -> 3 | {"(4,5)","(5,1)","(1,3)"} | f
581 1 | 4 | arc 1 -> 4 | {"(4,5)","(5,1)","(1,4)"} | f
582 4 | 5 | arc 4 -> 5 | {"(4,5)","(5,1)","(1,4)","(4,5)"} | t
583 5 | 1 | arc 5 -> 1 | {"(5,1)"} | f
584 1 | 2 | arc 1 -> 2 | {"(5,1)","(1,2)"} | f
585 2 | 3 | arc 2 -> 3 | {"(5,1)","(1,2)","(2,3)"} | f
586 1 | 3 | arc 1 -> 3 | {"(5,1)","(1,3)"} | f
587 1 | 4 | arc 1 -> 4 | {"(5,1)","(1,4)"} | f
588 4 | 5 | arc 4 -> 5 | {"(5,1)","(1,4)","(4,5)"} | f
589 5 | 1 | arc 5 -> 1 | {"(5,1)","(1,4)","(4,5)","(5,1)"} | t
593 -- test multiple WITH queries
596 y (id) AS (VALUES (1)),
597 x (id) AS (SELECT * FROM y UNION ALL SELECT id+1 FROM x WHERE id < 5)
608 -- forward reference OK
610 x(id) AS (SELECT * FROM y UNION ALL SELECT id+1 FROM x WHERE id < 5),
611 y(id) AS (values (1))
624 (VALUES (1) UNION ALL SELECT id+1 FROM x WHERE id < 5),
626 (VALUES (1) UNION ALL SELECT id+1 FROM y WHERE id < 10)
627 SELECT y.*, x.* FROM y LEFT JOIN x USING (id);
644 (VALUES (1) UNION ALL SELECT id+1 FROM x WHERE id < 5),
646 (VALUES (1) UNION ALL SELECT id+1 FROM x WHERE id < 10)
647 SELECT y.*, x.* FROM y LEFT JOIN x USING (id);
660 (SELECT 1 UNION ALL SELECT id+1 FROM x WHERE id < 3 ),
662 (SELECT * FROM x UNION ALL SELECT * FROM x),
664 (SELECT * FROM x UNION ALL SELECT id+1 FROM z WHERE id < 10)
699 (SELECT 1 UNION ALL SELECT id+1 FROM x WHERE id < 3 ),
701 (SELECT * FROM x UNION ALL SELECT * FROM x),
703 (SELECT * FROM y UNION ALL SELECT id+1 FROM z WHERE id < 10)
764 -- Test WITH attached to a data-modifying statement
766 CREATE TEMPORARY TABLE y (a INTEGER);
767 INSERT INTO y SELECT generate_series(1, 10);
772 SELECT a+20 FROM t RETURNING *;
815 UPDATE y SET a = y.a-10 FROM t WHERE y.a > 20 AND t.a = y.a RETURNING y.a;
855 WITH RECURSIVE t(a) AS (
858 SELECT a+1 FROM t WHERE a < 50
860 DELETE FROM y USING t WHERE t.a = y.a RETURNING y.a;
895 WITH RECURSIVE x(n) AS (SELECT 1 INTERSECT SELECT n+1 FROM x)
897 ERROR: recursive query "x" does not have the form non-recursive-term UNION [ALL] recursive-term
898 LINE 1: WITH RECURSIVE x(n) AS (SELECT 1 INTERSECT SELECT n+1 FROM x...
900 WITH RECURSIVE x(n) AS (SELECT 1 INTERSECT ALL SELECT n+1 FROM x)
902 ERROR: recursive query "x" does not have the form non-recursive-term UNION [ALL] recursive-term
903 LINE 1: WITH RECURSIVE x(n) AS (SELECT 1 INTERSECT ALL SELECT n+1 FR...
906 WITH RECURSIVE x(n) AS (SELECT 1 EXCEPT SELECT n+1 FROM x)
908 ERROR: recursive query "x" does not have the form non-recursive-term UNION [ALL] recursive-term
909 LINE 1: WITH RECURSIVE x(n) AS (SELECT 1 EXCEPT SELECT n+1 FROM x)
911 WITH RECURSIVE x(n) AS (SELECT 1 EXCEPT ALL SELECT n+1 FROM x)
913 ERROR: recursive query "x" does not have the form non-recursive-term UNION [ALL] recursive-term
914 LINE 1: WITH RECURSIVE x(n) AS (SELECT 1 EXCEPT ALL SELECT n+1 FROM ...
916 -- no non-recursive term
917 WITH RECURSIVE x(n) AS (SELECT n FROM x)
919 ERROR: recursive query "x" does not have the form non-recursive-term UNION [ALL] recursive-term
920 LINE 1: WITH RECURSIVE x(n) AS (SELECT n FROM x)
922 -- recursive term in the left hand side (strictly speaking, should allow this)
923 WITH RECURSIVE x(n) AS (SELECT n FROM x UNION ALL SELECT 1)
925 ERROR: recursive reference to query "x" must not appear within its non-recursive term
926 LINE 1: WITH RECURSIVE x(n) AS (SELECT n FROM x UNION ALL SELECT 1)
928 CREATE TEMPORARY TABLE y (a INTEGER);
929 INSERT INTO y SELECT generate_series(1, 10);
931 WITH RECURSIVE x(n) AS (SELECT a FROM y WHERE a = 1
933 SELECT x.n+1 FROM y LEFT JOIN x ON x.n = y.a WHERE n < 10)
935 ERROR: recursive reference to query "x" must not appear within an outer join
936 LINE 3: SELECT x.n+1 FROM y LEFT JOIN x ON x.n = y.a WHERE n < 10)
939 WITH RECURSIVE x(n) AS (SELECT a FROM y WHERE a = 1
941 SELECT x.n+1 FROM x RIGHT JOIN y ON x.n = y.a WHERE n < 10)
943 ERROR: recursive reference to query "x" must not appear within an outer join
944 LINE 3: SELECT x.n+1 FROM x RIGHT JOIN y ON x.n = y.a WHERE n < 10)
947 WITH RECURSIVE x(n) AS (SELECT a FROM y WHERE a = 1
949 SELECT x.n+1 FROM x FULL JOIN y ON x.n = y.a WHERE n < 10)
951 ERROR: recursive reference to query "x" must not appear within an outer join
952 LINE 3: SELECT x.n+1 FROM x FULL JOIN y ON x.n = y.a WHERE n < 10)
955 WITH RECURSIVE x(n) AS (SELECT 1 UNION ALL SELECT n+1 FROM x
956 WHERE n IN (SELECT * FROM x))
958 ERROR: recursive reference to query "x" must not appear within a subquery
959 LINE 2: WHERE n IN (SELECT * FROM x))
961 -- aggregate functions
962 WITH RECURSIVE x(n) AS (SELECT 1 UNION ALL SELECT count(*) FROM x)
964 ERROR: aggregate functions are not allowed in a recursive query's recursive term
965 LINE 1: WITH RECURSIVE x(n) AS (SELECT 1 UNION ALL SELECT count(*) F...
967 WITH RECURSIVE x(n) AS (SELECT 1 UNION ALL SELECT sum(n) FROM x)
969 ERROR: aggregate functions are not allowed in a recursive query's recursive term
970 LINE 1: WITH RECURSIVE x(n) AS (SELECT 1 UNION ALL SELECT sum(n) FRO...
973 WITH RECURSIVE x(n) AS (SELECT 1 UNION ALL SELECT n+1 FROM x ORDER BY 1)
975 ERROR: ORDER BY in a recursive query is not implemented
976 LINE 1: ...VE x(n) AS (SELECT 1 UNION ALL SELECT n+1 FROM x ORDER BY 1)
979 WITH RECURSIVE x(n) AS (SELECT 1 UNION ALL SELECT n+1 FROM x LIMIT 10 OFFSET 1)
981 ERROR: OFFSET in a recursive query is not implemented
982 LINE 1: ... AS (SELECT 1 UNION ALL SELECT n+1 FROM x LIMIT 10 OFFSET 1)
985 WITH RECURSIVE x(n) AS (SELECT 1 UNION ALL SELECT n+1 FROM x FOR UPDATE)
987 ERROR: FOR UPDATE/SHARE in a recursive query is not implemented
988 -- target list has a recursive query name
989 WITH RECURSIVE x(id) AS (values (1)
991 SELECT (SELECT * FROM x) FROM x WHERE id < 5
993 ERROR: recursive reference to query "x" must not appear within a subquery
994 LINE 3: SELECT (SELECT * FROM x) FROM x WHERE id < 5
996 -- mutual recursive query (not implemented)
998 x (id) AS (SELECT 1 UNION ALL SELECT id+1 FROM y WHERE id < 5),
999 y (id) AS (SELECT 1 UNION ALL SELECT id+1 FROM x WHERE id < 5)
1001 ERROR: mutual recursion between WITH items is not implemented
1002 LINE 2: x (id) AS (SELECT 1 UNION ALL SELECT id+1 FROM y WHERE id ...
1004 -- non-linear recursion is not allowed
1005 WITH RECURSIVE foo(i) AS
1008 (SELECT i+1 FROM foo WHERE i < 10
1010 SELECT i+1 FROM foo WHERE i < 5)
1011 ) SELECT * FROM foo;
1012 ERROR: recursive reference to query "foo" must not appear more than once
1013 LINE 6: SELECT i+1 FROM foo WHERE i < 5)
1015 WITH RECURSIVE foo(i) AS
1019 (SELECT i+1 FROM foo WHERE i < 10
1021 SELECT i+1 FROM foo WHERE i < 5) AS t
1022 ) SELECT * FROM foo;
1023 ERROR: recursive reference to query "foo" must not appear more than once
1024 LINE 7: SELECT i+1 FROM foo WHERE i < 5) AS t
1026 WITH RECURSIVE foo(i) AS
1029 (SELECT i+1 FROM foo WHERE i < 10
1031 SELECT i+1 FROM foo WHERE i < 5)
1032 ) SELECT * FROM foo;
1033 ERROR: recursive reference to query "foo" must not appear within EXCEPT
1034 LINE 6: SELECT i+1 FROM foo WHERE i < 5)
1036 WITH RECURSIVE foo(i) AS
1039 (SELECT i+1 FROM foo WHERE i < 10
1041 SELECT i+1 FROM foo WHERE i < 5)
1042 ) SELECT * FROM foo;
1043 ERROR: recursive reference to query "foo" must not appear more than once
1044 LINE 6: SELECT i+1 FROM foo WHERE i < 5)
1046 -- Wrong type induced from non-recursive term
1047 WITH RECURSIVE foo(i) AS
1048 (SELECT i FROM (VALUES(1),(2)) t(i)
1050 SELECT (i+1)::numeric(10,0) FROM foo WHERE i < 10)
1052 ERROR: recursive query "foo" column 1 has type integer in non-recursive term but type numeric overall
1053 LINE 2: (SELECT i FROM (VALUES(1),(2)) t(i)
1055 HINT: Cast the output of the non-recursive term to the correct type.
1056 -- rejects different typmod, too (should we allow this?)
1057 WITH RECURSIVE foo(i) AS
1058 (SELECT i::numeric(3,0) FROM (VALUES(1),(2)) t(i)
1060 SELECT (i+1)::numeric(10,0) FROM foo WHERE i < 10)
1062 ERROR: recursive query "foo" column 1 has type numeric(3,0) in non-recursive term but type numeric overall
1063 LINE 2: (SELECT i::numeric(3,0) FROM (VALUES(1),(2)) t(i)
1065 HINT: Cast the output of the non-recursive term to the correct type.
1066 -- disallow OLD/NEW reference in CTE
1067 CREATE TEMPORARY TABLE x (n integer);
1068 CREATE RULE r2 AS ON UPDATE TO x DO INSTEAD
1069 WITH t AS (SELECT OLD.*) UPDATE y SET a = t.n FROM t;
1070 ERROR: cannot refer to OLD within WITH query
1072 -- test for bug #4902
1074 with cte(foo) as ( values(42) ) values((select foo from cte));
1080 with cte(foo) as ( select 42 ) select * from ((select foo from cte)) q;
1086 -- test CTE referencing an outer-level variable (to see that changed-parameter
1087 -- signaling still works properly after fixing this bug)
1088 select ( with cte(foo) as ( values(f1) )
1089 select (select foo from cte) )
1100 select ( with cte(foo) as ( values(f1) )
1101 values((select foo from cte)) )
1113 -- test for nested-recursive-WITH bug
1115 WITH RECURSIVE t(j) AS (
1116 WITH RECURSIVE s(i) AS (
1119 SELECT i+1 FROM s WHERE i < 10
1123 SELECT j+1 FROM t WHERE j < 10
1186 -- test WITH attached to intermediate-level set operation
1188 WITH outermost(x) AS (
1190 UNION (WITH innermost as (SELECT 2)
1191 SELECT * FROM innermost
1194 SELECT * FROM outermost;
1202 WITH outermost(x) AS (
1204 UNION (WITH innermost as (SELECT 2)
1205 SELECT * FROM outermost -- fail
1206 UNION SELECT * FROM innermost)
1208 SELECT * FROM outermost;
1209 ERROR: relation "outermost" does not exist
1210 LINE 4: SELECT * FROM outermost
1212 DETAIL: There is a WITH item named "outermost", but it cannot be referenced from this part of the query.
1213 HINT: Use WITH RECURSIVE, or re-order the WITH items to remove forward references.
1214 WITH RECURSIVE outermost(x) AS (
1216 UNION (WITH innermost as (SELECT 2)
1217 SELECT * FROM outermost
1218 UNION SELECT * FROM innermost)
1220 SELECT * FROM outermost;
1227 WITH RECURSIVE outermost(x) AS (
1228 WITH innermost as (SELECT 2 FROM outermost) -- fail
1229 SELECT * FROM innermost
1230 UNION SELECT * from outermost
1232 SELECT * FROM outermost;
1233 ERROR: recursive reference to query "outermost" must not appear within a subquery
1234 LINE 2: WITH innermost as (SELECT 2 FROM outermost)
1237 -- This test will fail with the old implementation of PARAM_EXEC parameter
1238 -- assignment, because the "q1" Var passed down to A's targetlist subselect
1239 -- looks exactly like the "A.id" Var passed down to C's subselect, causing
1240 -- the old code to give them the same runtime PARAM_EXEC slot. But the
1241 -- lifespans of the two parameters overlap, thanks to B also reading A.
1244 A as ( select q2 as id, (select q1) as x from int8_tbl ),
1245 B as ( select id, row_number() over (partition by id) as r from A ),
1246 C as ( select A.id, array(select B.id from B where B.id = A.id) from A )
1249 -------------------+-------------------------------------
1251 4567890123456789 | {4567890123456789,4567890123456789}
1253 4567890123456789 | {4567890123456789,4567890123456789}
1254 -4567890123456789 | {-4567890123456789}
1258 -- Test CTEs read in non-initialization orders
1261 tab(id_key,link) AS (VALUES (1,17), (2,17), (3,17), (4,17), (6,17), (5,17)),
1262 iter (id_key, row_type, link) AS (
1263 SELECT 0, 'base', 17
1265 WITH remaining(id_key, row_type, link, min) AS (
1266 SELECT tab.id_key, 'true'::text, iter.link, MIN(tab.id_key) OVER ()
1267 FROM tab INNER JOIN iter USING (link)
1268 WHERE tab.id_key > iter.id_key
1270 first_remaining AS (
1271 SELECT id_key, row_type, link
1276 SELECT tab.id_key, 'new'::text, tab.link
1277 FROM first_remaining e INNER JOIN tab ON e.id_key=tab.id_key
1278 WHERE e.row_type = 'false'
1280 SELECT * FROM first_remaining
1281 UNION ALL SELECT * FROM effect
1285 id_key | row_type | link
1286 --------+----------+------
1297 tab(id_key,link) AS (VALUES (1,17), (2,17), (3,17), (4,17), (6,17), (5,17)),
1298 iter (id_key, row_type, link) AS (
1299 SELECT 0, 'base', 17
1301 WITH remaining(id_key, row_type, link, min) AS (
1302 SELECT tab.id_key, 'true'::text, iter.link, MIN(tab.id_key) OVER ()
1303 FROM tab INNER JOIN iter USING (link)
1304 WHERE tab.id_key > iter.id_key
1306 first_remaining AS (
1307 SELECT id_key, row_type, link
1312 SELECT tab.id_key, 'new'::text, tab.link
1313 FROM first_remaining e INNER JOIN tab ON e.id_key=tab.id_key
1314 WHERE e.row_type = 'false'
1316 SELECT * FROM first_remaining
1317 UNION ALL SELECT * FROM effect
1321 id_key | row_type | link
1322 --------+----------+------
1333 -- Data-modifying statements in WITH
1335 -- INSERT ... RETURNING
1391 -- UPDATE ... RETURNING
1447 -- DELETE ... RETURNING
1483 -- forward reference
1484 WITH RECURSIVE t AS (
1486 SELECT a+5 FROM t2 WHERE a > 5
1489 UPDATE y SET a=a-11 RETURNING *
1535 -- unconditional DO INSTEAD rule
1536 CREATE RULE y_rule AS ON DELETE TO y DO INSTEAD
1537 INSERT INTO y VALUES(42) RETURNING *;
1539 DELETE FROM y RETURNING *
1569 DROP RULE y_rule ON y;
1570 -- check merging of outer CTE with CTE in a rule action
1571 CREATE TEMP TABLE bug6051 AS
1572 select i from generate_series(1,3) as t(i);
1573 SELECT * FROM bug6051;
1581 WITH t1 AS ( DELETE FROM bug6051 RETURNING * )
1582 INSERT INTO bug6051 SELECT * FROM t1;
1583 SELECT * FROM bug6051;
1591 CREATE TEMP TABLE bug6051_2 (i int);
1592 CREATE RULE bug6051_ins AS ON INSERT TO bug6051 DO INSTEAD
1593 INSERT INTO bug6051_2
1595 WITH t1 AS ( DELETE FROM bug6051 RETURNING * )
1596 INSERT INTO bug6051 SELECT * FROM t1;
1597 SELECT * FROM bug6051;
1602 SELECT * FROM bug6051_2;
1610 -- a truly recursive CTE in the same list
1611 WITH RECURSIVE t(a) AS (
1614 SELECT a+1 FROM t WHERE a+1 < 5
1617 SELECT * FROM t RETURNING *
1619 SELECT * FROM t2 JOIN y USING (a) ORDER BY a;
1656 -- data-modifying WITH in a modifying statement
1662 INSERT INTO y SELECT -a FROM t RETURNING *;
1710 -- check that WITH query is run to completion even if outer query isn't
1712 UPDATE y SET a = a * 100 RETURNING *
1714 SELECT * FROM t LIMIT 10;
1756 -- check that run to completion happens in proper ordering
1758 INSERT INTO y SELECT generate_series(1, 3);
1759 CREATE TEMPORARY TABLE yy (a INTEGER);
1760 WITH RECURSIVE t1 AS (
1761 INSERT INTO y SELECT * FROM y RETURNING *
1763 INSERT INTO yy SELECT * FROM t1 RETURNING *
1790 WITH RECURSIVE t1 AS (
1791 INSERT INTO yy SELECT * FROM t2 RETURNING *
1793 INSERT INTO y SELECT * FROM y RETURNING *
1834 INSERT INTO y SELECT generate_series(1, 10);
1835 CREATE FUNCTION y_trigger() RETURNS trigger AS $$
1837 raise notice 'y_trigger: a = %', new.a;
1840 $$ LANGUAGE plpgsql;
1841 CREATE TRIGGER y_trig BEFORE INSERT ON y FOR EACH ROW
1842 EXECUTE PROCEDURE y_trigger();
1852 NOTICE: y_trigger: a = 21
1853 NOTICE: y_trigger: a = 22
1854 NOTICE: y_trigger: a = 23
1880 DROP TRIGGER y_trig ON y;
1881 CREATE TRIGGER y_trig AFTER INSERT ON y FOR EACH ROW
1882 EXECUTE PROCEDURE y_trigger();
1891 SELECT * FROM t LIMIT 1;
1892 NOTICE: y_trigger: a = 31
1893 NOTICE: y_trigger: a = 32
1894 NOTICE: y_trigger: a = 33
1921 DROP TRIGGER y_trig ON y;
1922 CREATE OR REPLACE FUNCTION y_trigger() RETURNS trigger AS $$
1924 raise notice 'y_trigger';
1927 $$ LANGUAGE plpgsql;
1928 CREATE TRIGGER y_trig AFTER INSERT ON y FOR EACH STATEMENT
1929 EXECUTE PROCEDURE y_trigger();
1971 DROP TRIGGER y_trig ON y;
1972 DROP FUNCTION y_trigger();
1973 -- WITH attached to inherited UPDATE or DELETE
1974 CREATE TEMP TABLE parent ( id int, val text );
1975 CREATE TEMP TABLE child1 ( ) INHERITS ( parent );
1976 CREATE TEMP TABLE child2 ( ) INHERITS ( parent );
1977 INSERT INTO parent VALUES ( 1, 'p1' );
1978 INSERT INTO child1 VALUES ( 11, 'c11' ),( 12, 'c12' );
1979 INSERT INTO child2 VALUES ( 23, 'c21' ),( 24, 'c22' );
1980 WITH rcte AS ( SELECT sum(id) AS totalid FROM parent )
1981 UPDATE parent SET id = id + totalid FROM rcte;
1982 SELECT * FROM parent;
1992 WITH wcte AS ( INSERT INTO child1 VALUES ( 42, 'new' ) RETURNING id AS newid )
1993 UPDATE parent SET id = id + newid FROM wcte;
1994 SELECT * FROM parent;
2005 WITH rcte AS ( SELECT max(id) AS maxid FROM parent )
2006 DELETE FROM parent USING rcte WHERE id = maxid;
2007 SELECT * FROM parent;
2017 WITH wcte AS ( INSERT INTO child2 VALUES ( 42, 'new2' ) RETURNING id AS newid )
2018 DELETE FROM parent USING wcte WHERE id = newid;
2019 SELECT * FROM parent;
2029 -- check EXPLAIN VERBOSE for a wCTE with RETURNING
2030 EXPLAIN (VERBOSE, COSTS OFF)
2031 WITH wcte AS ( INSERT INTO int8_tbl VALUES ( 42, 47 ) RETURNING q2 )
2032 DELETE FROM a USING wcte WHERE aa = q2;
2034 ------------------------------------------------
2037 -> Insert on public.int8_tbl
2040 Output: 42::bigint, 47::bigint
2042 Output: a.ctid, wcte.*
2043 Join Filter: (a.aa = wcte.q2)
2044 -> Seq Scan on public.a
2045 Output: a.ctid, a.aa
2047 Output: wcte.*, wcte.q2
2049 Output: b.ctid, wcte.*
2050 Join Filter: (b.aa = wcte.q2)
2051 -> Seq Scan on public.b
2052 Output: b.ctid, b.aa
2054 Output: wcte.*, wcte.q2
2056 Output: c.ctid, wcte.*
2057 Join Filter: (c.aa = wcte.q2)
2058 -> Seq Scan on public.c
2059 Output: c.ctid, c.aa
2061 Output: wcte.*, wcte.q2
2063 Output: d.ctid, wcte.*
2064 Join Filter: (d.aa = wcte.q2)
2065 -> Seq Scan on public.d
2066 Output: d.ctid, d.aa
2068 Output: wcte.*, wcte.q2
2072 -- data-modifying WITH tries to use its own output
2073 WITH RECURSIVE t AS (
2078 ERROR: recursive query "t" must not contain data-modifying statements
2079 LINE 1: WITH RECURSIVE t AS (
2081 -- no RETURNING in a referenced data-modifying WITH
2083 INSERT INTO y VALUES(0)
2086 ERROR: WITH query "t" does not have a RETURNING clause
2087 LINE 4: SELECT * FROM t;
2089 -- data-modifying WITH allowed only at the top level
2091 WITH t AS (UPDATE y SET a=a+1 RETURNING *)
2094 ERROR: WITH clause containing a data-modifying statement must be at the top level
2095 LINE 2: WITH t AS (UPDATE y SET a=a+1 RETURNING *)
2097 -- most variants of rules aren't allowed
2098 CREATE RULE y_rule AS ON INSERT TO y WHERE a=0 DO INSTEAD DELETE FROM y;
2100 INSERT INTO y VALUES(0)
2103 ERROR: conditional DO INSTEAD rules are not supported for data-modifying statements in WITH
2104 DROP RULE y_rule ON y;