| blob | 13bbb9032545f614402e86d47b055123b71017c2 |
1 --
2 -- Tests for common table expressions (WITH query, ... SELECT ...)
3 --
4 -- Basic WITH
5 WITH q1(x,y) AS (SELECT 1,2)
6 SELECT * FROM q1, q1 AS q2;
7 x | y | x | y
8 ---+---+---+---
9 1 | 2 | 1 | 2
10 (1 row)
12 -- Multiple uses are evaluated only once
13 SELECT count(*) FROM (
14 WITH q1(x) AS (SELECT random() FROM generate_series(1, 5))
15 SELECT * FROM q1
16 UNION
17 SELECT * FROM q1
18 ) ss;
19 count
20 -------
21 5
22 (1 row)
24 -- WITH RECURSIVE
25 -- sum of 1..100
26 WITH RECURSIVE t(n) AS (
27 VALUES (1)
28 UNION ALL
29 SELECT n+1 FROM t WHERE n < 100
30 )
31 SELECT sum(n) FROM t;
32 sum
33 ------
34 5050
35 (1 row)
37 WITH RECURSIVE t(n) AS (
38 SELECT (VALUES(1))
39 UNION ALL
40 SELECT n+1 FROM t WHERE n < 5
41 )
42 SELECT * FROM t;
43 n
44 ---
45 1
46 2
47 3
48 4
49 5
50 (5 rows)
52 -- This is an infinite loop with UNION ALL, but not with UNION
53 WITH RECURSIVE t(n) AS (
54 SELECT 1
55 UNION
56 SELECT 10-n FROM t)
57 SELECT * FROM t;
58 n
59 ---
60 1
61 9
62 (2 rows)
64 -- This'd be an infinite loop, but outside query reads only as much as needed
65 WITH RECURSIVE t(n) AS (
66 VALUES (1)
67 UNION ALL
68 SELECT n+1 FROM t)
69 SELECT * FROM t LIMIT 10;
70 n
71 ----
72 1
73 2
74 3
75 4
76 5
77 6
78 7
79 8
80 9
81 10
82 (10 rows)
84 -- UNION case should have same property
85 WITH RECURSIVE t(n) AS (
86 SELECT 1
87 UNION
88 SELECT n+1 FROM t)
89 SELECT * FROM t LIMIT 10;
90 n
91 ----
92 1
93 2
94 3
95 4
96 5
97 6
98 7
99 8
100 9
101 10
102 (10 rows)
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;
107 x | is_unknown
108 -----+------------
109 foo | t
110 (1 row)
112 WITH RECURSIVE t(n) AS (
113 SELECT 'foo'
114 UNION ALL
115 SELECT n || ' bar' FROM t WHERE length(n) < 20
116 )
117 SELECT n, n IS OF (text) as is_text FROM t;
118 n | is_text
119 -------------------------+---------
120 foo | t
121 foo bar | t
122 foo bar bar | t
123 foo bar bar bar | t
124 foo bar bar bar bar | t
125 foo bar bar bar bar bar | t
126 (6 rows)
128 --
129 -- Some examples with a tree
130 --
131 -- department structure represented here is as follows:
132 --
133 -- ROOT-+->A-+->B-+->C
134 -- | |
135 -- | +->D-+->F
136 -- +->E-+->G
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
141 );
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
153 (
154 -- non recursive term
155 SELECT name as root_name, * FROM department WHERE name = 'A'
156 UNION ALL
157 -- recursive term
158 SELECT sd.root_name, d.* FROM department AS d, subdepartment AS sd
159 WHERE d.parent_department = sd.id
160 )
161 SELECT * FROM subdepartment ORDER BY name;
162 root_name | id | parent_department | name
163 -----------+----+-------------------+------
164 A | 1 | 0 | A
165 A | 2 | 1 | B
166 A | 3 | 2 | C
167 A | 4 | 2 | D
168 A | 6 | 4 | F
169 (5 rows)
171 -- extract all departments under 'A' with "level" number
172 WITH RECURSIVE subdepartment(level, id, parent_department, name) AS
173 (
174 -- non recursive term
175 SELECT 1, * FROM department WHERE name = 'A'
176 UNION ALL
177 -- recursive term
178 SELECT sd.level + 1, d.* FROM department AS d, subdepartment AS sd
179 WHERE d.parent_department = sd.id
180 )
181 SELECT * FROM subdepartment ORDER BY name;
182 level | id | parent_department | name
183 -------+----+-------------------+------
184 1 | 1 | 0 | A
185 2 | 2 | 1 | B
186 3 | 3 | 2 | C
187 3 | 4 | 2 | D
188 4 | 6 | 4 | F
189 (5 rows)
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
194 (
195 -- non recursive term
196 SELECT 1, * FROM department WHERE name = 'A'
197 UNION ALL
198 -- recursive term
199 SELECT sd.level + 1, d.* FROM department AS d, subdepartment AS sd
200 WHERE d.parent_department = sd.id
201 )
202 SELECT * FROM subdepartment WHERE level >= 2 ORDER BY name;
203 level | id | parent_department | name
204 -------+----+-------------------+------
205 2 | 2 | 1 | B
206 3 | 3 | 2 | C
207 3 | 4 | 2 | D
208 4 | 6 | 4 | F
209 (4 rows)
211 -- "RECURSIVE" is ignored if the query has no self-reference
212 WITH RECURSIVE subdepartment AS
213 (
214 -- note lack of recursive UNION structure
215 SELECT * FROM department WHERE name = 'A'
216 )
217 SELECT * FROM subdepartment ORDER BY name;
218 id | parent_department | name
219 ----+-------------------+------
220 1 | 0 | A
221 (1 row)
223 -- inside subqueries
224 SELECT count(*) FROM (
225 WITH RECURSIVE t(n) AS (
226 SELECT 1 UNION ALL SELECT n + 1 FROM t WHERE n < 500
227 )
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
232 )
233 SELECT * FROM t WHERE n < 50000
234 ) AS t WHERE n < 100);
235 count
236 -------
237 98
238 (1 row)
240 -- use same CTE twice at different subquery levels
241 WITH q1(x,y) AS (
242 SELECT hundred, sum(ten) FROM tenk1 GROUP BY hundred
243 )
244 SELECT count(*) FROM q1 WHERE y > (SELECT sum(y)/100 FROM q1 qsub);
245 count
246 -------
247 50
248 (1 row)
250 -- via a VIEW
251 CREATE TEMPORARY VIEW vsubdepartment AS
252 WITH RECURSIVE subdepartment AS
253 (
254 -- non recursive term
255 SELECT * FROM department WHERE name = 'A'
256 UNION ALL
257 -- recursive term
258 SELECT d.* FROM department AS d, subdepartment AS sd
259 WHERE d.parent_department = sd.id
260 )
261 SELECT * FROM subdepartment;
262 SELECT * FROM vsubdepartment ORDER BY name;
263 id | parent_department | name
264 ----+-------------------+------
265 1 | 0 | A
266 2 | 1 | B
267 3 | 2 | C
268 4 | 2 | D
269 6 | 4 | F
270 (5 rows)
272 -- Check reverse listing
273 SELECT pg_get_viewdef('vsubdepartment'::regclass);
274 pg_get_viewdef
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;
277 (1 row)
279 SELECT pg_get_viewdef('vsubdepartment'::regclass, true);
280 pg_get_viewdef
281 --------------------------------------------------------------------------------------
282 WITH RECURSIVE subdepartment AS (
283 SELECT department.id, department.parent_department, department.name
284 FROM department
285 WHERE department.name = 'A'::text
286 UNION ALL
287 SELECT d.id, d.parent_department, d.name
288 FROM department d, subdepartment sd
289 WHERE d.parent_department = sd.id
290 )
291 SELECT subdepartment.id, subdepartment.parent_department, subdepartment.name
292 FROM subdepartment;
293 (1 row)
295 -- corner case in which sub-WITH gets initialized first
296 with recursive q as (
297 select * from department
298 union all
299 (with x as (select * from q)
300 select * from x)
301 )
302 select * from q limit 24;
303 id | parent_department | name
304 ----+-------------------+------
305 0 | | ROOT
306 1 | 0 | A
307 2 | 1 | B
308 3 | 2 | C
309 4 | 2 | D
310 5 | 0 | E
311 6 | 4 | F
312 7 | 5 | G
313 0 | | ROOT
314 1 | 0 | A
315 2 | 1 | B
316 3 | 2 | C
317 4 | 2 | D
318 5 | 0 | E
319 6 | 4 | F
320 7 | 5 | G
321 0 | | ROOT
322 1 | 0 | A
323 2 | 1 | B
324 3 | 2 | C
325 4 | 2 | D
326 5 | 0 | E
327 6 | 4 | F
328 7 | 5 | G
329 (24 rows)
331 with recursive q as (
332 select * from department
333 union all
334 (with recursive x as (
335 select * from department
336 union all
337 (select * from q union all select * from x)
338 )
339 select * from x)
340 )
341 select * from q limit 32;
342 id | parent_department | name
343 ----+-------------------+------
344 0 | | ROOT
345 1 | 0 | A
346 2 | 1 | B
347 3 | 2 | C
348 4 | 2 | D
349 5 | 0 | E
350 6 | 4 | F
351 7 | 5 | G
352 0 | | ROOT
353 1 | 0 | A
354 2 | 1 | B
355 3 | 2 | C
356 4 | 2 | D
357 5 | 0 | E
358 6 | 4 | F
359 7 | 5 | G
360 0 | | ROOT
361 1 | 0 | A
362 2 | 1 | B
363 3 | 2 | C
364 4 | 2 | D
365 5 | 0 | E
366 6 | 4 | F
367 7 | 5 | G
368 0 | | ROOT
369 1 | 0 | A
370 2 | 1 | B
371 3 | 2 | C
372 4 | 2 | D
373 5 | 0 | E
374 6 | 4 | F
375 7 | 5 | G
376 (32 rows)
378 -- recursive term has sub-UNION
379 WITH RECURSIVE t(i,j) AS (
380 VALUES (1,2)
381 UNION ALL
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))
385 SELECT * FROM t;
386 i | j
387 ---+---
388 1 | 2
389 2 | 3
390 3 | 4
391 (3 rows)
393 --
394 -- different tree example
395 --
396 CREATE TEMPORARY TABLE tree(
397 id INTEGER PRIMARY KEY,
398 parent_id INTEGER REFERENCES tree(id)
399 );
400 NOTICE: CREATE TABLE / PRIMARY KEY will create implicit index "tree_pkey" for table "tree"
401 INSERT INTO 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);
404 --
405 -- get all paths from "second level" nodes to leaf nodes
406 --
407 WITH RECURSIVE t(id, path) AS (
408 VALUES(1,ARRAY[]::integer[])
409 UNION ALL
410 SELECT tree.id, t.path || tree.id
411 FROM tree JOIN t ON (tree.parent_id = t.id)
412 )
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 ----+------+----+-------------
420 2 | {2} | 4 | {2,4}
421 2 | {2} | 5 | {2,5}
422 2 | {2} | 6 | {2,6}
423 2 | {2} | 9 | {2,4,9}
424 2 | {2} | 10 | {2,4,10}
425 2 | {2} | 14 | {2,4,9,14}
426 3 | {3} | 7 | {3,7}
427 3 | {3} | 8 | {3,8}
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}
433 (13 rows)
435 -- just count 'em
436 WITH RECURSIVE t(id, path) AS (
437 VALUES(1,ARRAY[]::integer[])
438 UNION ALL
439 SELECT tree.id, t.path || tree.id
440 FROM tree JOIN t ON (tree.parent_id = t.id)
441 )
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)
446 GROUP BY t1.id
447 ORDER BY t1.id;
448 id | count
449 ----+-------
450 2 | 6
451 3 | 7
452 (2 rows)
454 --
455 -- test cycle detection
456 --
457 create temp table graph( f int, t int, label text );
458 insert into graph values
459 (1, 2, 'arc 1 -> 2'),
460 (1, 3, 'arc 1 -> 3'),
461 (2, 3, 'arc 2 -> 3'),
462 (1, 4, 'arc 1 -> 4'),
463 (4, 5, 'arc 4 -> 5'),
464 (5, 1, 'arc 5 -> 1');
465 with recursive search_graph(f, t, label, path, cycle) as (
466 select *, array[row(g.f, g.t)], false from graph g
467 union all
468 select g.*, path || row(g.f, g.t), row(g.f, g.t) = any(path)
469 from graph g, search_graph sg
470 where g.f = sg.t and not cycle
471 )
472 select * from search_graph;
473 f | t | label | path | cycle
474 ---+---+------------+-------------------------------------------+-------
475 1 | 2 | arc 1 -> 2 | {"(1,2)"} | f
476 1 | 3 | arc 1 -> 3 | {"(1,3)"} | f
477 2 | 3 | arc 2 -> 3 | {"(2,3)"} | f
478 1 | 4 | arc 1 -> 4 | {"(1,4)"} | f
479 4 | 5 | arc 4 -> 5 | {"(4,5)"} | f
480 5 | 1 | arc 5 -> 1 | {"(5,1)"} | f
481 1 | 2 | arc 1 -> 2 | {"(5,1)","(1,2)"} | f
482 1 | 3 | arc 1 -> 3 | {"(5,1)","(1,3)"} | f
483 1 | 4 | arc 1 -> 4 | {"(5,1)","(1,4)"} | f
484 2 | 3 | arc 2 -> 3 | {"(1,2)","(2,3)"} | f
485 4 | 5 | arc 4 -> 5 | {"(1,4)","(4,5)"} | f
486 5 | 1 | arc 5 -> 1 | {"(4,5)","(5,1)"} | f
487 1 | 2 | arc 1 -> 2 | {"(4,5)","(5,1)","(1,2)"} | f
488 1 | 3 | arc 1 -> 3 | {"(4,5)","(5,1)","(1,3)"} | f
489 1 | 4 | arc 1 -> 4 | {"(4,5)","(5,1)","(1,4)"} | f
490 2 | 3 | arc 2 -> 3 | {"(5,1)","(1,2)","(2,3)"} | f
491 4 | 5 | arc 4 -> 5 | {"(5,1)","(1,4)","(4,5)"} | f
492 5 | 1 | arc 5 -> 1 | {"(1,4)","(4,5)","(5,1)"} | f
493 1 | 2 | arc 1 -> 2 | {"(1,4)","(4,5)","(5,1)","(1,2)"} | f
494 1 | 3 | arc 1 -> 3 | {"(1,4)","(4,5)","(5,1)","(1,3)"} | f
495 1 | 4 | arc 1 -> 4 | {"(1,4)","(4,5)","(5,1)","(1,4)"} | t
496 2 | 3 | arc 2 -> 3 | {"(4,5)","(5,1)","(1,2)","(2,3)"} | f
497 4 | 5 | arc 4 -> 5 | {"(4,5)","(5,1)","(1,4)","(4,5)"} | t
498 5 | 1 | arc 5 -> 1 | {"(5,1)","(1,4)","(4,5)","(5,1)"} | t
499 2 | 3 | arc 2 -> 3 | {"(1,4)","(4,5)","(5,1)","(1,2)","(2,3)"} | f
500 (25 rows)
502 -- ordering by the path column has same effect as SEARCH DEPTH FIRST
503 with recursive search_graph(f, t, label, path, cycle) as (
504 select *, array[row(g.f, g.t)], false from graph g
505 union all
506 select g.*, path || row(g.f, g.t), row(g.f, g.t) = any(path)
507 from graph g, search_graph sg
508 where g.f = sg.t and not cycle
509 )
510 select * from search_graph order by path;
511 f | t | label | path | cycle
512 ---+---+------------+-------------------------------------------+-------
513 1 | 2 | arc 1 -> 2 | {"(1,2)"} | f
514 2 | 3 | arc 2 -> 3 | {"(1,2)","(2,3)"} | f
515 1 | 3 | arc 1 -> 3 | {"(1,3)"} | f
516 1 | 4 | arc 1 -> 4 | {"(1,4)"} | f
517 4 | 5 | arc 4 -> 5 | {"(1,4)","(4,5)"} | f
518 5 | 1 | arc 5 -> 1 | {"(1,4)","(4,5)","(5,1)"} | f
519 1 | 2 | arc 1 -> 2 | {"(1,4)","(4,5)","(5,1)","(1,2)"} | f
520 2 | 3 | arc 2 -> 3 | {"(1,4)","(4,5)","(5,1)","(1,2)","(2,3)"} | f
521 1 | 3 | arc 1 -> 3 | {"(1,4)","(4,5)","(5,1)","(1,3)"} | f
522 1 | 4 | arc 1 -> 4 | {"(1,4)","(4,5)","(5,1)","(1,4)"} | t
523 2 | 3 | arc 2 -> 3 | {"(2,3)"} | f
524 4 | 5 | arc 4 -> 5 | {"(4,5)"} | f
525 5 | 1 | arc 5 -> 1 | {"(4,5)","(5,1)"} | f
526 1 | 2 | arc 1 -> 2 | {"(4,5)","(5,1)","(1,2)"} | f
527 2 | 3 | arc 2 -> 3 | {"(4,5)","(5,1)","(1,2)","(2,3)"} | f
528 1 | 3 | arc 1 -> 3 | {"(4,5)","(5,1)","(1,3)"} | f
529 1 | 4 | arc 1 -> 4 | {"(4,5)","(5,1)","(1,4)"} | f
530 4 | 5 | arc 4 -> 5 | {"(4,5)","(5,1)","(1,4)","(4,5)"} | t
531 5 | 1 | arc 5 -> 1 | {"(5,1)"} | f
532 1 | 2 | arc 1 -> 2 | {"(5,1)","(1,2)"} | f
533 2 | 3 | arc 2 -> 3 | {"(5,1)","(1,2)","(2,3)"} | f
534 1 | 3 | arc 1 -> 3 | {"(5,1)","(1,3)"} | f
535 1 | 4 | arc 1 -> 4 | {"(5,1)","(1,4)"} | f
536 4 | 5 | arc 4 -> 5 | {"(5,1)","(1,4)","(4,5)"} | f
537 5 | 1 | arc 5 -> 1 | {"(5,1)","(1,4)","(4,5)","(5,1)"} | t
538 (25 rows)
540 --
541 -- test multiple WITH queries
542 --
543 WITH RECURSIVE
544 y (id) AS (VALUES (1)),
545 x (id) AS (SELECT * FROM y UNION ALL SELECT id+1 FROM x WHERE id < 5)
546 SELECT * FROM x;
547 id
548 ----
549 1
550 2
551 3
552 4
553 5
554 (5 rows)
556 -- forward reference OK
557 WITH RECURSIVE
558 x(id) AS (SELECT * FROM y UNION ALL SELECT id+1 FROM x WHERE id < 5),
559 y(id) AS (values (1))
560 SELECT * FROM x;
561 id
562 ----
563 1
564 2
565 3
566 4
567 5
568 (5 rows)
570 WITH RECURSIVE
571 x(id) AS
572 (VALUES (1) UNION ALL SELECT id+1 FROM x WHERE id < 5),
573 y(id) AS
574 (VALUES (1) UNION ALL SELECT id+1 FROM y WHERE id < 10)
575 SELECT y.*, x.* FROM y LEFT JOIN x USING (id);
576 id | id
577 ----+----
578 1 | 1
579 2 | 2
580 3 | 3
581 4 | 4
582 5 | 5
583 6 |
584 7 |
585 8 |
586 9 |
587 10 |
588 (10 rows)
590 WITH RECURSIVE
591 x(id) AS
592 (VALUES (1) UNION ALL SELECT id+1 FROM x WHERE id < 5),
593 y(id) AS
594 (VALUES (1) UNION ALL SELECT id+1 FROM x WHERE id < 10)
595 SELECT y.*, x.* FROM y LEFT JOIN x USING (id);
596 id | id
597 ----+----
598 1 | 1
599 2 | 2
600 3 | 3
601 4 | 4
602 5 | 5
603 6 |
604 (6 rows)
606 WITH RECURSIVE
607 x(id) AS
608 (SELECT 1 UNION ALL SELECT id+1 FROM x WHERE id < 3 ),
609 y(id) AS
610 (SELECT * FROM x UNION ALL SELECT * FROM x),
611 z(id) AS
612 (SELECT * FROM x UNION ALL SELECT id+1 FROM z WHERE id < 10)
613 SELECT * FROM z;
614 id
615 ----
616 1
617 2
618 3
619 2
620 3
621 4
622 3
623 4
624 5
625 4
626 5
627 6
628 5
629 6
630 7
631 6
632 7
633 8
634 7
635 8
636 9
637 8
638 9
639 10
640 9
641 10
642 10
643 (27 rows)
645 WITH RECURSIVE
646 x(id) AS
647 (SELECT 1 UNION ALL SELECT id+1 FROM x WHERE id < 3 ),
648 y(id) AS
649 (SELECT * FROM x UNION ALL SELECT * FROM x),
650 z(id) AS
651 (SELECT * FROM y UNION ALL SELECT id+1 FROM z WHERE id < 10)
652 SELECT * FROM z;
653 id
654 ----
655 1
656 2
657 3
658 1
659 2
660 3
661 2
662 3
663 4
664 2
665 3
666 4
667 3
668 4
669 5
670 3
671 4
672 5
673 4
674 5
675 6
676 4
677 5
678 6
679 5
680 6
681 7
682 5
683 6
684 7
685 6
686 7
687 8
688 6
689 7
690 8
691 7
692 8
693 9
694 7
695 8
696 9
697 8
698 9
699 10
700 8
701 9
702 10
703 9
704 10
705 9
706 10
707 10
708 10
709 (54 rows)
711 --
712 -- error cases
713 --
714 -- INTERSECT
715 WITH RECURSIVE x(n) AS (SELECT 1 INTERSECT SELECT n+1 FROM x)
716 SELECT * FROM x;
717 ERROR: recursive query "x" does not have the form non-recursive-term UNION [ALL] recursive-term
718 LINE 1: WITH RECURSIVE x(n) AS (SELECT 1 INTERSECT SELECT n+1 FROM x...
719 ^
720 WITH RECURSIVE x(n) AS (SELECT 1 INTERSECT ALL SELECT n+1 FROM x)
721 SELECT * FROM x;
722 ERROR: recursive query "x" does not have the form non-recursive-term UNION [ALL] recursive-term
723 LINE 1: WITH RECURSIVE x(n) AS (SELECT 1 INTERSECT ALL SELECT n+1 FR...
724 ^
725 -- EXCEPT
726 WITH RECURSIVE x(n) AS (SELECT 1 EXCEPT SELECT n+1 FROM x)
727 SELECT * FROM x;
728 ERROR: recursive query "x" does not have the form non-recursive-term UNION [ALL] recursive-term
729 LINE 1: WITH RECURSIVE x(n) AS (SELECT 1 EXCEPT SELECT n+1 FROM x)
730 ^
731 WITH RECURSIVE x(n) AS (SELECT 1 EXCEPT ALL SELECT n+1 FROM x)
732 SELECT * FROM x;
733 ERROR: recursive query "x" does not have the form non-recursive-term UNION [ALL] recursive-term
734 LINE 1: WITH RECURSIVE x(n) AS (SELECT 1 EXCEPT ALL SELECT n+1 FROM ...
735 ^
736 -- no non-recursive term
737 WITH RECURSIVE x(n) AS (SELECT n FROM x)
738 SELECT * FROM x;
739 ERROR: recursive query "x" does not have the form non-recursive-term UNION [ALL] recursive-term
740 LINE 1: WITH RECURSIVE x(n) AS (SELECT n FROM x)
741 ^
742 -- recursive term in the left hand side (strictly speaking, should allow this)
743 WITH RECURSIVE x(n) AS (SELECT n FROM x UNION ALL SELECT 1)
744 SELECT * FROM x;
745 ERROR: recursive reference to query "x" must not appear within its non-recursive term
746 LINE 1: WITH RECURSIVE x(n) AS (SELECT n FROM x UNION ALL SELECT 1)
747 ^
748 CREATE TEMPORARY TABLE y (a INTEGER);
749 INSERT INTO y SELECT generate_series(1, 10);
750 -- LEFT JOIN
751 WITH RECURSIVE x(n) AS (SELECT a FROM y WHERE a = 1
752 UNION ALL
753 SELECT x.n+1 FROM y LEFT JOIN x ON x.n = y.a WHERE n < 10)
754 SELECT * FROM x;
755 ERROR: recursive reference to query "x" must not appear within an outer join
756 LINE 3: SELECT x.n+1 FROM y LEFT JOIN x ON x.n = y.a WHERE n < 10)
757 ^
758 -- RIGHT JOIN
759 WITH RECURSIVE x(n) AS (SELECT a FROM y WHERE a = 1
760 UNION ALL
761 SELECT x.n+1 FROM x RIGHT JOIN y ON x.n = y.a WHERE n < 10)
762 SELECT * FROM x;
763 ERROR: recursive reference to query "x" must not appear within an outer join
764 LINE 3: SELECT x.n+1 FROM x RIGHT JOIN y ON x.n = y.a WHERE n < 10)
765 ^
766 -- FULL JOIN
767 WITH RECURSIVE x(n) AS (SELECT a FROM y WHERE a = 1
768 UNION ALL
769 SELECT x.n+1 FROM x FULL JOIN y ON x.n = y.a WHERE n < 10)
770 SELECT * FROM x;
771 ERROR: recursive reference to query "x" must not appear within an outer join
772 LINE 3: SELECT x.n+1 FROM x FULL JOIN y ON x.n = y.a WHERE n < 10)
773 ^
774 -- subquery
775 WITH RECURSIVE x(n) AS (SELECT 1 UNION ALL SELECT n+1 FROM x
776 WHERE n IN (SELECT * FROM x))
777 SELECT * FROM x;
778 ERROR: recursive reference to query "x" must not appear within a subquery
779 LINE 2: WHERE n IN (SELECT * FROM x))
780 ^
781 -- aggregate functions
782 WITH RECURSIVE x(n) AS (SELECT 1 UNION ALL SELECT count(*) FROM x)
783 SELECT * FROM x;
784 ERROR: aggregates not allowed in a recursive query's recursive term
785 LINE 1: WITH RECURSIVE x(n) AS (SELECT 1 UNION ALL SELECT count(*) F...
786 ^
787 WITH RECURSIVE x(n) AS (SELECT 1 UNION ALL SELECT sum(n) FROM x)
788 SELECT * FROM x;
789 ERROR: aggregates not allowed in a recursive query's recursive term
790 LINE 1: WITH RECURSIVE x(n) AS (SELECT 1 UNION ALL SELECT sum(n) FRO...
791 ^
792 -- ORDER BY
793 WITH RECURSIVE x(n) AS (SELECT 1 UNION ALL SELECT n+1 FROM x ORDER BY 1)
794 SELECT * FROM x;
795 ERROR: ORDER BY in a recursive query is not implemented
796 LINE 1: ...VE x(n) AS (SELECT 1 UNION ALL SELECT n+1 FROM x ORDER BY 1)
797 ^
798 -- LIMIT/OFFSET
799 WITH RECURSIVE x(n) AS (SELECT 1 UNION ALL SELECT n+1 FROM x LIMIT 10 OFFSET 1)
800 SELECT * FROM x;
801 ERROR: OFFSET in a recursive query is not implemented
802 LINE 1: ... AS (SELECT 1 UNION ALL SELECT n+1 FROM x LIMIT 10 OFFSET 1)
803 ^
804 -- FOR UPDATE
805 WITH RECURSIVE x(n) AS (SELECT 1 UNION ALL SELECT n+1 FROM x FOR UPDATE)
806 SELECT * FROM x;
807 ERROR: FOR UPDATE/SHARE in a recursive query is not implemented
808 -- target list has a recursive query name
809 WITH RECURSIVE x(id) AS (values (1)
810 UNION ALL
811 SELECT (SELECT * FROM x) FROM x WHERE id < 5
812 ) SELECT * FROM x;
813 ERROR: recursive reference to query "x" must not appear within a subquery
814 LINE 3: SELECT (SELECT * FROM x) FROM x WHERE id < 5
815 ^
816 -- mutual recursive query (not implemented)
817 WITH RECURSIVE
818 x (id) AS (SELECT 1 UNION ALL SELECT id+1 FROM y WHERE id < 5),
819 y (id) AS (SELECT 1 UNION ALL SELECT id+1 FROM x WHERE id < 5)
820 SELECT * FROM x;
821 ERROR: mutual recursion between WITH items is not implemented
822 LINE 2: x (id) AS (SELECT 1 UNION ALL SELECT id+1 FROM y WHERE id ...
823 ^
824 -- non-linear recursion is not allowed
825 WITH RECURSIVE foo(i) AS
826 (values (1)
827 UNION ALL
828 (SELECT i+1 FROM foo WHERE i < 10
829 UNION ALL
830 SELECT i+1 FROM foo WHERE i < 5)
831 ) SELECT * FROM foo;
832 ERROR: recursive reference to query "foo" must not appear more than once
833 LINE 6: SELECT i+1 FROM foo WHERE i < 5)
834 ^
835 WITH RECURSIVE foo(i) AS
836 (values (1)
837 UNION ALL
838 SELECT * FROM
839 (SELECT i+1 FROM foo WHERE i < 10
840 UNION ALL
841 SELECT i+1 FROM foo WHERE i < 5) AS t
842 ) SELECT * FROM foo;
843 ERROR: recursive reference to query "foo" must not appear more than once
844 LINE 7: SELECT i+1 FROM foo WHERE i < 5) AS t
845 ^
846 WITH RECURSIVE foo(i) AS
847 (values (1)
848 UNION ALL
849 (SELECT i+1 FROM foo WHERE i < 10
850 EXCEPT
851 SELECT i+1 FROM foo WHERE i < 5)
852 ) SELECT * FROM foo;
853 ERROR: recursive reference to query "foo" must not appear within EXCEPT
854 LINE 6: SELECT i+1 FROM foo WHERE i < 5)
855 ^
856 WITH RECURSIVE foo(i) AS
857 (values (1)
858 UNION ALL
859 (SELECT i+1 FROM foo WHERE i < 10
860 INTERSECT
861 SELECT i+1 FROM foo WHERE i < 5)
862 ) SELECT * FROM foo;
863 ERROR: recursive reference to query "foo" must not appear more than once
864 LINE 6: SELECT i+1 FROM foo WHERE i < 5)
865 ^
866 -- Wrong type induced from non-recursive term
867 WITH RECURSIVE foo(i) AS
868 (SELECT i FROM (VALUES(1),(2)) t(i)
869 UNION ALL
870 SELECT (i+1)::numeric(10,0) FROM foo WHERE i < 10)
871 SELECT * FROM foo;
872 ERROR: recursive query "foo" column 1 has type integer in non-recursive term but type numeric overall
873 LINE 2: (SELECT i FROM (VALUES(1),(2)) t(i)
874 ^
875 HINT: Cast the output of the non-recursive term to the correct type.
876 -- rejects different typmod, too (should we allow this?)
877 WITH RECURSIVE foo(i) AS
878 (SELECT i::numeric(3,0) FROM (VALUES(1),(2)) t(i)
879 UNION ALL
880 SELECT (i+1)::numeric(10,0) FROM foo WHERE i < 10)
881 SELECT * FROM foo;
882 ERROR: recursive query "foo" column 1 has type numeric(3,0) in non-recursive term but type numeric overall
883 LINE 2: (SELECT i::numeric(3,0) FROM (VALUES(1),(2)) t(i)
884 ^
885 HINT: Cast the output of the non-recursive term to the correct type.