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'd be an infinite loop, but outside query reads only as much as needed
53 WITH RECURSIVE t(n) AS (
57 SELECT * FROM t LIMIT 10;
73 -- Some examples with a tree
75 -- department structure represented here is as follows:
77 -- ROOT-+->A-+->B-+->C
81 CREATE TEMP TABLE department (
82 id INTEGER PRIMARY KEY, -- department ID
83 parent_department INTEGER REFERENCES department, -- upper department ID
84 name TEXT -- department name
86 NOTICE: CREATE TABLE / PRIMARY KEY will create implicit index "department_pkey" for table "department"
87 INSERT INTO department VALUES (0, NULL, 'ROOT');
88 INSERT INTO department VALUES (1, 0, 'A');
89 INSERT INTO department VALUES (2, 1, 'B');
90 INSERT INTO department VALUES (3, 2, 'C');
91 INSERT INTO department VALUES (4, 2, 'D');
92 INSERT INTO department VALUES (5, 0, 'E');
93 INSERT INTO department VALUES (6, 4, 'F');
94 INSERT INTO department VALUES (7, 5, 'G');
95 -- extract all departments under 'A'. Result should be A, B, C, D and F
96 WITH RECURSIVE subdepartment AS
99 SELECT name as root_name, * FROM department WHERE name = 'A'
102 SELECT sd.root_name, d.* FROM department AS d, subdepartment AS sd
103 WHERE d.parent_department = sd.id
105 SELECT * FROM subdepartment ORDER BY name;
106 root_name | id | parent_department | name
107 -----------+----+-------------------+------
115 -- extract all departments under 'A' with "level" number
116 WITH RECURSIVE subdepartment(level, id, parent_department, name) AS
118 -- non recursive term
119 SELECT 1, * FROM department WHERE name = 'A'
122 SELECT sd.level + 1, d.* FROM department AS d, subdepartment AS sd
123 WHERE d.parent_department = sd.id
125 SELECT * FROM subdepartment ORDER BY name;
126 level | id | parent_department | name
127 -------+----+-------------------+------
135 -- extract all departments under 'A' with "level" number.
136 -- Only shows level 2 or more
137 WITH RECURSIVE subdepartment(level, id, parent_department, name) AS
139 -- non recursive term
140 SELECT 1, * FROM department WHERE name = 'A'
143 SELECT sd.level + 1, d.* FROM department AS d, subdepartment AS sd
144 WHERE d.parent_department = sd.id
146 SELECT * FROM subdepartment WHERE level >= 2 ORDER BY name;
147 level | id | parent_department | name
148 -------+----+-------------------+------
155 -- "RECURSIVE" is ignored if the query has no self-reference
156 WITH RECURSIVE subdepartment AS
158 -- note lack of recursive UNION structure
159 SELECT * FROM department WHERE name = 'A'
161 SELECT * FROM subdepartment ORDER BY name;
162 id | parent_department | name
163 ----+-------------------+------
168 SELECT count(*) FROM (
169 WITH RECURSIVE t(n) AS (
170 SELECT 1 UNION ALL SELECT n + 1 FROM t WHERE n < 500
172 SELECT * FROM t) AS t WHERE n < (
173 SELECT count(*) FROM (
174 WITH RECURSIVE t(n) AS (
175 SELECT 1 UNION ALL SELECT n + 1 FROM t WHERE n < 100
177 SELECT * FROM t WHERE n < 50000
178 ) AS t WHERE n < 100);
184 -- use same CTE twice at different subquery levels
186 SELECT hundred, sum(ten) FROM tenk1 GROUP BY hundred
188 SELECT count(*) FROM q1 WHERE y > (SELECT sum(y)/100 FROM q1 qsub);
195 CREATE TEMPORARY VIEW vsubdepartment AS
196 WITH RECURSIVE subdepartment AS
198 -- non recursive term
199 SELECT * FROM department WHERE name = 'A'
202 SELECT d.* FROM department AS d, subdepartment AS sd
203 WHERE d.parent_department = sd.id
205 SELECT * FROM subdepartment;
206 SELECT * FROM vsubdepartment ORDER BY name;
207 id | parent_department | name
208 ----+-------------------+------
216 -- Check reverse listing
217 SELECT pg_get_viewdef('vsubdepartment'::regclass);
219 ---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
220 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;
223 SELECT pg_get_viewdef('vsubdepartment'::regclass, true);
225 --------------------------------------------------------------------------------------
226 WITH RECURSIVE subdepartment AS (
227 SELECT department.id, department.parent_department, department.name
229 WHERE department.name = 'A'::text
231 SELECT d.id, d.parent_department, d.name
232 FROM department d, subdepartment sd
233 WHERE d.parent_department = sd.id
235 SELECT subdepartment.id, subdepartment.parent_department, subdepartment.name
239 -- recursive term has sub-UNION
240 WITH RECURSIVE t(i,j) AS (
243 SELECT t2.i, t.j+1 FROM
244 (SELECT 2 AS i UNION ALL SELECT 3 AS i) AS t2
245 JOIN t ON (t2.i = t.i+1))
255 -- different tree example
257 CREATE TEMPORARY TABLE tree(
258 id INTEGER PRIMARY KEY,
259 parent_id INTEGER REFERENCES tree(id)
261 NOTICE: CREATE TABLE / PRIMARY KEY will create implicit index "tree_pkey" for table "tree"
263 VALUES (1, NULL), (2, 1), (3,1), (4,2), (5,2), (6,2), (7,3), (8,3),
264 (9,4), (10,4), (11,7), (12,7), (13,7), (14, 9), (15,11), (16,11);
266 -- get all paths from "second level" nodes to leaf nodes
268 WITH RECURSIVE t(id, path) AS (
269 VALUES(1,ARRAY[]::integer[])
271 SELECT tree.id, t.path || tree.id
272 FROM tree JOIN t ON (tree.parent_id = t.id)
274 SELECT t1.*, t2.* FROM t AS t1 JOIN t AS t2 ON
275 (t1.path[1] = t2.path[1] AND
276 array_upper(t1.path,1) = 1 AND
277 array_upper(t2.path,1) > 1)
278 ORDER BY t1.id, t2.id;
279 id | path | id | path
280 ----+------+----+-------------
284 2 | {2} | 9 | {2,4,9}
285 2 | {2} | 10 | {2,4,10}
286 2 | {2} | 14 | {2,4,9,14}
289 3 | {3} | 11 | {3,7,11}
290 3 | {3} | 12 | {3,7,12}
291 3 | {3} | 13 | {3,7,13}
292 3 | {3} | 15 | {3,7,11,15}
293 3 | {3} | 16 | {3,7,11,16}
297 WITH RECURSIVE t(id, path) AS (
298 VALUES(1,ARRAY[]::integer[])
300 SELECT tree.id, t.path || tree.id
301 FROM tree JOIN t ON (tree.parent_id = t.id)
303 SELECT t1.id, count(t2.*) FROM t AS t1 JOIN t AS t2 ON
304 (t1.path[1] = t2.path[1] AND
305 array_upper(t1.path,1) = 1 AND
306 array_upper(t2.path,1) > 1)
316 -- test multiple WITH queries
319 y (id) AS (VALUES (1)),
320 x (id) AS (SELECT * FROM y UNION ALL SELECT id+1 FROM x WHERE id < 5)
331 -- forward reference OK
333 x(id) AS (SELECT * FROM y UNION ALL SELECT id+1 FROM x WHERE id < 5),
334 y(id) AS (values (1))
347 (VALUES (1) UNION ALL SELECT id+1 FROM x WHERE id < 5),
349 (VALUES (1) UNION ALL SELECT id+1 FROM y WHERE id < 10)
350 SELECT y.*, x.* FROM y LEFT JOIN x USING (id);
367 (VALUES (1) UNION ALL SELECT id+1 FROM x WHERE id < 5),
369 (VALUES (1) UNION ALL SELECT id+1 FROM x WHERE id < 10)
370 SELECT y.*, x.* FROM y LEFT JOIN x USING (id);
383 (SELECT 1 UNION ALL SELECT id+1 FROM x WHERE id < 3 ),
385 (SELECT * FROM x UNION ALL SELECT * FROM x),
387 (SELECT * FROM x UNION ALL SELECT id+1 FROM z WHERE id < 10)
422 (SELECT 1 UNION ALL SELECT id+1 FROM x WHERE id < 3 ),
424 (SELECT * FROM x UNION ALL SELECT * FROM x),
426 (SELECT * FROM y UNION ALL SELECT id+1 FROM z WHERE id < 10)
489 -- UNION (should be supported someday)
490 WITH RECURSIVE x(n) AS (SELECT 1 UNION SELECT n+1 FROM x)
492 ERROR: recursive query "x" does not have the form non-recursive-term UNION ALL recursive-term
493 LINE 1: WITH RECURSIVE x(n) AS (SELECT 1 UNION SELECT n+1 FROM x)
496 WITH RECURSIVE x(n) AS (SELECT 1 INTERSECT SELECT n+1 FROM x)
498 ERROR: recursive query "x" does not have the form non-recursive-term UNION ALL recursive-term
499 LINE 1: WITH RECURSIVE x(n) AS (SELECT 1 INTERSECT SELECT n+1 FROM x...
501 WITH RECURSIVE x(n) AS (SELECT 1 INTERSECT ALL SELECT n+1 FROM x)
503 ERROR: recursive query "x" does not have the form non-recursive-term UNION ALL recursive-term
504 LINE 1: WITH RECURSIVE x(n) AS (SELECT 1 INTERSECT ALL SELECT n+1 FR...
507 WITH RECURSIVE x(n) AS (SELECT 1 EXCEPT SELECT n+1 FROM x)
509 ERROR: recursive query "x" does not have the form non-recursive-term UNION ALL recursive-term
510 LINE 1: WITH RECURSIVE x(n) AS (SELECT 1 EXCEPT SELECT n+1 FROM x)
512 WITH RECURSIVE x(n) AS (SELECT 1 EXCEPT ALL SELECT n+1 FROM x)
514 ERROR: recursive query "x" does not have the form non-recursive-term UNION ALL recursive-term
515 LINE 1: WITH RECURSIVE x(n) AS (SELECT 1 EXCEPT ALL SELECT n+1 FROM ...
517 -- no non-recursive term
518 WITH RECURSIVE x(n) AS (SELECT n FROM x)
520 ERROR: recursive query "x" does not have the form non-recursive-term UNION ALL recursive-term
521 LINE 1: WITH RECURSIVE x(n) AS (SELECT n FROM x)
523 -- recursive term in the left hand side (strictly speaking, should allow this)
524 WITH RECURSIVE x(n) AS (SELECT n FROM x UNION ALL SELECT 1)
526 ERROR: recursive reference to query "x" must not appear within its non-recursive term
527 LINE 1: WITH RECURSIVE x(n) AS (SELECT n FROM x UNION ALL SELECT 1)
529 CREATE TEMPORARY TABLE y (a INTEGER);
530 INSERT INTO y SELECT generate_series(1, 10);
532 WITH RECURSIVE x(n) AS (SELECT a FROM y WHERE a = 1
534 SELECT x.n+1 FROM y LEFT JOIN x ON x.n = y.a WHERE n < 10)
536 ERROR: recursive reference to query "x" must not appear within an outer join
537 LINE 3: SELECT x.n+1 FROM y LEFT JOIN x ON x.n = y.a WHERE n < 10)
540 WITH RECURSIVE x(n) AS (SELECT a FROM y WHERE a = 1
542 SELECT x.n+1 FROM x RIGHT JOIN y ON x.n = y.a WHERE n < 10)
544 ERROR: recursive reference to query "x" must not appear within an outer join
545 LINE 3: SELECT x.n+1 FROM x RIGHT JOIN y ON x.n = y.a WHERE n < 10)
548 WITH RECURSIVE x(n) AS (SELECT a FROM y WHERE a = 1
550 SELECT x.n+1 FROM x FULL JOIN y ON x.n = y.a WHERE n < 10)
552 ERROR: recursive reference to query "x" must not appear within an outer join
553 LINE 3: SELECT x.n+1 FROM x FULL JOIN y ON x.n = y.a WHERE n < 10)
556 WITH RECURSIVE x(n) AS (SELECT 1 UNION ALL SELECT n+1 FROM x
557 WHERE n IN (SELECT * FROM x))
559 ERROR: recursive reference to query "x" must not appear within a subquery
560 LINE 2: WHERE n IN (SELECT * FROM x))
562 -- aggregate functions
563 WITH RECURSIVE x(n) AS (SELECT 1 UNION ALL SELECT count(*) FROM x)
565 ERROR: aggregates not allowed in a recursive query's recursive term
566 LINE 1: WITH RECURSIVE x(n) AS (SELECT 1 UNION ALL SELECT count(*) F...
568 WITH RECURSIVE x(n) AS (SELECT 1 UNION ALL SELECT sum(n) FROM x)
570 ERROR: aggregates not allowed in a recursive query's recursive term
571 LINE 1: WITH RECURSIVE x(n) AS (SELECT 1 UNION ALL SELECT sum(n) FRO...
574 WITH RECURSIVE x(n) AS (SELECT 1 UNION ALL SELECT n+1 FROM x ORDER BY 1)
576 ERROR: ORDER BY in a recursive query is not implemented
577 LINE 1: ...VE x(n) AS (SELECT 1 UNION ALL SELECT n+1 FROM x ORDER BY 1)
580 WITH RECURSIVE x(n) AS (SELECT 1 UNION ALL SELECT n+1 FROM x LIMIT 10 OFFSET 1)
582 ERROR: OFFSET in a recursive query is not implemented
583 LINE 1: ... AS (SELECT 1 UNION ALL SELECT n+1 FROM x LIMIT 10 OFFSET 1)
586 WITH RECURSIVE x(n) AS (SELECT 1 UNION ALL SELECT n+1 FROM x FOR UPDATE)
588 ERROR: FOR UPDATE/SHARE in a recursive query is not implemented
589 -- target list has a recursive query name
590 WITH RECURSIVE x(id) AS (values (1)
592 SELECT (SELECT * FROM x) FROM x WHERE id < 5
594 ERROR: recursive reference to query "x" must not appear within a subquery
595 LINE 3: SELECT (SELECT * FROM x) FROM x WHERE id < 5
597 -- mutual recursive query (not implemented)
599 x (id) AS (SELECT 1 UNION ALL SELECT id+1 FROM y WHERE id < 5),
600 y (id) AS (SELECT 1 UNION ALL SELECT id+1 FROM x WHERE id < 5)
602 ERROR: mutual recursion between WITH items is not implemented
603 LINE 2: x (id) AS (SELECT 1 UNION ALL SELECT id+1 FROM y WHERE id ...
605 -- non-linear recursion is not allowed
606 WITH RECURSIVE foo(i) AS
609 (SELECT i+1 FROM foo WHERE i < 10
611 SELECT i+1 FROM foo WHERE i < 5)
613 ERROR: recursive reference to query "foo" must not appear more than once
614 LINE 6: SELECT i+1 FROM foo WHERE i < 5)
616 WITH RECURSIVE foo(i) AS
620 (SELECT i+1 FROM foo WHERE i < 10
622 SELECT i+1 FROM foo WHERE i < 5) AS t
624 ERROR: recursive reference to query "foo" must not appear more than once
625 LINE 7: SELECT i+1 FROM foo WHERE i < 5) AS t
627 WITH RECURSIVE foo(i) AS
630 (SELECT i+1 FROM foo WHERE i < 10
632 SELECT i+1 FROM foo WHERE i < 5)
634 ERROR: recursive reference to query "foo" must not appear within EXCEPT
635 LINE 6: SELECT i+1 FROM foo WHERE i < 5)
637 WITH RECURSIVE foo(i) AS
640 (SELECT i+1 FROM foo WHERE i < 10
642 SELECT i+1 FROM foo WHERE i < 5)
644 ERROR: recursive reference to query "foo" must not appear more than once
645 LINE 6: SELECT i+1 FROM foo WHERE i < 5)
647 -- Wrong type induced from non-recursive term
648 WITH RECURSIVE foo(i) AS
649 (SELECT i FROM (VALUES(1),(2)) t(i)
651 SELECT (i+1)::numeric(10,0) FROM foo WHERE i < 10)
653 ERROR: recursive query "foo" column 1 has type integer in non-recursive term but type numeric overall
654 LINE 2: (SELECT i FROM (VALUES(1),(2)) t(i)
656 HINT: Cast the output of the non-recursive term to the correct type.
657 -- rejects different typmod, too (should we allow this?)
658 WITH RECURSIVE foo(i) AS
659 (SELECT i::numeric(3,0) FROM (VALUES(1),(2)) t(i)
661 SELECT (i+1)::numeric(10,0) FROM foo WHERE i < 10)
663 ERROR: recursive query "foo" column 1 has type numeric(3,0) in non-recursive term but type numeric overall
664 LINE 2: (SELECT i::numeric(3,0) FROM (VALUES(1),(2)) t(i)
666 HINT: Cast the output of the non-recursive term to the correct type.