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 NOTICE: CREATE TABLE / PRIMARY KEY will create implicit index "department_pkey" for table "department"
143 INSERT INTO department VALUES (0, NULL, 'ROOT');
144 INSERT INTO department VALUES (1, 0, 'A');
145 INSERT INTO department VALUES (2, 1, 'B');
146 INSERT INTO department VALUES (3, 2, 'C');
147 INSERT INTO department VALUES (4, 2, 'D');
148 INSERT INTO department VALUES (5, 0, 'E');
149 INSERT INTO department VALUES (6, 4, 'F');
150 INSERT INTO department VALUES (7, 5, 'G');
151 -- extract all departments under 'A'. Result should be A, B, C, D and F
152 WITH RECURSIVE subdepartment AS
154 -- non recursive term
155 SELECT name as root_name, * FROM department WHERE name = 'A'
158 SELECT sd.root_name, d.* FROM department AS d, subdepartment AS sd
159 WHERE d.parent_department = sd.id
161 SELECT * FROM subdepartment ORDER BY name;
162 root_name | id | parent_department | name
163 -----------+----+-------------------+------
171 -- extract all departments under 'A' with "level" number
172 WITH RECURSIVE subdepartment(level, id, parent_department, name) AS
174 -- non recursive term
175 SELECT 1, * FROM department WHERE name = 'A'
178 SELECT sd.level + 1, d.* FROM department AS d, subdepartment AS sd
179 WHERE d.parent_department = sd.id
181 SELECT * FROM subdepartment ORDER BY name;
182 level | id | parent_department | name
183 -------+----+-------------------+------
191 -- extract all departments under 'A' with "level" number.
192 -- Only shows level 2 or more
193 WITH RECURSIVE subdepartment(level, id, parent_department, name) AS
195 -- non recursive term
196 SELECT 1, * FROM department WHERE name = 'A'
199 SELECT sd.level + 1, d.* FROM department AS d, subdepartment AS sd
200 WHERE d.parent_department = sd.id
202 SELECT * FROM subdepartment WHERE level >= 2 ORDER BY name;
203 level | id | parent_department | name
204 -------+----+-------------------+------
211 -- "RECURSIVE" is ignored if the query has no self-reference
212 WITH RECURSIVE subdepartment AS
214 -- note lack of recursive UNION structure
215 SELECT * FROM department WHERE name = 'A'
217 SELECT * FROM subdepartment ORDER BY name;
218 id | parent_department | name
219 ----+-------------------+------
224 SELECT count(*) FROM (
225 WITH RECURSIVE t(n) AS (
226 SELECT 1 UNION ALL SELECT n + 1 FROM t WHERE n < 500
228 SELECT * FROM t) AS t WHERE n < (
229 SELECT count(*) FROM (
230 WITH RECURSIVE t(n) AS (
231 SELECT 1 UNION ALL SELECT n + 1 FROM t WHERE n < 100
233 SELECT * FROM t WHERE n < 50000
234 ) AS t WHERE n < 100);
240 -- use same CTE twice at different subquery levels
242 SELECT hundred, sum(ten) FROM tenk1 GROUP BY hundred
244 SELECT count(*) FROM q1 WHERE y > (SELECT sum(y)/100 FROM q1 qsub);
251 CREATE TEMPORARY VIEW vsubdepartment AS
252 WITH RECURSIVE subdepartment AS
254 -- non recursive term
255 SELECT * FROM department WHERE name = 'A'
258 SELECT d.* FROM department AS d, subdepartment AS sd
259 WHERE d.parent_department = sd.id
261 SELECT * FROM subdepartment;
262 SELECT * FROM vsubdepartment ORDER BY name;
263 id | parent_department | name
264 ----+-------------------+------
272 -- Check reverse listing
273 SELECT pg_get_viewdef('vsubdepartment'::regclass);
275 ---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
276 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;
279 SELECT pg_get_viewdef('vsubdepartment'::regclass, true);
281 --------------------------------------------------------------------------------------
282 WITH RECURSIVE subdepartment AS ( +
283 SELECT department.id, department.parent_department, department.name+
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 -- corner case in which sub-WITH gets initialized first
296 with recursive q as (
297 select * from department
299 (with x as (select * from q)
302 select * from q limit 24;
303 id | parent_department | name
304 ----+-------------------+------
331 with recursive q as (
332 select * from department
334 (with recursive x as (
335 select * from department
337 (select * from q union all select * from x)
341 select * from q limit 32;
342 id | parent_department | name
343 ----+-------------------+------
378 -- recursive term has sub-UNION
379 WITH RECURSIVE t(i,j) AS (
382 SELECT t2.i, t.j+1 FROM
383 (SELECT 2 AS i UNION ALL SELECT 3 AS i) AS t2
384 JOIN t ON (t2.i = t.i+1))
394 -- different tree example
396 CREATE TEMPORARY TABLE tree(
397 id INTEGER PRIMARY KEY,
398 parent_id INTEGER REFERENCES tree(id)
400 NOTICE: CREATE TABLE / PRIMARY KEY will create implicit index "tree_pkey" for table "tree"
402 VALUES (1, NULL), (2, 1), (3,1), (4,2), (5,2), (6,2), (7,3), (8,3),
403 (9,4), (10,4), (11,7), (12,7), (13,7), (14, 9), (15,11), (16,11);
405 -- get all paths from "second level" nodes to leaf nodes
407 WITH RECURSIVE t(id, path) AS (
408 VALUES(1,ARRAY[]::integer[])
410 SELECT tree.id, t.path || tree.id
411 FROM tree JOIN t ON (tree.parent_id = t.id)
413 SELECT t1.*, t2.* FROM t AS t1 JOIN t AS t2 ON
414 (t1.path[1] = t2.path[1] AND
415 array_upper(t1.path,1) = 1 AND
416 array_upper(t2.path,1) > 1)
417 ORDER BY t1.id, t2.id;
418 id | path | id | path
419 ----+------+----+-------------
423 2 | {2} | 9 | {2,4,9}
424 2 | {2} | 10 | {2,4,10}
425 2 | {2} | 14 | {2,4,9,14}
428 3 | {3} | 11 | {3,7,11}
429 3 | {3} | 12 | {3,7,12}
430 3 | {3} | 13 | {3,7,13}
431 3 | {3} | 15 | {3,7,11,15}
432 3 | {3} | 16 | {3,7,11,16}
436 WITH RECURSIVE t(id, path) AS (
437 VALUES(1,ARRAY[]::integer[])
439 SELECT tree.id, t.path || tree.id
440 FROM tree JOIN t ON (tree.parent_id = t.id)
442 SELECT t1.id, count(t2.*) FROM t AS t1 JOIN t AS t2 ON
443 (t1.path[1] = t2.path[1] AND
444 array_upper(t1.path,1) = 1 AND
445 array_upper(t2.path,1) > 1)
454 -- this variant tickled a whole-row-variable bug in 8.4devel
455 WITH RECURSIVE t(id, path) AS (
456 VALUES(1,ARRAY[]::integer[])
458 SELECT tree.id, t.path || tree.id
459 FROM tree JOIN t ON (tree.parent_id = t.id)
461 SELECT t1.id, t2.path, t2 FROM t AS t1 JOIN t AS t2 ON
464 ----+-------------+--------------------
468 4 | {2,4} | (4,"{2,4}")
469 5 | {2,5} | (5,"{2,5}")
470 6 | {2,6} | (6,"{2,6}")
471 7 | {3,7} | (7,"{3,7}")
472 8 | {3,8} | (8,"{3,8}")
473 9 | {2,4,9} | (9,"{2,4,9}")
474 10 | {2,4,10} | (10,"{2,4,10}")
475 11 | {3,7,11} | (11,"{3,7,11}")
476 12 | {3,7,12} | (12,"{3,7,12}")
477 13 | {3,7,13} | (13,"{3,7,13}")
478 14 | {2,4,9,14} | (14,"{2,4,9,14}")
479 15 | {3,7,11,15} | (15,"{3,7,11,15}")
480 16 | {3,7,11,16} | (16,"{3,7,11,16}")
484 -- test cycle detection
486 create temp table graph( f int, t int, label text );
487 insert into graph values
488 (1, 2, 'arc 1 -> 2'),
489 (1, 3, 'arc 1 -> 3'),
490 (2, 3, 'arc 2 -> 3'),
491 (1, 4, 'arc 1 -> 4'),
492 (4, 5, 'arc 4 -> 5'),
493 (5, 1, 'arc 5 -> 1');
494 with recursive search_graph(f, t, label, path, cycle) as (
495 select *, array[row(g.f, g.t)], false from graph g
497 select g.*, path || row(g.f, g.t), row(g.f, g.t) = any(path)
498 from graph g, search_graph sg
499 where g.f = sg.t and not cycle
501 select * from search_graph;
502 f | t | label | path | cycle
503 ---+---+------------+-------------------------------------------+-------
504 1 | 2 | arc 1 -> 2 | {"(1,2)"} | f
505 1 | 3 | arc 1 -> 3 | {"(1,3)"} | f
506 2 | 3 | arc 2 -> 3 | {"(2,3)"} | f
507 1 | 4 | arc 1 -> 4 | {"(1,4)"} | f
508 4 | 5 | arc 4 -> 5 | {"(4,5)"} | f
509 5 | 1 | arc 5 -> 1 | {"(5,1)"} | f
510 1 | 2 | arc 1 -> 2 | {"(5,1)","(1,2)"} | f
511 1 | 3 | arc 1 -> 3 | {"(5,1)","(1,3)"} | f
512 1 | 4 | arc 1 -> 4 | {"(5,1)","(1,4)"} | f
513 2 | 3 | arc 2 -> 3 | {"(1,2)","(2,3)"} | f
514 4 | 5 | arc 4 -> 5 | {"(1,4)","(4,5)"} | f
515 5 | 1 | arc 5 -> 1 | {"(4,5)","(5,1)"} | f
516 1 | 2 | arc 1 -> 2 | {"(4,5)","(5,1)","(1,2)"} | f
517 1 | 3 | arc 1 -> 3 | {"(4,5)","(5,1)","(1,3)"} | f
518 1 | 4 | arc 1 -> 4 | {"(4,5)","(5,1)","(1,4)"} | f
519 2 | 3 | arc 2 -> 3 | {"(5,1)","(1,2)","(2,3)"} | f
520 4 | 5 | arc 4 -> 5 | {"(5,1)","(1,4)","(4,5)"} | f
521 5 | 1 | arc 5 -> 1 | {"(1,4)","(4,5)","(5,1)"} | f
522 1 | 2 | arc 1 -> 2 | {"(1,4)","(4,5)","(5,1)","(1,2)"} | f
523 1 | 3 | arc 1 -> 3 | {"(1,4)","(4,5)","(5,1)","(1,3)"} | f
524 1 | 4 | arc 1 -> 4 | {"(1,4)","(4,5)","(5,1)","(1,4)"} | t
525 2 | 3 | arc 2 -> 3 | {"(4,5)","(5,1)","(1,2)","(2,3)"} | f
526 4 | 5 | arc 4 -> 5 | {"(4,5)","(5,1)","(1,4)","(4,5)"} | t
527 5 | 1 | arc 5 -> 1 | {"(5,1)","(1,4)","(4,5)","(5,1)"} | t
528 2 | 3 | arc 2 -> 3 | {"(1,4)","(4,5)","(5,1)","(1,2)","(2,3)"} | f
531 -- ordering by the path column has same effect as SEARCH DEPTH FIRST
532 with recursive search_graph(f, t, label, path, cycle) as (
533 select *, array[row(g.f, g.t)], false from graph g
535 select g.*, path || row(g.f, g.t), row(g.f, g.t) = any(path)
536 from graph g, search_graph sg
537 where g.f = sg.t and not cycle
539 select * from search_graph order by path;
540 f | t | label | path | cycle
541 ---+---+------------+-------------------------------------------+-------
542 1 | 2 | arc 1 -> 2 | {"(1,2)"} | f
543 2 | 3 | arc 2 -> 3 | {"(1,2)","(2,3)"} | f
544 1 | 3 | arc 1 -> 3 | {"(1,3)"} | f
545 1 | 4 | arc 1 -> 4 | {"(1,4)"} | f
546 4 | 5 | arc 4 -> 5 | {"(1,4)","(4,5)"} | f
547 5 | 1 | arc 5 -> 1 | {"(1,4)","(4,5)","(5,1)"} | f
548 1 | 2 | arc 1 -> 2 | {"(1,4)","(4,5)","(5,1)","(1,2)"} | f
549 2 | 3 | arc 2 -> 3 | {"(1,4)","(4,5)","(5,1)","(1,2)","(2,3)"} | f
550 1 | 3 | arc 1 -> 3 | {"(1,4)","(4,5)","(5,1)","(1,3)"} | f
551 1 | 4 | arc 1 -> 4 | {"(1,4)","(4,5)","(5,1)","(1,4)"} | t
552 2 | 3 | arc 2 -> 3 | {"(2,3)"} | f
553 4 | 5 | arc 4 -> 5 | {"(4,5)"} | f
554 5 | 1 | arc 5 -> 1 | {"(4,5)","(5,1)"} | f
555 1 | 2 | arc 1 -> 2 | {"(4,5)","(5,1)","(1,2)"} | f
556 2 | 3 | arc 2 -> 3 | {"(4,5)","(5,1)","(1,2)","(2,3)"} | f
557 1 | 3 | arc 1 -> 3 | {"(4,5)","(5,1)","(1,3)"} | f
558 1 | 4 | arc 1 -> 4 | {"(4,5)","(5,1)","(1,4)"} | f
559 4 | 5 | arc 4 -> 5 | {"(4,5)","(5,1)","(1,4)","(4,5)"} | t
560 5 | 1 | arc 5 -> 1 | {"(5,1)"} | f
561 1 | 2 | arc 1 -> 2 | {"(5,1)","(1,2)"} | f
562 2 | 3 | arc 2 -> 3 | {"(5,1)","(1,2)","(2,3)"} | f
563 1 | 3 | arc 1 -> 3 | {"(5,1)","(1,3)"} | f
564 1 | 4 | arc 1 -> 4 | {"(5,1)","(1,4)"} | f
565 4 | 5 | arc 4 -> 5 | {"(5,1)","(1,4)","(4,5)"} | f
566 5 | 1 | arc 5 -> 1 | {"(5,1)","(1,4)","(4,5)","(5,1)"} | t
570 -- test multiple WITH queries
573 y (id) AS (VALUES (1)),
574 x (id) AS (SELECT * FROM y UNION ALL SELECT id+1 FROM x WHERE id < 5)
585 -- forward reference OK
587 x(id) AS (SELECT * FROM y UNION ALL SELECT id+1 FROM x WHERE id < 5),
588 y(id) AS (values (1))
601 (VALUES (1) UNION ALL SELECT id+1 FROM x WHERE id < 5),
603 (VALUES (1) UNION ALL SELECT id+1 FROM y WHERE id < 10)
604 SELECT y.*, x.* FROM y LEFT JOIN x USING (id);
621 (VALUES (1) UNION ALL SELECT id+1 FROM x WHERE id < 5),
623 (VALUES (1) UNION ALL SELECT id+1 FROM x WHERE id < 10)
624 SELECT y.*, x.* FROM y LEFT JOIN x USING (id);
637 (SELECT 1 UNION ALL SELECT id+1 FROM x WHERE id < 3 ),
639 (SELECT * FROM x UNION ALL SELECT * FROM x),
641 (SELECT * FROM x UNION ALL SELECT id+1 FROM z WHERE id < 10)
676 (SELECT 1 UNION ALL SELECT id+1 FROM x WHERE id < 3 ),
678 (SELECT * FROM x UNION ALL SELECT * FROM x),
680 (SELECT * FROM y UNION ALL SELECT id+1 FROM z WHERE id < 10)
741 -- Test WITH attached to a DML statement
743 CREATE TEMPORARY TABLE y (a INTEGER);
744 INSERT INTO y SELECT generate_series(1, 10);
749 SELECT a+20 FROM t RETURNING *;
792 UPDATE y SET a = y.a-10 FROM t WHERE y.a > 20 AND t.a = y.a RETURNING y.a;
832 WITH RECURSIVE t(a) AS (
835 SELECT a+1 FROM t WHERE a < 50
837 DELETE FROM y USING t WHERE t.a = y.a RETURNING y.a;
872 WITH RECURSIVE x(n) AS (SELECT 1 INTERSECT SELECT n+1 FROM x)
874 ERROR: recursive query "x" does not have the form non-recursive-term UNION [ALL] recursive-term
875 LINE 1: WITH RECURSIVE x(n) AS (SELECT 1 INTERSECT SELECT n+1 FROM x...
877 WITH RECURSIVE x(n) AS (SELECT 1 INTERSECT ALL SELECT n+1 FROM x)
879 ERROR: recursive query "x" does not have the form non-recursive-term UNION [ALL] recursive-term
880 LINE 1: WITH RECURSIVE x(n) AS (SELECT 1 INTERSECT ALL SELECT n+1 FR...
883 WITH RECURSIVE x(n) AS (SELECT 1 EXCEPT SELECT n+1 FROM x)
885 ERROR: recursive query "x" does not have the form non-recursive-term UNION [ALL] recursive-term
886 LINE 1: WITH RECURSIVE x(n) AS (SELECT 1 EXCEPT SELECT n+1 FROM x)
888 WITH RECURSIVE x(n) AS (SELECT 1 EXCEPT ALL SELECT n+1 FROM x)
890 ERROR: recursive query "x" does not have the form non-recursive-term UNION [ALL] recursive-term
891 LINE 1: WITH RECURSIVE x(n) AS (SELECT 1 EXCEPT ALL SELECT n+1 FROM ...
893 -- no non-recursive term
894 WITH RECURSIVE x(n) AS (SELECT n FROM x)
896 ERROR: recursive query "x" does not have the form non-recursive-term UNION [ALL] recursive-term
897 LINE 1: WITH RECURSIVE x(n) AS (SELECT n FROM x)
899 -- recursive term in the left hand side (strictly speaking, should allow this)
900 WITH RECURSIVE x(n) AS (SELECT n FROM x UNION ALL SELECT 1)
902 ERROR: recursive reference to query "x" must not appear within its non-recursive term
903 LINE 1: WITH RECURSIVE x(n) AS (SELECT n FROM x UNION ALL SELECT 1)
905 CREATE TEMPORARY TABLE y (a INTEGER);
906 INSERT INTO y SELECT generate_series(1, 10);
908 WITH RECURSIVE x(n) AS (SELECT a FROM y WHERE a = 1
910 SELECT x.n+1 FROM y LEFT JOIN x ON x.n = y.a WHERE n < 10)
912 ERROR: recursive reference to query "x" must not appear within an outer join
913 LINE 3: SELECT x.n+1 FROM y LEFT JOIN x ON x.n = y.a WHERE n < 10)
916 WITH RECURSIVE x(n) AS (SELECT a FROM y WHERE a = 1
918 SELECT x.n+1 FROM x RIGHT JOIN y ON x.n = y.a WHERE n < 10)
920 ERROR: recursive reference to query "x" must not appear within an outer join
921 LINE 3: SELECT x.n+1 FROM x RIGHT JOIN y ON x.n = y.a WHERE n < 10)
924 WITH RECURSIVE x(n) AS (SELECT a FROM y WHERE a = 1
926 SELECT x.n+1 FROM x FULL JOIN y ON x.n = y.a WHERE n < 10)
928 ERROR: recursive reference to query "x" must not appear within an outer join
929 LINE 3: SELECT x.n+1 FROM x FULL JOIN y ON x.n = y.a WHERE n < 10)
932 WITH RECURSIVE x(n) AS (SELECT 1 UNION ALL SELECT n+1 FROM x
933 WHERE n IN (SELECT * FROM x))
935 ERROR: recursive reference to query "x" must not appear within a subquery
936 LINE 2: WHERE n IN (SELECT * FROM x))
938 -- aggregate functions
939 WITH RECURSIVE x(n) AS (SELECT 1 UNION ALL SELECT count(*) FROM x)
941 ERROR: aggregate functions not allowed in a recursive query's recursive term
942 LINE 1: WITH RECURSIVE x(n) AS (SELECT 1 UNION ALL SELECT count(*) F...
944 WITH RECURSIVE x(n) AS (SELECT 1 UNION ALL SELECT sum(n) FROM x)
946 ERROR: aggregate functions not allowed in a recursive query's recursive term
947 LINE 1: WITH RECURSIVE x(n) AS (SELECT 1 UNION ALL SELECT sum(n) FRO...
950 WITH RECURSIVE x(n) AS (SELECT 1 UNION ALL SELECT n+1 FROM x ORDER BY 1)
952 ERROR: ORDER BY in a recursive query is not implemented
953 LINE 1: ...VE x(n) AS (SELECT 1 UNION ALL SELECT n+1 FROM x ORDER BY 1)
956 WITH RECURSIVE x(n) AS (SELECT 1 UNION ALL SELECT n+1 FROM x LIMIT 10 OFFSET 1)
958 ERROR: OFFSET in a recursive query is not implemented
959 LINE 1: ... AS (SELECT 1 UNION ALL SELECT n+1 FROM x LIMIT 10 OFFSET 1)
962 WITH RECURSIVE x(n) AS (SELECT 1 UNION ALL SELECT n+1 FROM x FOR UPDATE)
964 ERROR: FOR UPDATE/SHARE in a recursive query is not implemented
965 -- target list has a recursive query name
966 WITH RECURSIVE x(id) AS (values (1)
968 SELECT (SELECT * FROM x) FROM x WHERE id < 5
970 ERROR: recursive reference to query "x" must not appear within a subquery
971 LINE 3: SELECT (SELECT * FROM x) FROM x WHERE id < 5
973 -- mutual recursive query (not implemented)
975 x (id) AS (SELECT 1 UNION ALL SELECT id+1 FROM y WHERE id < 5),
976 y (id) AS (SELECT 1 UNION ALL SELECT id+1 FROM x WHERE id < 5)
978 ERROR: mutual recursion between WITH items is not implemented
979 LINE 2: x (id) AS (SELECT 1 UNION ALL SELECT id+1 FROM y WHERE id ...
981 -- non-linear recursion is not allowed
982 WITH RECURSIVE foo(i) AS
985 (SELECT i+1 FROM foo WHERE i < 10
987 SELECT i+1 FROM foo WHERE i < 5)
989 ERROR: recursive reference to query "foo" must not appear more than once
990 LINE 6: SELECT i+1 FROM foo WHERE i < 5)
992 WITH RECURSIVE foo(i) AS
996 (SELECT i+1 FROM foo WHERE i < 10
998 SELECT i+1 FROM foo WHERE i < 5) AS t
1000 ERROR: recursive reference to query "foo" must not appear more than once
1001 LINE 7: SELECT i+1 FROM foo WHERE i < 5) AS t
1003 WITH RECURSIVE foo(i) AS
1006 (SELECT i+1 FROM foo WHERE i < 10
1008 SELECT i+1 FROM foo WHERE i < 5)
1009 ) SELECT * FROM foo;
1010 ERROR: recursive reference to query "foo" must not appear within EXCEPT
1011 LINE 6: SELECT i+1 FROM foo WHERE i < 5)
1013 WITH RECURSIVE foo(i) AS
1016 (SELECT i+1 FROM foo WHERE i < 10
1018 SELECT i+1 FROM foo WHERE i < 5)
1019 ) SELECT * FROM foo;
1020 ERROR: recursive reference to query "foo" must not appear more than once
1021 LINE 6: SELECT i+1 FROM foo WHERE i < 5)
1023 -- Wrong type induced from non-recursive term
1024 WITH RECURSIVE foo(i) AS
1025 (SELECT i FROM (VALUES(1),(2)) t(i)
1027 SELECT (i+1)::numeric(10,0) FROM foo WHERE i < 10)
1029 ERROR: recursive query "foo" column 1 has type integer in non-recursive term but type numeric overall
1030 LINE 2: (SELECT i FROM (VALUES(1),(2)) t(i)
1032 HINT: Cast the output of the non-recursive term to the correct type.
1033 -- rejects different typmod, too (should we allow this?)
1034 WITH RECURSIVE foo(i) AS
1035 (SELECT i::numeric(3,0) FROM (VALUES(1),(2)) t(i)
1037 SELECT (i+1)::numeric(10,0) FROM foo WHERE i < 10)
1039 ERROR: recursive query "foo" column 1 has type numeric(3,0) in non-recursive term but type numeric overall
1040 LINE 2: (SELECT i::numeric(3,0) FROM (VALUES(1),(2)) t(i)
1042 HINT: Cast the output of the non-recursive term to the correct type.
1043 -- disallow OLD/NEW reference in CTE
1044 CREATE TEMPORARY TABLE x (n integer);
1045 CREATE RULE r2 AS ON UPDATE TO x DO INSTEAD
1046 WITH t AS (SELECT OLD.*) UPDATE y SET a = t.n FROM t;
1047 ERROR: cannot refer to OLD within WITH query
1049 -- test for bug #4902
1051 with cte(foo) as ( values(42) ) values((select foo from cte));
1057 with cte(foo) as ( select 42 ) select * from ((select foo from cte)) q;
1063 -- test CTE referencing an outer-level variable (to see that changed-parameter
1064 -- signaling still works properly after fixing this bug)
1065 select ( with cte(foo) as ( values(f1) )
1066 select (select foo from cte) )
1077 select ( with cte(foo) as ( values(f1) )
1078 values((select foo from cte)) )
1090 -- test for nested-recursive-WITH bug
1092 WITH RECURSIVE t(j) AS (
1093 WITH RECURSIVE s(i) AS (
1096 SELECT i+1 FROM s WHERE i < 10
1100 SELECT j+1 FROM t WHERE j < 10