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 -- recursive term has sub-UNION
296 WITH RECURSIVE t(i,j) AS (
299 SELECT t2.i, t.j+1 FROM
300 (SELECT 2 AS i UNION ALL SELECT 3 AS i) AS t2
301 JOIN t ON (t2.i = t.i+1))
311 -- different tree example
313 CREATE TEMPORARY TABLE tree(
314 id INTEGER PRIMARY KEY,
315 parent_id INTEGER REFERENCES tree(id)
317 NOTICE: CREATE TABLE / PRIMARY KEY will create implicit index "tree_pkey" for table "tree"
319 VALUES (1, NULL), (2, 1), (3,1), (4,2), (5,2), (6,2), (7,3), (8,3),
320 (9,4), (10,4), (11,7), (12,7), (13,7), (14, 9), (15,11), (16,11);
322 -- get all paths from "second level" nodes to leaf nodes
324 WITH RECURSIVE t(id, path) AS (
325 VALUES(1,ARRAY[]::integer[])
327 SELECT tree.id, t.path || tree.id
328 FROM tree JOIN t ON (tree.parent_id = t.id)
330 SELECT t1.*, t2.* FROM t AS t1 JOIN t AS t2 ON
331 (t1.path[1] = t2.path[1] AND
332 array_upper(t1.path,1) = 1 AND
333 array_upper(t2.path,1) > 1)
334 ORDER BY t1.id, t2.id;
335 id | path | id | path
336 ----+------+----+-------------
340 2 | {2} | 9 | {2,4,9}
341 2 | {2} | 10 | {2,4,10}
342 2 | {2} | 14 | {2,4,9,14}
345 3 | {3} | 11 | {3,7,11}
346 3 | {3} | 12 | {3,7,12}
347 3 | {3} | 13 | {3,7,13}
348 3 | {3} | 15 | {3,7,11,15}
349 3 | {3} | 16 | {3,7,11,16}
353 WITH RECURSIVE t(id, path) AS (
354 VALUES(1,ARRAY[]::integer[])
356 SELECT tree.id, t.path || tree.id
357 FROM tree JOIN t ON (tree.parent_id = t.id)
359 SELECT t1.id, count(t2.*) FROM t AS t1 JOIN t AS t2 ON
360 (t1.path[1] = t2.path[1] AND
361 array_upper(t1.path,1) = 1 AND
362 array_upper(t2.path,1) > 1)
372 -- test multiple WITH queries
375 y (id) AS (VALUES (1)),
376 x (id) AS (SELECT * FROM y UNION ALL SELECT id+1 FROM x WHERE id < 5)
387 -- forward reference OK
389 x(id) AS (SELECT * FROM y UNION ALL SELECT id+1 FROM x WHERE id < 5),
390 y(id) AS (values (1))
403 (VALUES (1) UNION ALL SELECT id+1 FROM x WHERE id < 5),
405 (VALUES (1) UNION ALL SELECT id+1 FROM y WHERE id < 10)
406 SELECT y.*, x.* FROM y LEFT JOIN x USING (id);
423 (VALUES (1) UNION ALL SELECT id+1 FROM x WHERE id < 5),
425 (VALUES (1) UNION ALL SELECT id+1 FROM x WHERE id < 10)
426 SELECT y.*, x.* FROM y LEFT JOIN x USING (id);
439 (SELECT 1 UNION ALL SELECT id+1 FROM x WHERE id < 3 ),
441 (SELECT * FROM x UNION ALL SELECT * FROM x),
443 (SELECT * FROM x UNION ALL SELECT id+1 FROM z WHERE id < 10)
478 (SELECT 1 UNION ALL SELECT id+1 FROM x WHERE id < 3 ),
480 (SELECT * FROM x UNION ALL SELECT * FROM x),
482 (SELECT * FROM y UNION ALL SELECT id+1 FROM z WHERE id < 10)
546 WITH RECURSIVE x(n) AS (SELECT 1 INTERSECT SELECT n+1 FROM x)
548 ERROR: recursive query "x" does not have the form non-recursive-term UNION [ALL] recursive-term
549 LINE 1: WITH RECURSIVE x(n) AS (SELECT 1 INTERSECT SELECT n+1 FROM x...
551 WITH RECURSIVE x(n) AS (SELECT 1 INTERSECT ALL SELECT n+1 FROM x)
553 ERROR: recursive query "x" does not have the form non-recursive-term UNION [ALL] recursive-term
554 LINE 1: WITH RECURSIVE x(n) AS (SELECT 1 INTERSECT ALL SELECT n+1 FR...
557 WITH RECURSIVE x(n) AS (SELECT 1 EXCEPT SELECT n+1 FROM x)
559 ERROR: recursive query "x" does not have the form non-recursive-term UNION [ALL] recursive-term
560 LINE 1: WITH RECURSIVE x(n) AS (SELECT 1 EXCEPT SELECT n+1 FROM x)
562 WITH RECURSIVE x(n) AS (SELECT 1 EXCEPT ALL SELECT n+1 FROM x)
564 ERROR: recursive query "x" does not have the form non-recursive-term UNION [ALL] recursive-term
565 LINE 1: WITH RECURSIVE x(n) AS (SELECT 1 EXCEPT ALL SELECT n+1 FROM ...
567 -- no non-recursive term
568 WITH RECURSIVE x(n) AS (SELECT n FROM x)
570 ERROR: recursive query "x" does not have the form non-recursive-term UNION [ALL] recursive-term
571 LINE 1: WITH RECURSIVE x(n) AS (SELECT n FROM x)
573 -- recursive term in the left hand side (strictly speaking, should allow this)
574 WITH RECURSIVE x(n) AS (SELECT n FROM x UNION ALL SELECT 1)
576 ERROR: recursive reference to query "x" must not appear within its non-recursive term
577 LINE 1: WITH RECURSIVE x(n) AS (SELECT n FROM x UNION ALL SELECT 1)
579 CREATE TEMPORARY TABLE y (a INTEGER);
580 INSERT INTO y SELECT generate_series(1, 10);
582 WITH RECURSIVE x(n) AS (SELECT a FROM y WHERE a = 1
584 SELECT x.n+1 FROM y LEFT JOIN x ON x.n = y.a WHERE n < 10)
586 ERROR: recursive reference to query "x" must not appear within an outer join
587 LINE 3: SELECT x.n+1 FROM y LEFT JOIN x ON x.n = y.a WHERE n < 10)
590 WITH RECURSIVE x(n) AS (SELECT a FROM y WHERE a = 1
592 SELECT x.n+1 FROM x RIGHT JOIN y ON x.n = y.a WHERE n < 10)
594 ERROR: recursive reference to query "x" must not appear within an outer join
595 LINE 3: SELECT x.n+1 FROM x RIGHT JOIN y ON x.n = y.a WHERE n < 10)
598 WITH RECURSIVE x(n) AS (SELECT a FROM y WHERE a = 1
600 SELECT x.n+1 FROM x FULL JOIN y ON x.n = y.a WHERE n < 10)
602 ERROR: recursive reference to query "x" must not appear within an outer join
603 LINE 3: SELECT x.n+1 FROM x FULL JOIN y ON x.n = y.a WHERE n < 10)
606 WITH RECURSIVE x(n) AS (SELECT 1 UNION ALL SELECT n+1 FROM x
607 WHERE n IN (SELECT * FROM x))
609 ERROR: recursive reference to query "x" must not appear within a subquery
610 LINE 2: WHERE n IN (SELECT * FROM x))
612 -- aggregate functions
613 WITH RECURSIVE x(n) AS (SELECT 1 UNION ALL SELECT count(*) FROM x)
615 ERROR: aggregates not allowed in a recursive query's recursive term
616 LINE 1: WITH RECURSIVE x(n) AS (SELECT 1 UNION ALL SELECT count(*) F...
618 WITH RECURSIVE x(n) AS (SELECT 1 UNION ALL SELECT sum(n) FROM x)
620 ERROR: aggregates not allowed in a recursive query's recursive term
621 LINE 1: WITH RECURSIVE x(n) AS (SELECT 1 UNION ALL SELECT sum(n) FRO...
624 WITH RECURSIVE x(n) AS (SELECT 1 UNION ALL SELECT n+1 FROM x ORDER BY 1)
626 ERROR: ORDER BY in a recursive query is not implemented
627 LINE 1: ...VE x(n) AS (SELECT 1 UNION ALL SELECT n+1 FROM x ORDER BY 1)
630 WITH RECURSIVE x(n) AS (SELECT 1 UNION ALL SELECT n+1 FROM x LIMIT 10 OFFSET 1)
632 ERROR: OFFSET in a recursive query is not implemented
633 LINE 1: ... AS (SELECT 1 UNION ALL SELECT n+1 FROM x LIMIT 10 OFFSET 1)
636 WITH RECURSIVE x(n) AS (SELECT 1 UNION ALL SELECT n+1 FROM x FOR UPDATE)
638 ERROR: FOR UPDATE/SHARE in a recursive query is not implemented
639 -- target list has a recursive query name
640 WITH RECURSIVE x(id) AS (values (1)
642 SELECT (SELECT * FROM x) FROM x WHERE id < 5
644 ERROR: recursive reference to query "x" must not appear within a subquery
645 LINE 3: SELECT (SELECT * FROM x) FROM x WHERE id < 5
647 -- mutual recursive query (not implemented)
649 x (id) AS (SELECT 1 UNION ALL SELECT id+1 FROM y WHERE id < 5),
650 y (id) AS (SELECT 1 UNION ALL SELECT id+1 FROM x WHERE id < 5)
652 ERROR: mutual recursion between WITH items is not implemented
653 LINE 2: x (id) AS (SELECT 1 UNION ALL SELECT id+1 FROM y WHERE id ...
655 -- non-linear recursion is not allowed
656 WITH RECURSIVE foo(i) AS
659 (SELECT i+1 FROM foo WHERE i < 10
661 SELECT i+1 FROM foo WHERE i < 5)
663 ERROR: recursive reference to query "foo" must not appear more than once
664 LINE 6: SELECT i+1 FROM foo WHERE i < 5)
666 WITH RECURSIVE foo(i) AS
670 (SELECT i+1 FROM foo WHERE i < 10
672 SELECT i+1 FROM foo WHERE i < 5) AS t
674 ERROR: recursive reference to query "foo" must not appear more than once
675 LINE 7: SELECT i+1 FROM foo WHERE i < 5) AS t
677 WITH RECURSIVE foo(i) AS
680 (SELECT i+1 FROM foo WHERE i < 10
682 SELECT i+1 FROM foo WHERE i < 5)
684 ERROR: recursive reference to query "foo" must not appear within EXCEPT
685 LINE 6: SELECT i+1 FROM foo WHERE i < 5)
687 WITH RECURSIVE foo(i) AS
690 (SELECT i+1 FROM foo WHERE i < 10
692 SELECT i+1 FROM foo WHERE i < 5)
694 ERROR: recursive reference to query "foo" must not appear more than once
695 LINE 6: SELECT i+1 FROM foo WHERE i < 5)
697 -- Wrong type induced from non-recursive term
698 WITH RECURSIVE foo(i) AS
699 (SELECT i FROM (VALUES(1),(2)) t(i)
701 SELECT (i+1)::numeric(10,0) FROM foo WHERE i < 10)
703 ERROR: recursive query "foo" column 1 has type integer in non-recursive term but type numeric overall
704 LINE 2: (SELECT i FROM (VALUES(1),(2)) t(i)
706 HINT: Cast the output of the non-recursive term to the correct type.
707 -- rejects different typmod, too (should we allow this?)
708 WITH RECURSIVE foo(i) AS
709 (SELECT i::numeric(3,0) FROM (VALUES(1),(2)) t(i)
711 SELECT (i+1)::numeric(10,0) FROM foo WHERE i < 10)
713 ERROR: recursive query "foo" column 1 has type numeric(3,0) in non-recursive term but type numeric overall
714 LINE 2: (SELECT i::numeric(3,0) FROM (VALUES(1),(2)) t(i)
716 HINT: Cast the output of the non-recursive term to the correct type.