| blob | 08cfa5463fbcc6158a3c1c8b94610466112073dc |
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 -- UNION DISTINCT requires hashable type
53 WITH RECURSIVE t(n) AS (
54 VALUES ('01'::varbit)
55 UNION
56 SELECT n || '10'::varbit FROM t WHERE n < '100'::varbit
57 )
58 SELECT n FROM t;
59 ERROR: could not implement recursive UNION
60 DETAIL: All column datatypes must be hashable.
61 -- recursive view
62 CREATE RECURSIVE VIEW nums (n) AS
63 VALUES (1)
64 UNION ALL
65 SELECT n+1 FROM nums WHERE n < 5;
66 SELECT * FROM nums;
67 n
68 ---
69 1
70 2
71 3
72 4
73 5
74 (5 rows)
76 CREATE OR REPLACE RECURSIVE VIEW nums (n) AS
77 VALUES (1)
78 UNION ALL
79 SELECT n+1 FROM nums WHERE n < 6;
80 SELECT * FROM nums;
81 n
82 ---
83 1
84 2
85 3
86 4
87 5
88 6
89 (6 rows)
91 -- This is an infinite loop with UNION ALL, but not with UNION
92 WITH RECURSIVE t(n) AS (
93 SELECT 1
94 UNION
95 SELECT 10-n FROM t)
96 SELECT * FROM t;
97 n
98 ---
99 1
100 9
101 (2 rows)
103 -- This'd be an infinite loop, but outside query reads only as much as needed
104 WITH RECURSIVE t(n) AS (
105 VALUES (1)
106 UNION ALL
107 SELECT n+1 FROM t)
108 SELECT * FROM t LIMIT 10;
109 n
110 ----
111 1
112 2
113 3
114 4
115 5
116 6
117 7
118 8
119 9
120 10
121 (10 rows)
123 -- UNION case should have same property
124 WITH RECURSIVE t(n) AS (
125 SELECT 1
126 UNION
127 SELECT n+1 FROM t)
128 SELECT * FROM t LIMIT 10;
129 n
130 ----
131 1
132 2
133 3
134 4
135 5
136 6
137 7
138 8
139 9
140 10
141 (10 rows)
143 -- Test behavior with an unknown-type literal in the WITH
144 WITH q AS (SELECT 'foo' AS x)
145 SELECT x, pg_typeof(x) FROM q;
146 x | pg_typeof
147 -----+-----------
148 foo | text
149 (1 row)
151 WITH RECURSIVE t(n) AS (
152 SELECT 'foo'
153 UNION ALL
154 SELECT n || ' bar' FROM t WHERE length(n) < 20
155 )
156 SELECT n, pg_typeof(n) FROM t;
157 n | pg_typeof
158 -------------------------+-----------
159 foo | text
160 foo bar | text
161 foo bar bar | text
162 foo bar bar bar | text
163 foo bar bar bar bar | text
164 foo bar bar bar bar bar | text
165 (6 rows)
167 -- In a perfect world, this would work and resolve the literal as int ...
168 -- but for now, we have to be content with resolving to text too soon.
169 WITH RECURSIVE t(n) AS (
170 SELECT '7'
171 UNION ALL
172 SELECT n+1 FROM t WHERE n < 10
173 )
174 SELECT n, pg_typeof(n) FROM t;
175 ERROR: operator does not exist: text + integer
176 LINE 4: SELECT n+1 FROM t WHERE n < 10
177 ^
178 HINT: No operator matches the given name and argument types. You might need to add explicit type casts.
179 -- Deeply nested WITH caused a list-munging problem in v13
180 -- Detection of cross-references and self-references
181 WITH RECURSIVE w1(c1) AS
182 (WITH w2(c2) AS
183 (WITH w3(c3) AS
184 (WITH w4(c4) AS
185 (WITH w5(c5) AS
186 (WITH RECURSIVE w6(c6) AS
187 (WITH w6(c6) AS
188 (WITH w8(c8) AS
189 (SELECT 1)
190 SELECT * FROM w8)
191 SELECT * FROM w6)
192 SELECT * FROM w6)
193 SELECT * FROM w5)
194 SELECT * FROM w4)
195 SELECT * FROM w3)
196 SELECT * FROM w2)
197 SELECT * FROM w1;
198 c1
199 ----
200 1
201 (1 row)
203 -- Detection of invalid self-references
204 WITH RECURSIVE outermost(x) AS (
205 SELECT 1
206 UNION (WITH innermost1 AS (
207 SELECT 2
208 UNION (WITH innermost2 AS (
209 SELECT 3
210 UNION (WITH innermost3 AS (
211 SELECT 4
212 UNION (WITH innermost4 AS (
213 SELECT 5
214 UNION (WITH innermost5 AS (
215 SELECT 6
216 UNION (WITH innermost6 AS
217 (SELECT 7)
218 SELECT * FROM innermost6))
219 SELECT * FROM innermost5))
220 SELECT * FROM innermost4))
221 SELECT * FROM innermost3))
222 SELECT * FROM innermost2))
223 SELECT * FROM outermost
224 UNION SELECT * FROM innermost1)
225 )
226 SELECT * FROM outermost ORDER BY 1;
227 x
228 ---
229 1
230 2
231 3
232 4
233 5
234 6
235 7
236 (7 rows)
238 --
239 -- Some examples with a tree
240 --
241 -- department structure represented here is as follows:
242 --
243 -- ROOT-+->A-+->B-+->C
244 -- | |
245 -- | +->D-+->F
246 -- +->E-+->G
247 CREATE TEMP TABLE department (
248 id INTEGER PRIMARY KEY, -- department ID
249 parent_department INTEGER REFERENCES department, -- upper department ID
250 name TEXT -- department name
251 );
252 INSERT INTO department VALUES (0, NULL, 'ROOT');
253 INSERT INTO department VALUES (1, 0, 'A');
254 INSERT INTO department VALUES (2, 1, 'B');
255 INSERT INTO department VALUES (3, 2, 'C');
256 INSERT INTO department VALUES (4, 2, 'D');
257 INSERT INTO department VALUES (5, 0, 'E');
258 INSERT INTO department VALUES (6, 4, 'F');
259 INSERT INTO department VALUES (7, 5, 'G');
260 -- extract all departments under 'A'. Result should be A, B, C, D and F
261 WITH RECURSIVE subdepartment AS
262 (
263 -- non recursive term
264 SELECT name as root_name, * FROM department WHERE name = 'A'
265 UNION ALL
266 -- recursive term
267 SELECT sd.root_name, d.* FROM department AS d, subdepartment AS sd
268 WHERE d.parent_department = sd.id
269 )
270 SELECT * FROM subdepartment ORDER BY name;
271 root_name | id | parent_department | name
272 -----------+----+-------------------+------
273 A | 1 | 0 | A
274 A | 2 | 1 | B
275 A | 3 | 2 | C
276 A | 4 | 2 | D
277 A | 6 | 4 | F
278 (5 rows)
280 -- extract all departments under 'A' with "level" number
281 WITH RECURSIVE subdepartment(level, id, parent_department, name) AS
282 (
283 -- non recursive term
284 SELECT 1, * FROM department WHERE name = 'A'
285 UNION ALL
286 -- recursive term
287 SELECT sd.level + 1, d.* FROM department AS d, subdepartment AS sd
288 WHERE d.parent_department = sd.id
289 )
290 SELECT * FROM subdepartment ORDER BY name;
291 level | id | parent_department | name
292 -------+----+-------------------+------
293 1 | 1 | 0 | A
294 2 | 2 | 1 | B
295 3 | 3 | 2 | C
296 3 | 4 | 2 | D
297 4 | 6 | 4 | F
298 (5 rows)
300 -- extract all departments under 'A' with "level" number.
301 -- Only shows level 2 or more
302 WITH RECURSIVE subdepartment(level, id, parent_department, name) AS
303 (
304 -- non recursive term
305 SELECT 1, * FROM department WHERE name = 'A'
306 UNION ALL
307 -- recursive term
308 SELECT sd.level + 1, d.* FROM department AS d, subdepartment AS sd
309 WHERE d.parent_department = sd.id
310 )
311 SELECT * FROM subdepartment WHERE level >= 2 ORDER BY name;
312 level | id | parent_department | name
313 -------+----+-------------------+------
314 2 | 2 | 1 | B
315 3 | 3 | 2 | C
316 3 | 4 | 2 | D
317 4 | 6 | 4 | F
318 (4 rows)
320 -- "RECURSIVE" is ignored if the query has no self-reference
321 WITH RECURSIVE subdepartment AS
322 (
323 -- note lack of recursive UNION structure
324 SELECT * FROM department WHERE name = 'A'
325 )
326 SELECT * FROM subdepartment ORDER BY name;
327 id | parent_department | name
328 ----+-------------------+------
329 1 | 0 | A
330 (1 row)
332 -- inside subqueries
333 SELECT count(*) FROM (
334 WITH RECURSIVE t(n) AS (
335 SELECT 1 UNION ALL SELECT n + 1 FROM t WHERE n < 500
336 )
337 SELECT * FROM t) AS t WHERE n < (
338 SELECT count(*) FROM (
339 WITH RECURSIVE t(n) AS (
340 SELECT 1 UNION ALL SELECT n + 1 FROM t WHERE n < 100
341 )
342 SELECT * FROM t WHERE n < 50000
343 ) AS t WHERE n < 100);
344 count
345 -------
346 98
347 (1 row)
349 -- use same CTE twice at different subquery levels
350 WITH q1(x,y) AS (
351 SELECT hundred, sum(ten) FROM tenk1 GROUP BY hundred
352 )
353 SELECT count(*) FROM q1 WHERE y > (SELECT sum(y)/100 FROM q1 qsub);
354 count
355 -------
356 50
357 (1 row)
359 -- via a VIEW
360 CREATE TEMPORARY VIEW vsubdepartment AS
361 WITH RECURSIVE subdepartment AS
362 (
363 -- non recursive term
364 SELECT * FROM department WHERE name = 'A'
365 UNION ALL
366 -- recursive term
367 SELECT d.* FROM department AS d, subdepartment AS sd
368 WHERE d.parent_department = sd.id
369 )
370 SELECT * FROM subdepartment;
371 SELECT * FROM vsubdepartment ORDER BY name;
372 id | parent_department | name
373 ----+-------------------+------
374 1 | 0 | A
375 2 | 1 | B
376 3 | 2 | C
377 4 | 2 | D
378 6 | 4 | F
379 (5 rows)
381 -- Check reverse listing
382 SELECT pg_get_viewdef('vsubdepartment'::regclass);
383 pg_get_viewdef
384 -----------------------------------------------
385 WITH RECURSIVE subdepartment AS ( +
386 SELECT department.id, +
387 department.parent_department, +
388 department.name +
389 FROM department +
390 WHERE (department.name = 'A'::text)+
391 UNION ALL +
392 SELECT d.id, +
393 d.parent_department, +
394 d.name +
395 FROM department d, +
396 subdepartment sd +
397 WHERE (d.parent_department = sd.id)+
398 ) +
399 SELECT id, +
400 parent_department, +
401 name +
402 FROM subdepartment;
403 (1 row)
405 SELECT pg_get_viewdef('vsubdepartment'::regclass, true);
406 pg_get_viewdef
407 ---------------------------------------------
408 WITH RECURSIVE subdepartment AS ( +
409 SELECT department.id, +
410 department.parent_department, +
411 department.name +
412 FROM department +
413 WHERE department.name = 'A'::text+
414 UNION ALL +
415 SELECT d.id, +
416 d.parent_department, +
417 d.name +
418 FROM department d, +
419 subdepartment sd +
420 WHERE d.parent_department = sd.id+
421 ) +
422 SELECT id, +
423 parent_department, +
424 name +
425 FROM subdepartment;
426 (1 row)
428 -- Another reverse-listing example
429 CREATE VIEW sums_1_100 AS
430 WITH RECURSIVE t(n) AS (
431 VALUES (1)
432 UNION ALL
433 SELECT n+1 FROM t WHERE n < 100
434 )
435 SELECT sum(n) FROM t;
436 \d+ sums_1_100
437 View "public.sums_1_100"
438 Column | Type | Collation | Nullable | Default | Storage | Description
439 --------+--------+-----------+----------+---------+---------+-------------
440 sum | bigint | | | | plain |
441 View definition:
442 WITH RECURSIVE t(n) AS (
443 VALUES (1)
444 UNION ALL
445 SELECT t_1.n + 1
446 FROM t t_1
447 WHERE t_1.n < 100
448 )
449 SELECT sum(n) AS sum
450 FROM t;
452 -- corner case in which sub-WITH gets initialized first
453 with recursive q as (
454 select * from department
455 union all
456 (with x as (select * from q)
457 select * from x)
458 )
459 select * from q limit 24;
460 id | parent_department | name
461 ----+-------------------+------
462 0 | | ROOT
463 1 | 0 | A
464 2 | 1 | B
465 3 | 2 | C
466 4 | 2 | D
467 5 | 0 | E
468 6 | 4 | F
469 7 | 5 | G
470 0 | | ROOT
471 1 | 0 | A
472 2 | 1 | B
473 3 | 2 | C
474 4 | 2 | D
475 5 | 0 | E
476 6 | 4 | F
477 7 | 5 | G
478 0 | | ROOT
479 1 | 0 | A
480 2 | 1 | B
481 3 | 2 | C
482 4 | 2 | D
483 5 | 0 | E
484 6 | 4 | F
485 7 | 5 | G
486 (24 rows)
488 with recursive q as (
489 select * from department
490 union all
491 (with recursive x as (
492 select * from department
493 union all
494 (select * from q union all select * from x)
495 )
496 select * from x)
497 )
498 select * from q limit 32;
499 id | parent_department | name
500 ----+-------------------+------
501 0 | | ROOT
502 1 | 0 | A
503 2 | 1 | B
504 3 | 2 | C
505 4 | 2 | D
506 5 | 0 | E
507 6 | 4 | F
508 7 | 5 | G
509 0 | | ROOT
510 1 | 0 | A
511 2 | 1 | B
512 3 | 2 | C
513 4 | 2 | D
514 5 | 0 | E
515 6 | 4 | F
516 7 | 5 | G
517 0 | | ROOT
518 1 | 0 | A
519 2 | 1 | B
520 3 | 2 | C
521 4 | 2 | D
522 5 | 0 | E
523 6 | 4 | F
524 7 | 5 | G
525 0 | | ROOT
526 1 | 0 | A
527 2 | 1 | B
528 3 | 2 | C
529 4 | 2 | D
530 5 | 0 | E
531 6 | 4 | F
532 7 | 5 | G
533 (32 rows)
535 -- recursive term has sub-UNION
536 WITH RECURSIVE t(i,j) AS (
537 VALUES (1,2)
538 UNION ALL
539 SELECT t2.i, t.j+1 FROM
540 (SELECT 2 AS i UNION ALL SELECT 3 AS i) AS t2
541 JOIN t ON (t2.i = t.i+1))
542 SELECT * FROM t;
543 i | j
544 ---+---
545 1 | 2
546 2 | 3
547 3 | 4
548 (3 rows)
550 --
551 -- different tree example
552 --
553 CREATE TEMPORARY TABLE tree(
554 id INTEGER PRIMARY KEY,
555 parent_id INTEGER REFERENCES tree(id)
556 );
557 INSERT INTO tree
558 VALUES (1, NULL), (2, 1), (3,1), (4,2), (5,2), (6,2), (7,3), (8,3),
559 (9,4), (10,4), (11,7), (12,7), (13,7), (14, 9), (15,11), (16,11);
560 --
561 -- get all paths from "second level" nodes to leaf nodes
562 --
563 WITH RECURSIVE t(id, path) AS (
564 VALUES(1,ARRAY[]::integer[])
565 UNION ALL
566 SELECT tree.id, t.path || tree.id
567 FROM tree JOIN t ON (tree.parent_id = t.id)
568 )
569 SELECT t1.*, t2.* FROM t AS t1 JOIN t AS t2 ON
570 (t1.path[1] = t2.path[1] AND
571 array_upper(t1.path,1) = 1 AND
572 array_upper(t2.path,1) > 1)
573 ORDER BY t1.id, t2.id;
574 id | path | id | path
575 ----+------+----+-------------
576 2 | {2} | 4 | {2,4}
577 2 | {2} | 5 | {2,5}
578 2 | {2} | 6 | {2,6}
579 2 | {2} | 9 | {2,4,9}
580 2 | {2} | 10 | {2,4,10}
581 2 | {2} | 14 | {2,4,9,14}
582 3 | {3} | 7 | {3,7}
583 3 | {3} | 8 | {3,8}
584 3 | {3} | 11 | {3,7,11}
585 3 | {3} | 12 | {3,7,12}
586 3 | {3} | 13 | {3,7,13}
587 3 | {3} | 15 | {3,7,11,15}
588 3 | {3} | 16 | {3,7,11,16}
589 (13 rows)
591 -- just count 'em
592 WITH RECURSIVE t(id, path) AS (
593 VALUES(1,ARRAY[]::integer[])
594 UNION ALL
595 SELECT tree.id, t.path || tree.id
596 FROM tree JOIN t ON (tree.parent_id = t.id)
597 )
598 SELECT t1.id, count(t2.*) FROM t AS t1 JOIN t AS t2 ON
599 (t1.path[1] = t2.path[1] AND
600 array_upper(t1.path,1) = 1 AND
601 array_upper(t2.path,1) > 1)
602 GROUP BY t1.id
603 ORDER BY t1.id;
604 id | count
605 ----+-------
606 2 | 6
607 3 | 7
608 (2 rows)
610 -- this variant tickled a whole-row-variable bug in 8.4devel
611 WITH RECURSIVE t(id, path) AS (
612 VALUES(1,ARRAY[]::integer[])
613 UNION ALL
614 SELECT tree.id, t.path || tree.id
615 FROM tree JOIN t ON (tree.parent_id = t.id)
616 )
617 SELECT t1.id, t2.path, t2 FROM t AS t1 JOIN t AS t2 ON
618 (t1.id=t2.id);
619 id | path | t2
620 ----+-------------+--------------------
621 1 | {} | (1,{})
622 2 | {2} | (2,{2})
623 3 | {3} | (3,{3})
624 4 | {2,4} | (4,"{2,4}")
625 5 | {2,5} | (5,"{2,5}")
626 6 | {2,6} | (6,"{2,6}")
627 7 | {3,7} | (7,"{3,7}")
628 8 | {3,8} | (8,"{3,8}")
629 9 | {2,4,9} | (9,"{2,4,9}")
630 10 | {2,4,10} | (10,"{2,4,10}")
631 11 | {3,7,11} | (11,"{3,7,11}")
632 12 | {3,7,12} | (12,"{3,7,12}")
633 13 | {3,7,13} | (13,"{3,7,13}")
634 14 | {2,4,9,14} | (14,"{2,4,9,14}")
635 15 | {3,7,11,15} | (15,"{3,7,11,15}")
636 16 | {3,7,11,16} | (16,"{3,7,11,16}")
637 (16 rows)
639 -- test that column statistics from a materialized CTE are available
640 -- to upper planner (otherwise, we'd get a stupider plan)
641 explain (costs off)
642 with x as materialized (select unique1 from tenk1 b)
643 select count(*) from tenk1 a
644 where unique1 in (select * from x);
645 QUERY PLAN
646 ------------------------------------------------------------
647 Aggregate
648 CTE x
649 -> Index Only Scan using tenk1_unique1 on tenk1 b
650 -> Hash Semi Join
651 Hash Cond: (a.unique1 = x.unique1)
652 -> Index Only Scan using tenk1_unique1 on tenk1 a
653 -> Hash
654 -> CTE Scan on x
655 (8 rows)
657 explain (costs off)
658 with x as materialized (insert into tenk1 default values returning unique1)
659 select count(*) from tenk1 a
660 where unique1 in (select * from x);
661 QUERY PLAN
662 ------------------------------------------------------------
663 Aggregate
664 CTE x
665 -> Insert on tenk1
666 -> Result
667 -> Nested Loop
668 -> HashAggregate
669 Group Key: x.unique1
670 -> CTE Scan on x
671 -> Index Only Scan using tenk1_unique1 on tenk1 a
672 Index Cond: (unique1 = x.unique1)
673 (10 rows)
675 -- test that pathkeys from a materialized CTE are propagated up to the
676 -- outer query
677 explain (costs off)
678 with x as materialized (select unique1 from tenk1 b order by unique1)
679 select count(*) from tenk1 a
680 where unique1 in (select * from x);
681 QUERY PLAN
682 ------------------------------------------------------------
683 Aggregate
684 CTE x
685 -> Index Only Scan using tenk1_unique1 on tenk1 b
686 -> Merge Semi Join
687 Merge Cond: (a.unique1 = x.unique1)
688 -> Index Only Scan using tenk1_unique1 on tenk1 a
689 -> CTE Scan on x
690 (7 rows)
692 -- SEARCH clause
693 create temp table graph0( f int, t int, label text );
694 insert into graph0 values
695 (1, 2, 'arc 1 -> 2'),
696 (1, 3, 'arc 1 -> 3'),
697 (2, 3, 'arc 2 -> 3'),
698 (1, 4, 'arc 1 -> 4'),
699 (4, 5, 'arc 4 -> 5');
700 explain (verbose, costs off)
701 with recursive search_graph(f, t, label) as (
702 select * from graph0 g
703 union all
704 select g.*
705 from graph0 g, search_graph sg
706 where g.f = sg.t
707 ) search depth first by f, t set seq
708 select * from search_graph order by seq;
709 QUERY PLAN
710 ----------------------------------------------------------------------------------------------
711 Sort
712 Output: search_graph.f, search_graph.t, search_graph.label, search_graph.seq
713 Sort Key: search_graph.seq
714 CTE search_graph
715 -> Recursive Union
716 -> Seq Scan on pg_temp.graph0 g
717 Output: g.f, g.t, g.label, ARRAY[ROW(g.f, g.t)]
718 -> Merge Join
719 Output: g_1.f, g_1.t, g_1.label, array_cat(sg.seq, ARRAY[ROW(g_1.f, g_1.t)])
720 Merge Cond: (g_1.f = sg.t)
721 -> Sort
722 Output: g_1.f, g_1.t, g_1.label
723 Sort Key: g_1.f
724 -> Seq Scan on pg_temp.graph0 g_1
725 Output: g_1.f, g_1.t, g_1.label
726 -> Sort
727 Output: sg.seq, sg.t
728 Sort Key: sg.t
729 -> WorkTable Scan on search_graph sg
730 Output: sg.seq, sg.t
731 -> CTE Scan on search_graph
732 Output: search_graph.f, search_graph.t, search_graph.label, search_graph.seq
733 (22 rows)
735 with recursive search_graph(f, t, label) as (
736 select * from graph0 g
737 union all
738 select g.*
739 from graph0 g, search_graph sg
740 where g.f = sg.t
741 ) search depth first by f, t set seq
742 select * from search_graph order by seq;
743 f | t | label | seq
744 ---+---+------------+-------------------
745 1 | 2 | arc 1 -> 2 | {"(1,2)"}
746 2 | 3 | arc 2 -> 3 | {"(1,2)","(2,3)"}
747 1 | 3 | arc 1 -> 3 | {"(1,3)"}
748 1 | 4 | arc 1 -> 4 | {"(1,4)"}
749 4 | 5 | arc 4 -> 5 | {"(1,4)","(4,5)"}
750 2 | 3 | arc 2 -> 3 | {"(2,3)"}
751 4 | 5 | arc 4 -> 5 | {"(4,5)"}
752 (7 rows)
754 with recursive search_graph(f, t, label) as (
755 select * from graph0 g
756 union distinct
757 select g.*
758 from graph0 g, search_graph sg
759 where g.f = sg.t
760 ) search depth first by f, t set seq
761 select * from search_graph order by seq;
762 f | t | label | seq
763 ---+---+------------+-------------------
764 1 | 2 | arc 1 -> 2 | {"(1,2)"}
765 2 | 3 | arc 2 -> 3 | {"(1,2)","(2,3)"}
766 1 | 3 | arc 1 -> 3 | {"(1,3)"}
767 1 | 4 | arc 1 -> 4 | {"(1,4)"}
768 4 | 5 | arc 4 -> 5 | {"(1,4)","(4,5)"}
769 2 | 3 | arc 2 -> 3 | {"(2,3)"}
770 4 | 5 | arc 4 -> 5 | {"(4,5)"}
771 (7 rows)
773 explain (verbose, costs off)
774 with recursive search_graph(f, t, label) as (
775 select * from graph0 g
776 union all
777 select g.*
778 from graph0 g, search_graph sg
779 where g.f = sg.t
780 ) search breadth first by f, t set seq
781 select * from search_graph order by seq;
782 QUERY PLAN
783 -------------------------------------------------------------------------------------------------
784 Sort
785 Output: search_graph.f, search_graph.t, search_graph.label, search_graph.seq
786 Sort Key: search_graph.seq
787 CTE search_graph
788 -> Recursive Union
789 -> Seq Scan on pg_temp.graph0 g
790 Output: g.f, g.t, g.label, ROW('0'::bigint, g.f, g.t)
791 -> Merge Join
792 Output: g_1.f, g_1.t, g_1.label, ROW(int8inc((sg.seq)."*DEPTH*"), g_1.f, g_1.t)
793 Merge Cond: (g_1.f = sg.t)
794 -> Sort
795 Output: g_1.f, g_1.t, g_1.label
796 Sort Key: g_1.f
797 -> Seq Scan on pg_temp.graph0 g_1
798 Output: g_1.f, g_1.t, g_1.label
799 -> Sort
800 Output: sg.seq, sg.t
801 Sort Key: sg.t
802 -> WorkTable Scan on search_graph sg
803 Output: sg.seq, sg.t
804 -> CTE Scan on search_graph
805 Output: search_graph.f, search_graph.t, search_graph.label, search_graph.seq
806 (22 rows)
808 with recursive search_graph(f, t, label) as (
809 select * from graph0 g
810 union all
811 select g.*
812 from graph0 g, search_graph sg
813 where g.f = sg.t
814 ) search breadth first by f, t set seq
815 select * from search_graph order by seq;
816 f | t | label | seq
817 ---+---+------------+---------
818 1 | 2 | arc 1 -> 2 | (0,1,2)
819 1 | 3 | arc 1 -> 3 | (0,1,3)
820 1 | 4 | arc 1 -> 4 | (0,1,4)
821 2 | 3 | arc 2 -> 3 | (0,2,3)
822 4 | 5 | arc 4 -> 5 | (0,4,5)
823 2 | 3 | arc 2 -> 3 | (1,2,3)
824 4 | 5 | arc 4 -> 5 | (1,4,5)
825 (7 rows)
827 with recursive search_graph(f, t, label) as (
828 select * from graph0 g
829 union distinct
830 select g.*
831 from graph0 g, search_graph sg
832 where g.f = sg.t
833 ) search breadth first by f, t set seq
834 select * from search_graph order by seq;
835 f | t | label | seq
836 ---+---+------------+---------
837 1 | 2 | arc 1 -> 2 | (0,1,2)
838 1 | 3 | arc 1 -> 3 | (0,1,3)
839 1 | 4 | arc 1 -> 4 | (0,1,4)
840 2 | 3 | arc 2 -> 3 | (0,2,3)
841 4 | 5 | arc 4 -> 5 | (0,4,5)
842 2 | 3 | arc 2 -> 3 | (1,2,3)
843 4 | 5 | arc 4 -> 5 | (1,4,5)
844 (7 rows)
846 -- a constant initial value causes issues for EXPLAIN
847 explain (verbose, costs off)
848 with recursive test as (
849 select 1 as x
850 union all
851 select x + 1
852 from test
853 ) search depth first by x set y
854 select * from test limit 5;
855 QUERY PLAN
856 -----------------------------------------------------------------------------------------
857 Limit
858 Output: test.x, test.y
859 CTE test
860 -> Recursive Union
861 -> Result
862 Output: 1, '{(1)}'::record[]
863 -> WorkTable Scan on test test_1
864 Output: (test_1.x + 1), array_cat(test_1.y, ARRAY[ROW((test_1.x + 1))])
865 -> CTE Scan on test
866 Output: test.x, test.y
867 (10 rows)
869 with recursive test as (
870 select 1 as x
871 union all
872 select x + 1
873 from test
874 ) search depth first by x set y
875 select * from test limit 5;
876 x | y
877 ---+-----------------------
878 1 | {(1)}
879 2 | {(1),(2)}
880 3 | {(1),(2),(3)}
881 4 | {(1),(2),(3),(4)}
882 5 | {(1),(2),(3),(4),(5)}
883 (5 rows)
885 explain (verbose, costs off)
886 with recursive test as (
887 select 1 as x
888 union all
889 select x + 1
890 from test
891 ) search breadth first by x set y
892 select * from test limit 5;
893 QUERY PLAN
894 --------------------------------------------------------------------------------------------
895 Limit
896 Output: test.x, test.y
897 CTE test
898 -> Recursive Union
899 -> Result
900 Output: 1, '(0,1)'::record
901 -> WorkTable Scan on test test_1
902 Output: (test_1.x + 1), ROW(int8inc((test_1.y)."*DEPTH*"), (test_1.x + 1))
903 -> CTE Scan on test
904 Output: test.x, test.y
905 (10 rows)
907 with recursive test as (
908 select 1 as x
909 union all
910 select x + 1
911 from test
912 ) search breadth first by x set y
913 select * from test limit 5;
914 x | y
915 ---+-------
916 1 | (0,1)
917 2 | (1,2)
918 3 | (2,3)
919 4 | (3,4)
920 5 | (4,5)
921 (5 rows)
923 -- various syntax errors
924 with recursive search_graph(f, t, label) as (
925 select * from graph0 g
926 union all
927 select g.*
928 from graph0 g, search_graph sg
929 where g.f = sg.t
930 ) search depth first by foo, tar set seq
931 select * from search_graph;
932 ERROR: search column "foo" not in WITH query column list
933 LINE 7: ) search depth first by foo, tar set seq
934 ^
935 with recursive search_graph(f, t, label) as (
936 select * from graph0 g
937 union all
938 select g.*
939 from graph0 g, search_graph sg
940 where g.f = sg.t
941 ) search depth first by f, t set label
942 select * from search_graph;
943 ERROR: search sequence column name "label" already used in WITH query column list
944 LINE 7: ) search depth first by f, t set label
945 ^
946 with recursive search_graph(f, t, label) as (
947 select * from graph0 g
948 union all
949 select g.*
950 from graph0 g, search_graph sg
951 where g.f = sg.t
952 ) search depth first by f, t, f set seq
953 select * from search_graph;
954 ERROR: search column "f" specified more than once
955 LINE 7: ) search depth first by f, t, f set seq
956 ^
957 with recursive search_graph(f, t, label) as (
958 select * from graph0 g
959 union all
960 select * from graph0 g
961 union all
962 select g.*
963 from graph0 g, search_graph sg
964 where g.f = sg.t
965 ) search depth first by f, t set seq
966 select * from search_graph order by seq;
967 ERROR: with a SEARCH or CYCLE clause, the left side of the UNION must be a SELECT
968 with recursive search_graph(f, t, label) as (
969 select * from graph0 g
970 union all
971 (select * from graph0 g
972 union all
973 select g.*
974 from graph0 g, search_graph sg
975 where g.f = sg.t)
976 ) search depth first by f, t set seq
977 select * from search_graph order by seq;
978 ERROR: with a SEARCH or CYCLE clause, the right side of the UNION must be a SELECT
979 -- check that we distinguish same CTE name used at different levels
980 -- (this case could be supported, perhaps, but it isn't today)
981 with recursive x(col) as (
982 select 1
983 union
984 (with x as (select * from x)
985 select * from x)
986 ) search depth first by col set seq
987 select * from x;
988 ERROR: with a SEARCH or CYCLE clause, the recursive reference to WITH query "x" must be at the top level of its right-hand SELECT
989 -- test ruleutils and view expansion
990 create temp view v_search as
991 with recursive search_graph(f, t, label) as (
992 select * from graph0 g
993 union all
994 select g.*
995 from graph0 g, search_graph sg
996 where g.f = sg.t
997 ) search depth first by f, t set seq
998 select f, t, label from search_graph;
999 select pg_get_viewdef('v_search');
1000 pg_get_viewdef
1001 ------------------------------------------------
1002 WITH RECURSIVE search_graph(f, t, label) AS (+
1003 SELECT g.f, +
1004 g.t, +
1005 g.label +
1006 FROM graph0 g +
1007 UNION ALL +
1008 SELECT g.f, +
1009 g.t, +
1010 g.label +
1011 FROM graph0 g, +
1012 search_graph sg +
1013 WHERE (g.f = sg.t) +
1014 ) SEARCH DEPTH FIRST BY f, t SET seq +
1015 SELECT f, +
1016 t, +
1017 label +
1018 FROM search_graph;
1019 (1 row)
1021 select * from v_search;
1022 f | t | label
1023 ---+---+------------
1024 1 | 2 | arc 1 -> 2
1025 1 | 3 | arc 1 -> 3
1026 2 | 3 | arc 2 -> 3
1027 1 | 4 | arc 1 -> 4
1028 4 | 5 | arc 4 -> 5
1029 2 | 3 | arc 2 -> 3
1030 4 | 5 | arc 4 -> 5
1031 (7 rows)
1033 --
1034 -- test cycle detection
1035 --
1036 create temp table graph( f int, t int, label text );
1037 insert into graph values
1038 (1, 2, 'arc 1 -> 2'),
1039 (1, 3, 'arc 1 -> 3'),
1040 (2, 3, 'arc 2 -> 3'),
1041 (1, 4, 'arc 1 -> 4'),
1042 (4, 5, 'arc 4 -> 5'),
1043 (5, 1, 'arc 5 -> 1');
1044 with recursive search_graph(f, t, label, is_cycle, path) as (
1045 select *, false, array[row(g.f, g.t)] from graph g
1046 union all
1047 select g.*, row(g.f, g.t) = any(path), path || row(g.f, g.t)
1048 from graph g, search_graph sg
1049 where g.f = sg.t and not is_cycle
1050 )
1051 select * from search_graph;
1052 f | t | label | is_cycle | path
1053 ---+---+------------+----------+-------------------------------------------
1054 1 | 2 | arc 1 -> 2 | f | {"(1,2)"}
1055 1 | 3 | arc 1 -> 3 | f | {"(1,3)"}
1056 2 | 3 | arc 2 -> 3 | f | {"(2,3)"}
1057 1 | 4 | arc 1 -> 4 | f | {"(1,4)"}
1058 4 | 5 | arc 4 -> 5 | f | {"(4,5)"}
1059 5 | 1 | arc 5 -> 1 | f | {"(5,1)"}
1060 1 | 2 | arc 1 -> 2 | f | {"(5,1)","(1,2)"}
1061 1 | 3 | arc 1 -> 3 | f | {"(5,1)","(1,3)"}
1062 1 | 4 | arc 1 -> 4 | f | {"(5,1)","(1,4)"}
1063 2 | 3 | arc 2 -> 3 | f | {"(1,2)","(2,3)"}
1064 4 | 5 | arc 4 -> 5 | f | {"(1,4)","(4,5)"}
1065 5 | 1 | arc 5 -> 1 | f | {"(4,5)","(5,1)"}
1066 1 | 2 | arc 1 -> 2 | f | {"(4,5)","(5,1)","(1,2)"}
1067 1 | 3 | arc 1 -> 3 | f | {"(4,5)","(5,1)","(1,3)"}
1068 1 | 4 | arc 1 -> 4 | f | {"(4,5)","(5,1)","(1,4)"}
1069 2 | 3 | arc 2 -> 3 | f | {"(5,1)","(1,2)","(2,3)"}
1070 4 | 5 | arc 4 -> 5 | f | {"(5,1)","(1,4)","(4,5)"}
1071 5 | 1 | arc 5 -> 1 | f | {"(1,4)","(4,5)","(5,1)"}
1072 1 | 2 | arc 1 -> 2 | f | {"(1,4)","(4,5)","(5,1)","(1,2)"}
1073 1 | 3 | arc 1 -> 3 | f | {"(1,4)","(4,5)","(5,1)","(1,3)"}
1074 1 | 4 | arc 1 -> 4 | t | {"(1,4)","(4,5)","(5,1)","(1,4)"}
1075 2 | 3 | arc 2 -> 3 | f | {"(4,5)","(5,1)","(1,2)","(2,3)"}
1076 4 | 5 | arc 4 -> 5 | t | {"(4,5)","(5,1)","(1,4)","(4,5)"}
1077 5 | 1 | arc 5 -> 1 | t | {"(5,1)","(1,4)","(4,5)","(5,1)"}
1078 2 | 3 | arc 2 -> 3 | f | {"(1,4)","(4,5)","(5,1)","(1,2)","(2,3)"}
1079 (25 rows)
1081 -- UNION DISTINCT exercises row type hashing support
1082 with recursive search_graph(f, t, label, is_cycle, path) as (
1083 select *, false, array[row(g.f, g.t)] from graph g
1084 union distinct
1085 select g.*, row(g.f, g.t) = any(path), path || row(g.f, g.t)
1086 from graph g, search_graph sg
1087 where g.f = sg.t and not is_cycle
1088 )
1089 select * from search_graph;
1090 f | t | label | is_cycle | path
1091 ---+---+------------+----------+-------------------------------------------
1092 1 | 2 | arc 1 -> 2 | f | {"(1,2)"}
1093 1 | 3 | arc 1 -> 3 | f | {"(1,3)"}
1094 2 | 3 | arc 2 -> 3 | f | {"(2,3)"}
1095 1 | 4 | arc 1 -> 4 | f | {"(1,4)"}
1096 4 | 5 | arc 4 -> 5 | f | {"(4,5)"}
1097 5 | 1 | arc 5 -> 1 | f | {"(5,1)"}
1098 1 | 2 | arc 1 -> 2 | f | {"(5,1)","(1,2)"}
1099 1 | 3 | arc 1 -> 3 | f | {"(5,1)","(1,3)"}
1100 1 | 4 | arc 1 -> 4 | f | {"(5,1)","(1,4)"}
1101 2 | 3 | arc 2 -> 3 | f | {"(1,2)","(2,3)"}
1102 4 | 5 | arc 4 -> 5 | f | {"(1,4)","(4,5)"}
1103 5 | 1 | arc 5 -> 1 | f | {"(4,5)","(5,1)"}
1104 1 | 2 | arc 1 -> 2 | f | {"(4,5)","(5,1)","(1,2)"}
1105 1 | 3 | arc 1 -> 3 | f | {"(4,5)","(5,1)","(1,3)"}
1106 1 | 4 | arc 1 -> 4 | f | {"(4,5)","(5,1)","(1,4)"}
1107 2 | 3 | arc 2 -> 3 | f | {"(5,1)","(1,2)","(2,3)"}
1108 4 | 5 | arc 4 -> 5 | f | {"(5,1)","(1,4)","(4,5)"}
1109 5 | 1 | arc 5 -> 1 | f | {"(1,4)","(4,5)","(5,1)"}
1110 1 | 2 | arc 1 -> 2 | f | {"(1,4)","(4,5)","(5,1)","(1,2)"}
1111 1 | 3 | arc 1 -> 3 | f | {"(1,4)","(4,5)","(5,1)","(1,3)"}
1112 1 | 4 | arc 1 -> 4 | t | {"(1,4)","(4,5)","(5,1)","(1,4)"}
1113 2 | 3 | arc 2 -> 3 | f | {"(4,5)","(5,1)","(1,2)","(2,3)"}
1114 4 | 5 | arc 4 -> 5 | t | {"(4,5)","(5,1)","(1,4)","(4,5)"}
1115 5 | 1 | arc 5 -> 1 | t | {"(5,1)","(1,4)","(4,5)","(5,1)"}
1116 2 | 3 | arc 2 -> 3 | f | {"(1,4)","(4,5)","(5,1)","(1,2)","(2,3)"}
1117 (25 rows)
1119 -- ordering by the path column has same effect as SEARCH DEPTH FIRST
1120 with recursive search_graph(f, t, label, is_cycle, path) as (
1121 select *, false, array[row(g.f, g.t)] from graph g
1122 union all
1123 select g.*, row(g.f, g.t) = any(path), path || row(g.f, g.t)
1124 from graph g, search_graph sg
1125 where g.f = sg.t and not is_cycle
1126 )
1127 select * from search_graph order by path;
1128 f | t | label | is_cycle | path
1129 ---+---+------------+----------+-------------------------------------------
1130 1 | 2 | arc 1 -> 2 | f | {"(1,2)"}
1131 2 | 3 | arc 2 -> 3 | f | {"(1,2)","(2,3)"}
1132 1 | 3 | arc 1 -> 3 | f | {"(1,3)"}
1133 1 | 4 | arc 1 -> 4 | f | {"(1,4)"}
1134 4 | 5 | arc 4 -> 5 | f | {"(1,4)","(4,5)"}
1135 5 | 1 | arc 5 -> 1 | f | {"(1,4)","(4,5)","(5,1)"}
1136 1 | 2 | arc 1 -> 2 | f | {"(1,4)","(4,5)","(5,1)","(1,2)"}
1137 2 | 3 | arc 2 -> 3 | f | {"(1,4)","(4,5)","(5,1)","(1,2)","(2,3)"}
1138 1 | 3 | arc 1 -> 3 | f | {"(1,4)","(4,5)","(5,1)","(1,3)"}
1139 1 | 4 | arc 1 -> 4 | t | {"(1,4)","(4,5)","(5,1)","(1,4)"}
1140 2 | 3 | arc 2 -> 3 | f | {"(2,3)"}
1141 4 | 5 | arc 4 -> 5 | f | {"(4,5)"}
1142 5 | 1 | arc 5 -> 1 | f | {"(4,5)","(5,1)"}
1143 1 | 2 | arc 1 -> 2 | f | {"(4,5)","(5,1)","(1,2)"}
1144 2 | 3 | arc 2 -> 3 | f | {"(4,5)","(5,1)","(1,2)","(2,3)"}
1145 1 | 3 | arc 1 -> 3 | f | {"(4,5)","(5,1)","(1,3)"}
1146 1 | 4 | arc 1 -> 4 | f | {"(4,5)","(5,1)","(1,4)"}
1147 4 | 5 | arc 4 -> 5 | t | {"(4,5)","(5,1)","(1,4)","(4,5)"}
1148 5 | 1 | arc 5 -> 1 | f | {"(5,1)"}
1149 1 | 2 | arc 1 -> 2 | f | {"(5,1)","(1,2)"}
1150 2 | 3 | arc 2 -> 3 | f | {"(5,1)","(1,2)","(2,3)"}
1151 1 | 3 | arc 1 -> 3 | f | {"(5,1)","(1,3)"}
1152 1 | 4 | arc 1 -> 4 | f | {"(5,1)","(1,4)"}
1153 4 | 5 | arc 4 -> 5 | f | {"(5,1)","(1,4)","(4,5)"}
1154 5 | 1 | arc 5 -> 1 | t | {"(5,1)","(1,4)","(4,5)","(5,1)"}
1155 (25 rows)
1157 -- CYCLE clause
1158 explain (verbose, costs off)
1159 with recursive search_graph(f, t, label) as (
1160 select * from graph g
1161 union all
1162 select g.*
1163 from graph g, search_graph sg
1164 where g.f = sg.t
1165 ) cycle f, t set is_cycle using path
1166 select * from search_graph;
1167 QUERY PLAN
1168 -----------------------------------------------------------------------------------------------------------------------------------------------------------------------
1169 CTE Scan on search_graph
1170 Output: search_graph.f, search_graph.t, search_graph.label, search_graph.is_cycle, search_graph.path
1171 CTE search_graph
1172 -> Recursive Union
1173 -> Seq Scan on pg_temp.graph g
1174 Output: g.f, g.t, g.label, false, ARRAY[ROW(g.f, g.t)]
1175 -> Merge Join
1176 Output: g_1.f, g_1.t, g_1.label, CASE WHEN (ROW(g_1.f, g_1.t) = ANY (sg.path)) THEN true ELSE false END, array_cat(sg.path, ARRAY[ROW(g_1.f, g_1.t)])
1177 Merge Cond: (g_1.f = sg.t)
1178 -> Sort
1179 Output: g_1.f, g_1.t, g_1.label
1180 Sort Key: g_1.f
1181 -> Seq Scan on pg_temp.graph g_1
1182 Output: g_1.f, g_1.t, g_1.label
1183 -> Sort
1184 Output: sg.path, sg.t
1185 Sort Key: sg.t
1186 -> WorkTable Scan on search_graph sg
1187 Output: sg.path, sg.t
1188 Filter: (NOT sg.is_cycle)
1189 (20 rows)
1191 with recursive search_graph(f, t, label) as (
1192 select * from graph g
1193 union all
1194 select g.*
1195 from graph g, search_graph sg
1196 where g.f = sg.t
1197 ) cycle f, t set is_cycle using path
1198 select * from search_graph;
1199 f | t | label | is_cycle | path
1200 ---+---+------------+----------+-------------------------------------------
1201 1 | 2 | arc 1 -> 2 | f | {"(1,2)"}
1202 1 | 3 | arc 1 -> 3 | f | {"(1,3)"}
1203 2 | 3 | arc 2 -> 3 | f | {"(2,3)"}
1204 1 | 4 | arc 1 -> 4 | f | {"(1,4)"}
1205 4 | 5 | arc 4 -> 5 | f | {"(4,5)"}
1206 5 | 1 | arc 5 -> 1 | f | {"(5,1)"}
1207 1 | 2 | arc 1 -> 2 | f | {"(5,1)","(1,2)"}
1208 1 | 3 | arc 1 -> 3 | f | {"(5,1)","(1,3)"}
1209 1 | 4 | arc 1 -> 4 | f | {"(5,1)","(1,4)"}
1210 2 | 3 | arc 2 -> 3 | f | {"(1,2)","(2,3)"}
1211 4 | 5 | arc 4 -> 5 | f | {"(1,4)","(4,5)"}
1212 5 | 1 | arc 5 -> 1 | f | {"(4,5)","(5,1)"}
1213 1 | 2 | arc 1 -> 2 | f | {"(4,5)","(5,1)","(1,2)"}
1214 1 | 3 | arc 1 -> 3 | f | {"(4,5)","(5,1)","(1,3)"}
1215 1 | 4 | arc 1 -> 4 | f | {"(4,5)","(5,1)","(1,4)"}
1216 2 | 3 | arc 2 -> 3 | f | {"(5,1)","(1,2)","(2,3)"}
1217 4 | 5 | arc 4 -> 5 | f | {"(5,1)","(1,4)","(4,5)"}
1218 5 | 1 | arc 5 -> 1 | f | {"(1,4)","(4,5)","(5,1)"}
1219 1 | 2 | arc 1 -> 2 | f | {"(1,4)","(4,5)","(5,1)","(1,2)"}
1220 1 | 3 | arc 1 -> 3 | f | {"(1,4)","(4,5)","(5,1)","(1,3)"}
1221 1 | 4 | arc 1 -> 4 | t | {"(1,4)","(4,5)","(5,1)","(1,4)"}
1222 2 | 3 | arc 2 -> 3 | f | {"(4,5)","(5,1)","(1,2)","(2,3)"}
1223 4 | 5 | arc 4 -> 5 | t | {"(4,5)","(5,1)","(1,4)","(4,5)"}
1224 5 | 1 | arc 5 -> 1 | t | {"(5,1)","(1,4)","(4,5)","(5,1)"}
1225 2 | 3 | arc 2 -> 3 | f | {"(1,4)","(4,5)","(5,1)","(1,2)","(2,3)"}
1226 (25 rows)
1228 with recursive search_graph(f, t, label) as (
1229 select * from graph g
1230 union distinct
1231 select g.*
1232 from graph g, search_graph sg
1233 where g.f = sg.t
1234 ) cycle f, t set is_cycle to 'Y' default 'N' using path
1235 select * from search_graph;
1236 f | t | label | is_cycle | path
1237 ---+---+------------+----------+-------------------------------------------
1238 1 | 2 | arc 1 -> 2 | N | {"(1,2)"}
1239 1 | 3 | arc 1 -> 3 | N | {"(1,3)"}
1240 2 | 3 | arc 2 -> 3 | N | {"(2,3)"}
1241 1 | 4 | arc 1 -> 4 | N | {"(1,4)"}
1242 4 | 5 | arc 4 -> 5 | N | {"(4,5)"}
1243 5 | 1 | arc 5 -> 1 | N | {"(5,1)"}
1244 1 | 2 | arc 1 -> 2 | N | {"(5,1)","(1,2)"}
1245 1 | 3 | arc 1 -> 3 | N | {"(5,1)","(1,3)"}
1246 1 | 4 | arc 1 -> 4 | N | {"(5,1)","(1,4)"}
1247 2 | 3 | arc 2 -> 3 | N | {"(1,2)","(2,3)"}
1248 4 | 5 | arc 4 -> 5 | N | {"(1,4)","(4,5)"}
1249 5 | 1 | arc 5 -> 1 | N | {"(4,5)","(5,1)"}
1250 1 | 2 | arc 1 -> 2 | N | {"(4,5)","(5,1)","(1,2)"}
1251 1 | 3 | arc 1 -> 3 | N | {"(4,5)","(5,1)","(1,3)"}
1252 1 | 4 | arc 1 -> 4 | N | {"(4,5)","(5,1)","(1,4)"}
1253 2 | 3 | arc 2 -> 3 | N | {"(5,1)","(1,2)","(2,3)"}
1254 4 | 5 | arc 4 -> 5 | N | {"(5,1)","(1,4)","(4,5)"}
1255 5 | 1 | arc 5 -> 1 | N | {"(1,4)","(4,5)","(5,1)"}
1256 1 | 2 | arc 1 -> 2 | N | {"(1,4)","(4,5)","(5,1)","(1,2)"}
1257 1 | 3 | arc 1 -> 3 | N | {"(1,4)","(4,5)","(5,1)","(1,3)"}
1258 1 | 4 | arc 1 -> 4 | Y | {"(1,4)","(4,5)","(5,1)","(1,4)"}
1259 2 | 3 | arc 2 -> 3 | N | {"(4,5)","(5,1)","(1,2)","(2,3)"}
1260 4 | 5 | arc 4 -> 5 | Y | {"(4,5)","(5,1)","(1,4)","(4,5)"}
1261 5 | 1 | arc 5 -> 1 | Y | {"(5,1)","(1,4)","(4,5)","(5,1)"}
1262 2 | 3 | arc 2 -> 3 | N | {"(1,4)","(4,5)","(5,1)","(1,2)","(2,3)"}
1263 (25 rows)
1265 explain (verbose, costs off)
1266 with recursive test as (
1267 select 0 as x
1268 union all
1269 select (x + 1) % 10
1270 from test
1271 ) cycle x set is_cycle using path
1272 select * from test;
1273 QUERY PLAN
1274 -----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
1275 CTE Scan on test
1276 Output: test.x, test.is_cycle, test.path
1277 CTE test
1278 -> Recursive Union
1279 -> Result
1280 Output: 0, false, '{(0)}'::record[]
1281 -> WorkTable Scan on test test_1
1282 Output: ((test_1.x + 1) % 10), CASE WHEN (ROW(((test_1.x + 1) % 10)) = ANY (test_1.path)) THEN true ELSE false END, array_cat(test_1.path, ARRAY[ROW(((test_1.x + 1) % 10))])
1283 Filter: (NOT test_1.is_cycle)
1284 (9 rows)
1286 with recursive test as (
1287 select 0 as x
1288 union all
1289 select (x + 1) % 10
1290 from test
1291 ) cycle x set is_cycle using path
1292 select * from test;
1293 x | is_cycle | path
1294 ---+----------+-----------------------------------------------
1295 0 | f | {(0)}
1296 1 | f | {(0),(1)}
1297 2 | f | {(0),(1),(2)}
1298 3 | f | {(0),(1),(2),(3)}
1299 4 | f | {(0),(1),(2),(3),(4)}
1300 5 | f | {(0),(1),(2),(3),(4),(5)}
1301 6 | f | {(0),(1),(2),(3),(4),(5),(6)}
1302 7 | f | {(0),(1),(2),(3),(4),(5),(6),(7)}
1303 8 | f | {(0),(1),(2),(3),(4),(5),(6),(7),(8)}
1304 9 | f | {(0),(1),(2),(3),(4),(5),(6),(7),(8),(9)}
1305 0 | t | {(0),(1),(2),(3),(4),(5),(6),(7),(8),(9),(0)}
1306 (11 rows)
1308 with recursive test as (
1309 select 0 as x
1310 union all
1311 select (x + 1) % 10
1312 from test
1313 where not is_cycle -- redundant, but legal
1314 ) cycle x set is_cycle using path
1315 select * from test;
1316 x | is_cycle | path
1317 ---+----------+-----------------------------------------------
1318 0 | f | {(0)}
1319 1 | f | {(0),(1)}
1320 2 | f | {(0),(1),(2)}
1321 3 | f | {(0),(1),(2),(3)}
1322 4 | f | {(0),(1),(2),(3),(4)}
1323 5 | f | {(0),(1),(2),(3),(4),(5)}
1324 6 | f | {(0),(1),(2),(3),(4),(5),(6)}
1325 7 | f | {(0),(1),(2),(3),(4),(5),(6),(7)}
1326 8 | f | {(0),(1),(2),(3),(4),(5),(6),(7),(8)}
1327 9 | f | {(0),(1),(2),(3),(4),(5),(6),(7),(8),(9)}
1328 0 | t | {(0),(1),(2),(3),(4),(5),(6),(7),(8),(9),(0)}
1329 (11 rows)
1331 -- multiple CTEs
1332 with recursive
1333 graph(f, t, label) as (
1334 values (1, 2, 'arc 1 -> 2'),
1335 (1, 3, 'arc 1 -> 3'),
1336 (2, 3, 'arc 2 -> 3'),
1337 (1, 4, 'arc 1 -> 4'),
1338 (4, 5, 'arc 4 -> 5'),
1339 (5, 1, 'arc 5 -> 1')
1340 ),
1341 search_graph(f, t, label) as (
1342 select * from graph g
1343 union all
1344 select g.*
1345 from graph g, search_graph sg
1346 where g.f = sg.t
1347 ) cycle f, t set is_cycle to true default false using path
1348 select f, t, label from search_graph;
1349 f | t | label
1350 ---+---+------------
1351 1 | 2 | arc 1 -> 2
1352 1 | 3 | arc 1 -> 3
1353 2 | 3 | arc 2 -> 3
1354 1 | 4 | arc 1 -> 4
1355 4 | 5 | arc 4 -> 5
1356 5 | 1 | arc 5 -> 1
1357 2 | 3 | arc 2 -> 3
1358 4 | 5 | arc 4 -> 5
1359 5 | 1 | arc 5 -> 1
1360 1 | 4 | arc 1 -> 4
1361 1 | 3 | arc 1 -> 3
1362 1 | 2 | arc 1 -> 2
1363 5 | 1 | arc 5 -> 1
1364 1 | 4 | arc 1 -> 4
1365 1 | 3 | arc 1 -> 3
1366 1 | 2 | arc 1 -> 2
1367 4 | 5 | arc 4 -> 5
1368 2 | 3 | arc 2 -> 3
1369 1 | 4 | arc 1 -> 4
1370 1 | 3 | arc 1 -> 3
1371 1 | 2 | arc 1 -> 2
1372 4 | 5 | arc 4 -> 5
1373 2 | 3 | arc 2 -> 3
1374 5 | 1 | arc 5 -> 1
1375 2 | 3 | arc 2 -> 3
1376 (25 rows)
1378 -- star expansion
1379 with recursive a as (
1380 select 1 as b
1381 union all
1382 select * from a
1383 ) cycle b set c using p
1384 select * from a;
1385 b | c | p
1386 ---+---+-----------
1387 1 | f | {(1)}
1388 1 | t | {(1),(1)}
1389 (2 rows)
1391 -- search+cycle
1392 with recursive search_graph(f, t, label) as (
1393 select * from graph g
1394 union all
1395 select g.*
1396 from graph g, search_graph sg
1397 where g.f = sg.t
1398 ) search depth first by f, t set seq
1399 cycle f, t set is_cycle using path
1400 select * from search_graph;
1401 f | t | label | seq | is_cycle | path
1402 ---+---+------------+-------------------------------------------+----------+-------------------------------------------
1403 1 | 2 | arc 1 -> 2 | {"(1,2)"} | f | {"(1,2)"}
1404 1 | 3 | arc 1 -> 3 | {"(1,3)"} | f | {"(1,3)"}
1405 2 | 3 | arc 2 -> 3 | {"(2,3)"} | f | {"(2,3)"}
1406 1 | 4 | arc 1 -> 4 | {"(1,4)"} | f | {"(1,4)"}
1407 4 | 5 | arc 4 -> 5 | {"(4,5)"} | f | {"(4,5)"}
1408 5 | 1 | arc 5 -> 1 | {"(5,1)"} | f | {"(5,1)"}
1409 1 | 2 | arc 1 -> 2 | {"(5,1)","(1,2)"} | f | {"(5,1)","(1,2)"}
1410 1 | 3 | arc 1 -> 3 | {"(5,1)","(1,3)"} | f | {"(5,1)","(1,3)"}
1411 1 | 4 | arc 1 -> 4 | {"(5,1)","(1,4)"} | f | {"(5,1)","(1,4)"}
1412 2 | 3 | arc 2 -> 3 | {"(1,2)","(2,3)"} | f | {"(1,2)","(2,3)"}
1413 4 | 5 | arc 4 -> 5 | {"(1,4)","(4,5)"} | f | {"(1,4)","(4,5)"}
1414 5 | 1 | arc 5 -> 1 | {"(4,5)","(5,1)"} | f | {"(4,5)","(5,1)"}
1415 1 | 2 | arc 1 -> 2 | {"(4,5)","(5,1)","(1,2)"} | f | {"(4,5)","(5,1)","(1,2)"}
1416 1 | 3 | arc 1 -> 3 | {"(4,5)","(5,1)","(1,3)"} | f | {"(4,5)","(5,1)","(1,3)"}
1417 1 | 4 | arc 1 -> 4 | {"(4,5)","(5,1)","(1,4)"} | f | {"(4,5)","(5,1)","(1,4)"}
1418 2 | 3 | arc 2 -> 3 | {"(5,1)","(1,2)","(2,3)"} | f | {"(5,1)","(1,2)","(2,3)"}
1419 4 | 5 | arc 4 -> 5 | {"(5,1)","(1,4)","(4,5)"} | f | {"(5,1)","(1,4)","(4,5)"}
1420 5 | 1 | arc 5 -> 1 | {"(1,4)","(4,5)","(5,1)"} | f | {"(1,4)","(4,5)","(5,1)"}
1421 1 | 2 | arc 1 -> 2 | {"(1,4)","(4,5)","(5,1)","(1,2)"} | f | {"(1,4)","(4,5)","(5,1)","(1,2)"}
1422 1 | 3 | arc 1 -> 3 | {"(1,4)","(4,5)","(5,1)","(1,3)"} | f | {"(1,4)","(4,5)","(5,1)","(1,3)"}
1423 1 | 4 | arc 1 -> 4 | {"(1,4)","(4,5)","(5,1)","(1,4)"} | t | {"(1,4)","(4,5)","(5,1)","(1,4)"}
1424 2 | 3 | arc 2 -> 3 | {"(4,5)","(5,1)","(1,2)","(2,3)"} | f | {"(4,5)","(5,1)","(1,2)","(2,3)"}
1425 4 | 5 | arc 4 -> 5 | {"(4,5)","(5,1)","(1,4)","(4,5)"} | t | {"(4,5)","(5,1)","(1,4)","(4,5)"}
1426 5 | 1 | arc 5 -> 1 | {"(5,1)","(1,4)","(4,5)","(5,1)"} | t | {"(5,1)","(1,4)","(4,5)","(5,1)"}
1427 2 | 3 | arc 2 -> 3 | {"(1,4)","(4,5)","(5,1)","(1,2)","(2,3)"} | f | {"(1,4)","(4,5)","(5,1)","(1,2)","(2,3)"}
1428 (25 rows)
1430 with recursive search_graph(f, t, label) as (
1431 select * from graph g
1432 union all
1433 select g.*
1434 from graph g, search_graph sg
1435 where g.f = sg.t
1436 ) search breadth first by f, t set seq
1437 cycle f, t set is_cycle using path
1438 select * from search_graph;
1439 f | t | label | seq | is_cycle | path
1440 ---+---+------------+---------+----------+-------------------------------------------
1441 1 | 2 | arc 1 -> 2 | (0,1,2) | f | {"(1,2)"}
1442 1 | 3 | arc 1 -> 3 | (0,1,3) | f | {"(1,3)"}
1443 2 | 3 | arc 2 -> 3 | (0,2,3) | f | {"(2,3)"}
1444 1 | 4 | arc 1 -> 4 | (0,1,4) | f | {"(1,4)"}
1445 4 | 5 | arc 4 -> 5 | (0,4,5) | f | {"(4,5)"}
1446 5 | 1 | arc 5 -> 1 | (0,5,1) | f | {"(5,1)"}
1447 1 | 2 | arc 1 -> 2 | (1,1,2) | f | {"(5,1)","(1,2)"}
1448 1 | 3 | arc 1 -> 3 | (1,1,3) | f | {"(5,1)","(1,3)"}
1449 1 | 4 | arc 1 -> 4 | (1,1,4) | f | {"(5,1)","(1,4)"}
1450 2 | 3 | arc 2 -> 3 | (1,2,3) | f | {"(1,2)","(2,3)"}
1451 4 | 5 | arc 4 -> 5 | (1,4,5) | f | {"(1,4)","(4,5)"}
1452 5 | 1 | arc 5 -> 1 | (1,5,1) | f | {"(4,5)","(5,1)"}
1453 1 | 2 | arc 1 -> 2 | (2,1,2) | f | {"(4,5)","(5,1)","(1,2)"}
1454 1 | 3 | arc 1 -> 3 | (2,1,3) | f | {"(4,5)","(5,1)","(1,3)"}
1455 1 | 4 | arc 1 -> 4 | (2,1,4) | f | {"(4,5)","(5,1)","(1,4)"}
1456 2 | 3 | arc 2 -> 3 | (2,2,3) | f | {"(5,1)","(1,2)","(2,3)"}
1457 4 | 5 | arc 4 -> 5 | (2,4,5) | f | {"(5,1)","(1,4)","(4,5)"}
1458 5 | 1 | arc 5 -> 1 | (2,5,1) | f | {"(1,4)","(4,5)","(5,1)"}
1459 1 | 2 | arc 1 -> 2 | (3,1,2) | f | {"(1,4)","(4,5)","(5,1)","(1,2)"}
1460 1 | 3 | arc 1 -> 3 | (3,1,3) | f | {"(1,4)","(4,5)","(5,1)","(1,3)"}
1461 1 | 4 | arc 1 -> 4 | (3,1,4) | t | {"(1,4)","(4,5)","(5,1)","(1,4)"}
1462 2 | 3 | arc 2 -> 3 | (3,2,3) | f | {"(4,5)","(5,1)","(1,2)","(2,3)"}
1463 4 | 5 | arc 4 -> 5 | (3,4,5) | t | {"(4,5)","(5,1)","(1,4)","(4,5)"}
1464 5 | 1 | arc 5 -> 1 | (3,5,1) | t | {"(5,1)","(1,4)","(4,5)","(5,1)"}
1465 2 | 3 | arc 2 -> 3 | (4,2,3) | f | {"(1,4)","(4,5)","(5,1)","(1,2)","(2,3)"}
1466 (25 rows)
1468 -- various syntax errors
1469 with recursive search_graph(f, t, label) as (
1470 select * from graph g
1471 union all
1472 select g.*
1473 from graph g, search_graph sg
1474 where g.f = sg.t
1475 ) cycle foo, tar set is_cycle using path
1476 select * from search_graph;
1477 ERROR: cycle column "foo" not in WITH query column list
1478 LINE 7: ) cycle foo, tar set is_cycle using path
1479 ^
1480 with recursive search_graph(f, t, label) as (
1481 select * from graph g
1482 union all
1483 select g.*
1484 from graph g, search_graph sg
1485 where g.f = sg.t
1486 ) cycle f, t set is_cycle to true default 55 using path
1487 select * from search_graph;
1488 ERROR: CYCLE types boolean and integer cannot be matched
1489 LINE 7: ) cycle f, t set is_cycle to true default 55 using path
1490 ^
1491 with recursive search_graph(f, t, label) as (
1492 select * from graph g
1493 union all
1494 select g.*
1495 from graph g, search_graph sg
1496 where g.f = sg.t
1497 ) cycle f, t set is_cycle to point '(1,1)' default point '(0,0)' using path
1498 select * from search_graph;
1499 ERROR: could not identify an equality operator for type point
1500 with recursive search_graph(f, t, label) as (
1501 select * from graph g
1502 union all
1503 select g.*
1504 from graph g, search_graph sg
1505 where g.f = sg.t
1506 ) cycle f, t set label to true default false using path
1507 select * from search_graph;
1508 ERROR: cycle mark column name "label" already used in WITH query column list
1509 LINE 7: ) cycle f, t set label to true default false using path
1510 ^
1511 with recursive search_graph(f, t, label) as (
1512 select * from graph g
1513 union all
1514 select g.*
1515 from graph g, search_graph sg
1516 where g.f = sg.t
1517 ) cycle f, t set is_cycle to true default false using label
1518 select * from search_graph;
1519 ERROR: cycle path column name "label" already used in WITH query column list
1520 LINE 7: ) cycle f, t set is_cycle to true default false using label
1521 ^
1522 with recursive search_graph(f, t, label) as (
1523 select * from graph g
1524 union all
1525 select g.*
1526 from graph g, search_graph sg
1527 where g.f = sg.t
1528 ) cycle f, t set foo to true default false using foo
1529 select * from search_graph;
1530 ERROR: cycle mark column name and cycle path column name are the same
1531 LINE 7: ) cycle f, t set foo to true default false using foo
1532 ^
1533 with recursive search_graph(f, t, label) as (
1534 select * from graph g
1535 union all
1536 select g.*
1537 from graph g, search_graph sg
1538 where g.f = sg.t
1539 ) cycle f, t, f set is_cycle to true default false using path
1540 select * from search_graph;
1541 ERROR: cycle column "f" specified more than once
1542 LINE 7: ) cycle f, t, f set is_cycle to true default false using pat...
1543 ^
1544 with recursive search_graph(f, t, label) as (
1545 select * from graph g
1546 union all
1547 select g.*
1548 from graph g, search_graph sg
1549 where g.f = sg.t
1550 ) search depth first by f, t set foo
1551 cycle f, t set foo to true default false using path
1552 select * from search_graph;
1553 ERROR: search sequence column name and cycle mark column name are the same
1554 LINE 7: ) search depth first by f, t set foo
1555 ^
1556 with recursive search_graph(f, t, label) as (
1557 select * from graph g
1558 union all
1559 select g.*
1560 from graph g, search_graph sg
1561 where g.f = sg.t
1562 ) search depth first by f, t set foo
1563 cycle f, t set is_cycle to true default false using foo
1564 select * from search_graph;
1565 ERROR: search sequence column name and cycle path column name are the same
1566 LINE 7: ) search depth first by f, t set foo
1567 ^
1568 -- test ruleutils and view expansion
1569 create temp view v_cycle1 as
1570 with recursive search_graph(f, t, label) as (
1571 select * from graph g
1572 union all
1573 select g.*
1574 from graph g, search_graph sg
1575 where g.f = sg.t
1576 ) cycle f, t set is_cycle using path
1577 select f, t, label from search_graph;
1578 create temp view v_cycle2 as
1579 with recursive search_graph(f, t, label) as (
1580 select * from graph g
1581 union all
1582 select g.*
1583 from graph g, search_graph sg
1584 where g.f = sg.t
1585 ) cycle f, t set is_cycle to 'Y' default 'N' using path
1586 select f, t, label from search_graph;
1587 select pg_get_viewdef('v_cycle1');
1588 pg_get_viewdef
1589 ------------------------------------------------
1590 WITH RECURSIVE search_graph(f, t, label) AS (+
1591 SELECT g.f, +
1592 g.t, +
1593 g.label +
1594 FROM graph g +
1595 UNION ALL +
1596 SELECT g.f, +
1597 g.t, +
1598 g.label +
1599 FROM graph g, +
1600 search_graph sg +
1601 WHERE (g.f = sg.t) +
1602 ) CYCLE f, t SET is_cycle USING path +
1603 SELECT f, +
1604 t, +
1605 label +
1606 FROM search_graph;
1607 (1 row)
1609 select pg_get_viewdef('v_cycle2');
1610 pg_get_viewdef
1611 -----------------------------------------------------------------------------
1612 WITH RECURSIVE search_graph(f, t, label) AS ( +
1613 SELECT g.f, +
1614 g.t, +
1615 g.label +
1616 FROM graph g +
1617 UNION ALL +
1618 SELECT g.f, +
1619 g.t, +
1620 g.label +
1621 FROM graph g, +
1622 search_graph sg +
1623 WHERE (g.f = sg.t) +
1624 ) CYCLE f, t SET is_cycle TO 'Y'::text DEFAULT 'N'::text USING path+
1625 SELECT f, +
1626 t, +
1627 label +
1628 FROM search_graph;
1629 (1 row)
1631 select * from v_cycle1;
1632 f | t | label
1633 ---+---+------------
1634 1 | 2 | arc 1 -> 2
1635 1 | 3 | arc 1 -> 3
1636 2 | 3 | arc 2 -> 3
1637 1 | 4 | arc 1 -> 4
1638 4 | 5 | arc 4 -> 5
1639 5 | 1 | arc 5 -> 1
1640 1 | 2 | arc 1 -> 2
1641 1 | 3 | arc 1 -> 3
1642 1 | 4 | arc 1 -> 4
1643 2 | 3 | arc 2 -> 3
1644 4 | 5 | arc 4 -> 5
1645 5 | 1 | arc 5 -> 1
1646 1 | 2 | arc 1 -> 2
1647 1 | 3 | arc 1 -> 3
1648 1 | 4 | arc 1 -> 4
1649 2 | 3 | arc 2 -> 3
1650 4 | 5 | arc 4 -> 5
1651 5 | 1 | arc 5 -> 1
1652 1 | 2 | arc 1 -> 2
1653 1 | 3 | arc 1 -> 3
1654 1 | 4 | arc 1 -> 4
1655 2 | 3 | arc 2 -> 3
1656 4 | 5 | arc 4 -> 5
1657 5 | 1 | arc 5 -> 1
1658 2 | 3 | arc 2 -> 3
1659 (25 rows)
1661 select * from v_cycle2;
1662 f | t | label
1663 ---+---+------------
1664 1 | 2 | arc 1 -> 2
1665 1 | 3 | arc 1 -> 3
1666 2 | 3 | arc 2 -> 3
1667 1 | 4 | arc 1 -> 4
1668 4 | 5 | arc 4 -> 5
1669 5 | 1 | arc 5 -> 1
1670 1 | 2 | arc 1 -> 2
1671 1 | 3 | arc 1 -> 3
1672 1 | 4 | arc 1 -> 4
1673 2 | 3 | arc 2 -> 3
1674 4 | 5 | arc 4 -> 5
1675 5 | 1 | arc 5 -> 1
1676 1 | 2 | arc 1 -> 2
1677 1 | 3 | arc 1 -> 3
1678 1 | 4 | arc 1 -> 4
1679 2 | 3 | arc 2 -> 3
1680 4 | 5 | arc 4 -> 5
1681 5 | 1 | arc 5 -> 1
1682 1 | 2 | arc 1 -> 2
1683 1 | 3 | arc 1 -> 3
1684 1 | 4 | arc 1 -> 4
1685 2 | 3 | arc 2 -> 3
1686 4 | 5 | arc 4 -> 5
1687 5 | 1 | arc 5 -> 1
1688 2 | 3 | arc 2 -> 3
1689 (25 rows)
1691 --
1692 -- test multiple WITH queries
1693 --
1694 WITH RECURSIVE
1695 y (id) AS (VALUES (1)),
1696 x (id) AS (SELECT * FROM y UNION ALL SELECT id+1 FROM x WHERE id < 5)
1697 SELECT * FROM x;
1698 id
1699 ----
1700 1
1701 2
1702 3
1703 4
1704 5
1705 (5 rows)
1707 -- forward reference OK
1708 WITH RECURSIVE
1709 x(id) AS (SELECT * FROM y UNION ALL SELECT id+1 FROM x WHERE id < 5),
1710 y(id) AS (values (1))
1711 SELECT * FROM x;
1712 id
1713 ----
1714 1
1715 2
1716 3
1717 4
1718 5
1719 (5 rows)
1721 WITH RECURSIVE
1722 x(id) AS
1723 (VALUES (1) UNION ALL SELECT id+1 FROM x WHERE id < 5),
1724 y(id) AS
1725 (VALUES (1) UNION ALL SELECT id+1 FROM y WHERE id < 10)
1726 SELECT y.*, x.* FROM y LEFT JOIN x USING (id);
1727 id | id
1728 ----+----
1729 1 | 1
1730 2 | 2
1731 3 | 3
1732 4 | 4
1733 5 | 5
1734 6 |
1735 7 |
1736 8 |
1737 9 |
1738 10 |
1739 (10 rows)
1741 WITH RECURSIVE
1742 x(id) AS
1743 (VALUES (1) UNION ALL SELECT id+1 FROM x WHERE id < 5),
1744 y(id) AS
1745 (VALUES (1) UNION ALL SELECT id+1 FROM x WHERE id < 10)
1746 SELECT y.*, x.* FROM y LEFT JOIN x USING (id);
1747 id | id
1748 ----+----
1749 1 | 1
1750 2 | 2
1751 3 | 3
1752 4 | 4
1753 5 | 5
1754 6 |
1755 (6 rows)
1757 WITH RECURSIVE
1758 x(id) AS
1759 (SELECT 1 UNION ALL SELECT id+1 FROM x WHERE id < 3 ),
1760 y(id) AS
1761 (SELECT * FROM x UNION ALL SELECT * FROM x),
1762 z(id) AS
1763 (SELECT * FROM x UNION ALL SELECT id+1 FROM z WHERE id < 10)
1764 SELECT * FROM z;
1765 id
1766 ----
1767 1
1768 2
1769 3
1770 2
1771 3
1772 4
1773 3
1774 4
1775 5
1776 4
1777 5
1778 6
1779 5
1780 6
1781 7
1782 6
1783 7
1784 8
1785 7
1786 8
1787 9
1788 8
1789 9
1790 10
1791 9
1792 10
1793 10
1794 (27 rows)
1796 WITH RECURSIVE
1797 x(id) AS
1798 (SELECT 1 UNION ALL SELECT id+1 FROM x WHERE id < 3 ),
1799 y(id) AS
1800 (SELECT * FROM x UNION ALL SELECT * FROM x),
1801 z(id) AS
1802 (SELECT * FROM y UNION ALL SELECT id+1 FROM z WHERE id < 10)
1803 SELECT * FROM z;
1804 id
1805 ----
1806 1
1807 2
1808 3
1809 1
1810 2
1811 3
1812 2
1813 3
1814 4
1815 2
1816 3
1817 4
1818 3
1819 4
1820 5
1821 3
1822 4
1823 5
1824 4
1825 5
1826 6
1827 4
1828 5
1829 6
1830 5
1831 6
1832 7
1833 5
1834 6
1835 7
1836 6
1837 7
1838 8
1839 6
1840 7
1841 8
1842 7
1843 8
1844 9
1845 7
1846 8
1847 9
1848 8
1849 9
1850 10
1851 8
1852 9
1853 10
1854 9
1855 10
1856 9
1857 10
1858 10
1859 10
1860 (54 rows)
1862 --
1863 -- Test WITH attached to a data-modifying statement
1864 --
1865 CREATE TEMPORARY TABLE y (a INTEGER);
1866 INSERT INTO y SELECT generate_series(1, 10);
1867 WITH t AS (
1868 SELECT a FROM y
1869 )
1870 INSERT INTO y
1871 SELECT a+20 FROM t RETURNING *;
1872 a
1873 ----
1874 21
1875 22
1876 23
1877 24
1878 25
1879 26
1880 27
1881 28
1882 29
1883 30
1884 (10 rows)
1886 SELECT * FROM y;
1887 a
1888 ----
1889 1
1890 2
1891 3
1892 4
1893 5
1894 6
1895 7
1896 8
1897 9
1898 10
1899 21
1900 22
1901 23
1902 24
1903 25
1904 26
1905 27
1906 28
1907 29
1908 30
1909 (20 rows)
1911 WITH t AS (
1912 SELECT a FROM y
1913 )
1914 UPDATE y SET a = y.a-10 FROM t WHERE y.a > 20 AND t.a = y.a RETURNING y.a;
1915 a
1916 ----
1917 11
1918 12
1919 13
1920 14
1921 15
1922 16
1923 17
1924 18
1925 19
1926 20
1927 (10 rows)
1929 SELECT * FROM y;
1930 a
1931 ----
1932 1
1933 2
1934 3
1935 4
1936 5
1937 6
1938 7
1939 8
1940 9
1941 10
1942 11
1943 12
1944 13
1945 14
1946 15
1947 16
1948 17
1949 18
1950 19
1951 20
1952 (20 rows)
1954 WITH RECURSIVE t(a) AS (
1955 SELECT 11
1956 UNION ALL
1957 SELECT a+1 FROM t WHERE a < 50
1958 )
1959 DELETE FROM y USING t WHERE t.a = y.a RETURNING y.a;
1960 a
1961 ----
1962 11
1963 12
1964 13
1965 14
1966 15
1967 16
1968 17
1969 18
1970 19
1971 20
1972 (10 rows)
1974 SELECT * FROM y;
1975 a
1976 ----
1977 1
1978 2
1979 3
1980 4
1981 5
1982 6
1983 7
1984 8
1985 9
1986 10
1987 (10 rows)
1989 DROP TABLE y;
1990 --
1991 -- error cases
1992 --
1993 WITH x(n, b) AS (SELECT 1)
1994 SELECT * FROM x;
1995 ERROR: WITH query "x" has 1 columns available but 2 columns specified
1996 LINE 1: WITH x(n, b) AS (SELECT 1)
1997 ^
1998 -- INTERSECT
1999 WITH RECURSIVE x(n) AS (SELECT 1 INTERSECT SELECT n+1 FROM x)
2000 SELECT * FROM x;
2001 ERROR: recursive query "x" does not have the form non-recursive-term UNION [ALL] recursive-term
2002 LINE 1: WITH RECURSIVE x(n) AS (SELECT 1 INTERSECT SELECT n+1 FROM x...
2003 ^
2004 WITH RECURSIVE x(n) AS (SELECT 1 INTERSECT ALL SELECT n+1 FROM x)
2005 SELECT * FROM x;
2006 ERROR: recursive query "x" does not have the form non-recursive-term UNION [ALL] recursive-term
2007 LINE 1: WITH RECURSIVE x(n) AS (SELECT 1 INTERSECT ALL SELECT n+1 FR...
2008 ^
2009 -- EXCEPT
2010 WITH RECURSIVE x(n) AS (SELECT 1 EXCEPT SELECT n+1 FROM x)
2011 SELECT * FROM x;
2012 ERROR: recursive query "x" does not have the form non-recursive-term UNION [ALL] recursive-term
2013 LINE 1: WITH RECURSIVE x(n) AS (SELECT 1 EXCEPT SELECT n+1 FROM x)
2014 ^
2015 WITH RECURSIVE x(n) AS (SELECT 1 EXCEPT ALL SELECT n+1 FROM x)
2016 SELECT * FROM x;
2017 ERROR: recursive query "x" does not have the form non-recursive-term UNION [ALL] recursive-term
2018 LINE 1: WITH RECURSIVE x(n) AS (SELECT 1 EXCEPT ALL SELECT n+1 FROM ...
2019 ^
2020 -- no non-recursive term
2021 WITH RECURSIVE x(n) AS (SELECT n FROM x)
2022 SELECT * FROM x;
2023 ERROR: recursive query "x" does not have the form non-recursive-term UNION [ALL] recursive-term
2024 LINE 1: WITH RECURSIVE x(n) AS (SELECT n FROM x)
2025 ^
2026 -- recursive term in the left hand side (strictly speaking, should allow this)
2027 WITH RECURSIVE x(n) AS (SELECT n FROM x UNION ALL SELECT 1)
2028 SELECT * FROM x;
2029 ERROR: recursive reference to query "x" must not appear within its non-recursive term
2030 LINE 1: WITH RECURSIVE x(n) AS (SELECT n FROM x UNION ALL SELECT 1)
2031 ^
2032 -- allow this, because we historically have
2033 WITH RECURSIVE x(n) AS (
2034 WITH x1 AS (SELECT 1 AS n)
2035 SELECT 0
2036 UNION
2037 SELECT * FROM x1)
2038 SELECT * FROM x;
2039 n
2040 ---
2041 0
2042 1
2043 (2 rows)
2045 -- but this should be rejected
2046 WITH RECURSIVE x(n) AS (
2047 WITH x1 AS (SELECT 1 FROM x)
2048 SELECT 0
2049 UNION
2050 SELECT * FROM x1)
2051 SELECT * FROM x;
2052 ERROR: recursive reference to query "x" must not appear within a subquery
2053 LINE 2: WITH x1 AS (SELECT 1 FROM x)
2054 ^
2055 -- and this too
2056 WITH RECURSIVE x(n) AS (
2057 (WITH x1 AS (SELECT 1 FROM x) SELECT * FROM x1)
2058 UNION
2059 SELECT 0)
2060 SELECT * FROM x;
2061 ERROR: recursive reference to query "x" must not appear within its non-recursive term
2062 LINE 2: (WITH x1 AS (SELECT 1 FROM x) SELECT * FROM x1)
2063 ^
2064 -- and this
2065 WITH RECURSIVE x(n) AS (
2066 SELECT 0 UNION SELECT 1
2067 ORDER BY (SELECT n FROM x))
2068 SELECT * FROM x;
2069 ERROR: ORDER BY in a recursive query is not implemented
2070 LINE 3: ORDER BY (SELECT n FROM x))
2071 ^
2072 CREATE TEMPORARY TABLE y (a INTEGER);
2073 INSERT INTO y SELECT generate_series(1, 10);
2074 -- LEFT JOIN
2075 WITH RECURSIVE x(n) AS (SELECT a FROM y WHERE a = 1
2076 UNION ALL
2077 SELECT x.n+1 FROM y LEFT JOIN x ON x.n = y.a WHERE n < 10)
2078 SELECT * FROM x;
2079 ERROR: recursive reference to query "x" must not appear within an outer join
2080 LINE 3: SELECT x.n+1 FROM y LEFT JOIN x ON x.n = y.a WHERE n < 10)
2081 ^
2082 -- RIGHT JOIN
2083 WITH RECURSIVE x(n) AS (SELECT a FROM y WHERE a = 1
2084 UNION ALL
2085 SELECT x.n+1 FROM x RIGHT JOIN y ON x.n = y.a WHERE n < 10)
2086 SELECT * FROM x;
2087 ERROR: recursive reference to query "x" must not appear within an outer join
2088 LINE 3: SELECT x.n+1 FROM x RIGHT JOIN y ON x.n = y.a WHERE n < 10)
2089 ^
2090 -- FULL JOIN
2091 WITH RECURSIVE x(n) AS (SELECT a FROM y WHERE a = 1
2092 UNION ALL
2093 SELECT x.n+1 FROM x FULL JOIN y ON x.n = y.a WHERE n < 10)
2094 SELECT * FROM x;
2095 ERROR: recursive reference to query "x" must not appear within an outer join
2096 LINE 3: SELECT x.n+1 FROM x FULL JOIN y ON x.n = y.a WHERE n < 10)
2097 ^
2098 -- subquery
2099 WITH RECURSIVE x(n) AS (SELECT 1 UNION ALL SELECT n+1 FROM x
2100 WHERE n IN (SELECT * FROM x))
2101 SELECT * FROM x;
2102 ERROR: recursive reference to query "x" must not appear within a subquery
2103 LINE 2: WHERE n IN (SELECT * FROM x))
2104 ^
2105 -- aggregate functions
2106 WITH RECURSIVE x(n) AS (SELECT 1 UNION ALL SELECT count(*) FROM x)
2107 SELECT * FROM x;
2108 ERROR: aggregate functions are not allowed in a recursive query's recursive term
2109 LINE 1: WITH RECURSIVE x(n) AS (SELECT 1 UNION ALL SELECT count(*) F...
2110 ^
2111 WITH RECURSIVE x(n) AS (SELECT 1 UNION ALL SELECT sum(n) FROM x)
2112 SELECT * FROM x;
2113 ERROR: aggregate functions are not allowed in a recursive query's recursive term
2114 LINE 1: WITH RECURSIVE x(n) AS (SELECT 1 UNION ALL SELECT sum(n) FRO...
2115 ^
2116 -- ORDER BY
2117 WITH RECURSIVE x(n) AS (SELECT 1 UNION ALL SELECT n+1 FROM x ORDER BY 1)
2118 SELECT * FROM x;
2119 ERROR: ORDER BY in a recursive query is not implemented
2120 LINE 1: ...VE x(n) AS (SELECT 1 UNION ALL SELECT n+1 FROM x ORDER BY 1)
2121 ^
2122 -- LIMIT/OFFSET
2123 WITH RECURSIVE x(n) AS (SELECT 1 UNION ALL SELECT n+1 FROM x LIMIT 10 OFFSET 1)
2124 SELECT * FROM x;
2125 ERROR: OFFSET in a recursive query is not implemented
2126 LINE 1: ... AS (SELECT 1 UNION ALL SELECT n+1 FROM x LIMIT 10 OFFSET 1)
2127 ^
2128 -- FOR UPDATE
2129 WITH RECURSIVE x(n) AS (SELECT 1 UNION ALL SELECT n+1 FROM x FOR UPDATE)
2130 SELECT * FROM x;
2131 ERROR: FOR UPDATE/SHARE in a recursive query is not implemented
2132 -- target list has a recursive query name
2133 WITH RECURSIVE x(id) AS (values (1)
2134 UNION ALL
2135 SELECT (SELECT * FROM x) FROM x WHERE id < 5
2136 ) SELECT * FROM x;
2137 ERROR: recursive reference to query "x" must not appear within a subquery
2138 LINE 3: SELECT (SELECT * FROM x) FROM x WHERE id < 5
2139 ^
2140 -- mutual recursive query (not implemented)
2141 WITH RECURSIVE
2142 x (id) AS (SELECT 1 UNION ALL SELECT id+1 FROM y WHERE id < 5),
2143 y (id) AS (SELECT 1 UNION ALL SELECT id+1 FROM x WHERE id < 5)
2144 SELECT * FROM x;
2145 ERROR: mutual recursion between WITH items is not implemented
2146 LINE 2: x (id) AS (SELECT 1 UNION ALL SELECT id+1 FROM y WHERE id ...
2147 ^
2148 -- non-linear recursion is not allowed
2149 WITH RECURSIVE foo(i) AS
2150 (values (1)
2151 UNION ALL
2152 (SELECT i+1 FROM foo WHERE i < 10
2153 UNION ALL
2154 SELECT i+1 FROM foo WHERE i < 5)
2155 ) SELECT * FROM foo;
2156 ERROR: recursive reference to query "foo" must not appear more than once
2157 LINE 6: SELECT i+1 FROM foo WHERE i < 5)
2158 ^
2159 WITH RECURSIVE foo(i) AS
2160 (values (1)
2161 UNION ALL
2162 SELECT * FROM
2163 (SELECT i+1 FROM foo WHERE i < 10
2164 UNION ALL
2165 SELECT i+1 FROM foo WHERE i < 5) AS t
2166 ) SELECT * FROM foo;
2167 ERROR: recursive reference to query "foo" must not appear more than once
2168 LINE 7: SELECT i+1 FROM foo WHERE i < 5) AS t
2169 ^
2170 WITH RECURSIVE foo(i) AS
2171 (values (1)
2172 UNION ALL
2173 (SELECT i+1 FROM foo WHERE i < 10
2174 EXCEPT
2175 SELECT i+1 FROM foo WHERE i < 5)
2176 ) SELECT * FROM foo;
2177 ERROR: recursive reference to query "foo" must not appear within EXCEPT
2178 LINE 6: SELECT i+1 FROM foo WHERE i < 5)
2179 ^
2180 WITH RECURSIVE foo(i) AS
2181 (values (1)
2182 UNION ALL
2183 (SELECT i+1 FROM foo WHERE i < 10
2184 INTERSECT
2185 SELECT i+1 FROM foo WHERE i < 5)
2186 ) SELECT * FROM foo;
2187 ERROR: recursive reference to query "foo" must not appear more than once
2188 LINE 6: SELECT i+1 FROM foo WHERE i < 5)
2189 ^
2190 -- Wrong type induced from non-recursive term
2191 WITH RECURSIVE foo(i) AS
2192 (SELECT i FROM (VALUES(1),(2)) t(i)
2193 UNION ALL
2194 SELECT (i+1)::numeric(10,0) FROM foo WHERE i < 10)
2195 SELECT * FROM foo;
2196 ERROR: recursive query "foo" column 1 has type integer in non-recursive term but type numeric overall
2197 LINE 2: (SELECT i FROM (VALUES(1),(2)) t(i)
2198 ^
2199 HINT: Cast the output of the non-recursive term to the correct type.
2200 -- rejects different typmod, too (should we allow this?)
2201 WITH RECURSIVE foo(i) AS
2202 (SELECT i::numeric(3,0) FROM (VALUES(1),(2)) t(i)
2203 UNION ALL
2204 SELECT (i+1)::numeric(10,0) FROM foo WHERE i < 10)
2205 SELECT * FROM foo;
2206 ERROR: recursive query "foo" column 1 has type numeric(3,0) in non-recursive term but type numeric overall
2207 LINE 2: (SELECT i::numeric(3,0) FROM (VALUES(1),(2)) t(i)
2208 ^
2209 HINT: Cast the output of the non-recursive term to the correct type.
2210 -- disallow OLD/NEW reference in CTE
2211 CREATE TEMPORARY TABLE x (n integer);
2212 CREATE RULE r2 AS ON UPDATE TO x DO INSTEAD
2213 WITH t AS (SELECT OLD.*) UPDATE y SET a = t.n FROM t;
2214 ERROR: cannot refer to OLD within WITH query
2215 --
2216 -- test for bug #4902
2217 --
2218 with cte(foo) as ( values(42) ) values((select foo from cte));
2219 column1
2220 ---------
2221 42
2222 (1 row)
2224 with cte(foo) as ( select 42 ) select * from ((select foo from cte)) q;
2225 foo
2226 -----
2227 42
2228 (1 row)
2230 -- test CTE referencing an outer-level variable (to see that changed-parameter
2231 -- signaling still works properly after fixing this bug)
2232 select ( with cte(foo) as ( values(f1) )
2233 select (select foo from cte) )
2234 from int4_tbl;
2235 foo
2236 -------------
2237 0
2238 123456
2239 -123456
2240 2147483647
2241 -2147483647
2242 (5 rows)
2244 select ( with cte(foo) as ( values(f1) )
2245 values((select foo from cte)) )
2246 from int4_tbl;
2247 column1
2248 -------------
2249 0
2250 123456
2251 -123456
2252 2147483647
2253 -2147483647
2254 (5 rows)
2256 --
2257 -- test for nested-recursive-WITH bug
2258 --
2259 WITH RECURSIVE t(j) AS (
2260 WITH RECURSIVE s(i) AS (
2261 VALUES (1)
2262 UNION ALL
2263 SELECT i+1 FROM s WHERE i < 10
2264 )
2265 SELECT i FROM s
2266 UNION ALL
2267 SELECT j+1 FROM t WHERE j < 10
2268 )
2269 SELECT * FROM t;
2270 j
2271 ----
2272 1
2273 2
2274 3
2275 4
2276 5
2277 6
2278 7
2279 8
2280 9
2281 10
2282 2
2283 3
2284 4
2285 5
2286 6
2287 7
2288 8
2289 9
2290 10
2291 3
2292 4
2293 5
2294 6
2295 7
2296 8
2297 9
2298 10
2299 4
2300 5
2301 6
2302 7
2303 8
2304 9
2305 10
2306 5
2307 6
2308 7
2309 8
2310 9
2311 10
2312 6
2313 7
2314 8
2315 9
2316 10
2317 7
2318 8
2319 9
2320 10
2321 8
2322 9
2323 10
2324 9
2325 10
2326 10
2327 (55 rows)
2329 --
2330 -- test WITH attached to intermediate-level set operation
2331 --
2332 WITH outermost(x) AS (
2333 SELECT 1
2334 UNION (WITH innermost as (SELECT 2)
2335 SELECT * FROM innermost
2336 UNION SELECT 3)
2337 )
2338 SELECT * FROM outermost ORDER BY 1;
2339 x
2340 ---
2341 1
2342 2
2343 3
2344 (3 rows)
2346 WITH outermost(x) AS (
2347 SELECT 1
2348 UNION (WITH innermost as (SELECT 2)
2349 SELECT * FROM outermost -- fail
2350 UNION SELECT * FROM innermost)
2351 )
2352 SELECT * FROM outermost ORDER BY 1;
2353 ERROR: relation "outermost" does not exist
2354 LINE 4: SELECT * FROM outermost -- fail
2355 ^
2356 DETAIL: There is a WITH item named "outermost", but it cannot be referenced from this part of the query.
2357 HINT: Use WITH RECURSIVE, or re-order the WITH items to remove forward references.
2358 WITH RECURSIVE outermost(x) AS (
2359 SELECT 1
2360 UNION (WITH innermost as (SELECT 2)
2361 SELECT * FROM outermost
2362 UNION SELECT * FROM innermost)
2363 )
2364 SELECT * FROM outermost ORDER BY 1;
2365 x
2366 ---
2367 1
2368 2
2369 (2 rows)
2371 WITH RECURSIVE outermost(x) AS (
2372 WITH innermost as (SELECT 2 FROM outermost) -- fail
2373 SELECT * FROM innermost
2374 UNION SELECT * from outermost
2375 )
2376 SELECT * FROM outermost ORDER BY 1;
2377 ERROR: recursive reference to query "outermost" must not appear within a subquery
2378 LINE 2: WITH innermost as (SELECT 2 FROM outermost) -- fail
2379 ^
2380 --
2381 -- This test will fail with the old implementation of PARAM_EXEC parameter
2382 -- assignment, because the "q1" Var passed down to A's targetlist subselect
2383 -- looks exactly like the "A.id" Var passed down to C's subselect, causing
2384 -- the old code to give them the same runtime PARAM_EXEC slot. But the
2385 -- lifespans of the two parameters overlap, thanks to B also reading A.
2386 --
2387 with
2388 A as ( select q2 as id, (select q1) as x from int8_tbl ),
2389 B as ( select id, row_number() over (partition by id) as r from A ),
2390 C as ( select A.id, array(select B.id from B where B.id = A.id) from A )
2391 select * from C;
2392 id | array
2393 -------------------+-------------------------------------
2394 456 | {456}
2395 4567890123456789 | {4567890123456789,4567890123456789}
2396 123 | {123}
2397 4567890123456789 | {4567890123456789,4567890123456789}
2398 -4567890123456789 | {-4567890123456789}
2399 (5 rows)
2401 --
2402 -- Test CTEs read in non-initialization orders
2403 --
2404 WITH RECURSIVE
2405 tab(id_key,link) AS (VALUES (1,17), (2,17), (3,17), (4,17), (6,17), (5,17)),
2406 iter (id_key, row_type, link) AS (
2407 SELECT 0, 'base', 17
2408 UNION ALL (
2409 WITH remaining(id_key, row_type, link, min) AS (
2410 SELECT tab.id_key, 'true'::text, iter.link, MIN(tab.id_key) OVER ()
2411 FROM tab INNER JOIN iter USING (link)
2412 WHERE tab.id_key > iter.id_key
2413 ),
2414 first_remaining AS (
2415 SELECT id_key, row_type, link
2416 FROM remaining
2417 WHERE id_key=min
2418 ),
2419 effect AS (
2420 SELECT tab.id_key, 'new'::text, tab.link
2421 FROM first_remaining e INNER JOIN tab ON e.id_key=tab.id_key
2422 WHERE e.row_type = 'false'
2423 )
2424 SELECT * FROM first_remaining
2425 UNION ALL SELECT * FROM effect
2426 )
2427 )
2428 SELECT * FROM iter;
2429 id_key | row_type | link
2430 --------+----------+------
2431 0 | base | 17
2432 1 | true | 17
2433 2 | true | 17
2434 3 | true | 17
2435 4 | true | 17
2436 5 | true | 17
2437 6 | true | 17
2438 (7 rows)
2440 WITH RECURSIVE
2441 tab(id_key,link) AS (VALUES (1,17), (2,17), (3,17), (4,17), (6,17), (5,17)),
2442 iter (id_key, row_type, link) AS (
2443 SELECT 0, 'base', 17
2444 UNION (
2445 WITH remaining(id_key, row_type, link, min) AS (
2446 SELECT tab.id_key, 'true'::text, iter.link, MIN(tab.id_key) OVER ()
2447 FROM tab INNER JOIN iter USING (link)
2448 WHERE tab.id_key > iter.id_key
2449 ),
2450 first_remaining AS (
2451 SELECT id_key, row_type, link
2452 FROM remaining
2453 WHERE id_key=min
2454 ),
2455 effect AS (
2456 SELECT tab.id_key, 'new'::text, tab.link
2457 FROM first_remaining e INNER JOIN tab ON e.id_key=tab.id_key
2458 WHERE e.row_type = 'false'
2459 )
2460 SELECT * FROM first_remaining
2461 UNION ALL SELECT * FROM effect
2462 )
2463 )
2464 SELECT * FROM iter;
2465 id_key | row_type | link
2466 --------+----------+------
2467 0 | base | 17
2468 1 | true | 17
2469 2 | true | 17
2470 3 | true | 17
2471 4 | true | 17
2472 5 | true | 17
2473 6 | true | 17
2474 (7 rows)
2476 --
2477 -- Data-modifying statements in WITH
2478 --
2479 -- INSERT ... RETURNING
2480 WITH t AS (
2481 INSERT INTO y
2482 VALUES
2483 (11),
2484 (12),
2485 (13),
2486 (14),
2487 (15),
2488 (16),
2489 (17),
2490 (18),
2491 (19),
2492 (20)
2493 RETURNING *
2494 )
2495 SELECT * FROM t;
2496 a
2497 ----
2498 11
2499 12
2500 13
2501 14
2502 15
2503 16
2504 17
2505 18
2506 19
2507 20
2508 (10 rows)
2510 SELECT * FROM y;
2511 a
2512 ----
2513 1
2514 2
2515 3
2516 4
2517 5
2518 6
2519 7
2520 8
2521 9
2522 10
2523 11
2524 12
2525 13
2526 14
2527 15
2528 16
2529 17
2530 18
2531 19
2532 20
2533 (20 rows)
2535 -- UPDATE ... RETURNING
2536 WITH t AS (
2537 UPDATE y
2538 SET a=a+1
2539 RETURNING *
2540 )
2541 SELECT * FROM t;
2542 a
2543 ----
2544 2
2545 3
2546 4
2547 5
2548 6
2549 7
2550 8
2551 9
2552 10
2553 11
2554 12
2555 13
2556 14
2557 15
2558 16
2559 17
2560 18
2561 19
2562 20
2563 21
2564 (20 rows)
2566 SELECT * FROM y;
2567 a
2568 ----
2569 2
2570 3
2571 4
2572 5
2573 6
2574 7
2575 8
2576 9
2577 10
2578 11
2579 12
2580 13
2581 14
2582 15
2583 16
2584 17
2585 18
2586 19
2587 20
2588 21
2589 (20 rows)
2591 -- DELETE ... RETURNING
2592 WITH t AS (
2593 DELETE FROM y
2594 WHERE a <= 10
2595 RETURNING *
2596 )
2597 SELECT * FROM t;
2598 a
2599 ----
2600 2
2601 3
2602 4
2603 5
2604 6
2605 7
2606 8
2607 9
2608 10
2609 (9 rows)
2611 SELECT * FROM y;
2612 a
2613 ----
2614 11
2615 12
2616 13
2617 14
2618 15
2619 16
2620 17
2621 18
2622 19
2623 20
2624 21
2625 (11 rows)
2627 -- forward reference
2628 WITH RECURSIVE t AS (
2629 INSERT INTO y
2630 SELECT a+5 FROM t2 WHERE a > 5
2631 RETURNING *
2632 ), t2 AS (
2633 UPDATE y SET a=a-11 RETURNING *
2634 )
2635 SELECT * FROM t
2636 UNION ALL
2637 SELECT * FROM t2;
2638 a
2639 ----
2640 11
2641 12
2642 13
2643 14
2644 15
2645 0
2646 1
2647 2
2648 3
2649 4
2650 5
2651 6
2652 7
2653 8
2654 9
2655 10
2656 (16 rows)
2658 SELECT * FROM y;
2659 a
2660 ----
2661 0
2662 1
2663 2
2664 3
2665 4
2666 5
2667 6
2668 11
2669 7
2670 12
2671 8
2672 13
2673 9
2674 14
2675 10
2676 15
2677 (16 rows)
2679 -- unconditional DO INSTEAD rule
2680 CREATE RULE y_rule AS ON DELETE TO y DO INSTEAD
2681 INSERT INTO y VALUES(42) RETURNING *;
2682 WITH t AS (
2683 DELETE FROM y RETURNING *
2684 )
2685 SELECT * FROM t;
2686 a
2687 ----
2688 42
2689 (1 row)
2691 SELECT * FROM y;
2692 a
2693 ----
2694 0
2695 1
2696 2
2697 3
2698 4
2699 5
2700 6
2701 11
2702 7
2703 12
2704 8
2705 13
2706 9
2707 14
2708 10
2709 15
2710 42
2711 (17 rows)
2713 DROP RULE y_rule ON y;
2714 -- check merging of outer CTE with CTE in a rule action
2715 CREATE TEMP TABLE bug6051 AS
2716 select i from generate_series(1,3) as t(i);
2717 SELECT * FROM bug6051;
2718 i
2719 ---
2720 1
2721 2
2722 3
2723 (3 rows)
2725 WITH t1 AS ( DELETE FROM bug6051 RETURNING * )
2726 INSERT INTO bug6051 SELECT * FROM t1;
2727 SELECT * FROM bug6051;
2728 i
2729 ---
2730 1
2731 2
2732 3
2733 (3 rows)
2735 CREATE TEMP TABLE bug6051_2 (i int);
2736 CREATE RULE bug6051_ins AS ON INSERT TO bug6051 DO INSTEAD
2737 INSERT INTO bug6051_2
2738 VALUES(NEW.i);
2739 WITH t1 AS ( DELETE FROM bug6051 RETURNING * )
2740 INSERT INTO bug6051 SELECT * FROM t1;
2741 SELECT * FROM bug6051;
2742 i
2743 ---
2744 (0 rows)
2746 SELECT * FROM bug6051_2;
2747 i
2748 ---
2749 1
2750 2
2751 3
2752 (3 rows)
2754 -- check INSERT ... SELECT rule actions are disallowed on commands
2755 -- that have modifyingCTEs
2756 CREATE OR REPLACE RULE bug6051_ins AS ON INSERT TO bug6051 DO INSTEAD
2757 INSERT INTO bug6051_2
2758 SELECT NEW.i;
2759 WITH t1 AS ( DELETE FROM bug6051 RETURNING * )
2760 INSERT INTO bug6051 SELECT * FROM t1;
2761 ERROR: INSERT ... SELECT rule actions are not supported for queries having data-modifying statements in WITH
2762 -- silly example to verify that hasModifyingCTE flag is propagated
2763 CREATE TEMP TABLE bug6051_3 AS
2764 SELECT a FROM generate_series(11,13) AS a;
2765 CREATE RULE bug6051_3_ins AS ON INSERT TO bug6051_3 DO INSTEAD
2766 SELECT i FROM bug6051_2;
2767 BEGIN; SET LOCAL debug_parallel_query = on;
2768 WITH t1 AS ( DELETE FROM bug6051_3 RETURNING * )
2769 INSERT INTO bug6051_3 SELECT * FROM t1;
2770 i
2771 ---
2772 1
2773 2
2774 3
2775 1
2776 2
2777 3
2778 1
2779 2
2780 3
2781 (9 rows)
2783 COMMIT;
2784 SELECT * FROM bug6051_3;
2785 a
2786 ---
2787 (0 rows)
2789 -- check case where CTE reference is removed due to optimization
2790 EXPLAIN (VERBOSE, COSTS OFF)
2791 SELECT q1 FROM
2792 (
2793 WITH t_cte AS (SELECT * FROM int8_tbl t)
2794 SELECT q1, (SELECT q2 FROM t_cte WHERE t_cte.q1 = i8.q1) AS t_sub
2795 FROM int8_tbl i8
2796 ) ss;
2797 QUERY PLAN
2798 --------------------------------------
2799 Subquery Scan on ss
2800 Output: ss.q1
2801 -> Seq Scan on public.int8_tbl i8
2802 Output: i8.q1, NULL::bigint
2803 (4 rows)
2805 SELECT q1 FROM
2806 (
2807 WITH t_cte AS (SELECT * FROM int8_tbl t)
2808 SELECT q1, (SELECT q2 FROM t_cte WHERE t_cte.q1 = i8.q1) AS t_sub
2809 FROM int8_tbl i8
2810 ) ss;
2811 q1
2812 ------------------
2813 123
2814 123
2815 4567890123456789
2816 4567890123456789
2817 4567890123456789
2818 (5 rows)
2820 EXPLAIN (VERBOSE, COSTS OFF)
2821 SELECT q1 FROM
2822 (
2823 WITH t_cte AS MATERIALIZED (SELECT * FROM int8_tbl t)
2824 SELECT q1, (SELECT q2 FROM t_cte WHERE t_cte.q1 = i8.q1) AS t_sub
2825 FROM int8_tbl i8
2826 ) ss;
2827 QUERY PLAN
2828 ---------------------------------------------
2829 Subquery Scan on ss
2830 Output: ss.q1
2831 -> Seq Scan on public.int8_tbl i8
2832 Output: i8.q1, NULL::bigint
2833 CTE t_cte
2834 -> Seq Scan on public.int8_tbl t
2835 Output: t.q1, t.q2
2836 (7 rows)
2838 SELECT q1 FROM
2839 (
2840 WITH t_cte AS MATERIALIZED (SELECT * FROM int8_tbl t)
2841 SELECT q1, (SELECT q2 FROM t_cte WHERE t_cte.q1 = i8.q1) AS t_sub
2842 FROM int8_tbl i8
2843 ) ss;
2844 q1
2845 ------------------
2846 123
2847 123
2848 4567890123456789
2849 4567890123456789
2850 4567890123456789
2851 (5 rows)
2853 -- a truly recursive CTE in the same list
2854 WITH RECURSIVE t(a) AS (
2855 SELECT 0
2856 UNION ALL
2857 SELECT a+1 FROM t WHERE a+1 < 5
2858 ), t2 as (
2859 INSERT INTO y
2860 SELECT * FROM t RETURNING *
2861 )
2862 SELECT * FROM t2 JOIN y USING (a) ORDER BY a;
2863 a
2864 ---
2865 0
2866 1
2867 2
2868 3
2869 4
2870 (5 rows)
2872 SELECT * FROM y;
2873 a
2874 ----
2875 0
2876 1
2877 2
2878 3
2879 4
2880 5
2881 6
2882 11
2883 7
2884 12
2885 8
2886 13
2887 9
2888 14
2889 10
2890 15
2891 42
2892 0
2893 1
2894 2
2895 3
2896 4
2897 (22 rows)
2899 -- data-modifying WITH in a modifying statement
2900 WITH t AS (
2901 DELETE FROM y
2902 WHERE a <= 10
2903 RETURNING *
2904 )
2905 INSERT INTO y SELECT -a FROM t RETURNING *;
2906 a
2907 -----
2908 0
2909 -1
2910 -2
2911 -3
2912 -4
2913 -5
2914 -6
2915 -7
2916 -8
2917 -9
2918 -10
2919 0
2920 -1
2921 -2
2922 -3
2923 -4
2924 (16 rows)
2926 SELECT * FROM y;
2927 a
2928 -----
2929 11
2930 12
2931 13
2932 14
2933 15
2934 42
2935 0
2936 -1
2937 -2
2938 -3
2939 -4
2940 -5
2941 -6
2942 -7
2943 -8
2944 -9
2945 -10
2946 0
2947 -1
2948 -2
2949 -3
2950 -4
2951 (22 rows)
2953 -- check that WITH query is run to completion even if outer query isn't
2954 WITH t AS (
2955 UPDATE y SET a = a * 100 RETURNING *
2956 )
2957 SELECT * FROM t LIMIT 10;
2958 a
2959 ------
2960 1100
2961 1200
2962 1300
2963 1400
2964 1500
2965 4200
2966 0
2967 -100
2968 -200
2969 -300
2970 (10 rows)
2972 SELECT * FROM y;
2973 a
2974 -------
2975 1100
2976 1200
2977 1300
2978 1400
2979 1500
2980 4200
2981 0
2982 -100
2983 -200
2984 -300
2985 -400
2986 -500
2987 -600
2988 -700
2989 -800
2990 -900
2991 -1000
2992 0
2993 -100
2994 -200
2995 -300
2996 -400
2997 (22 rows)
2999 -- data-modifying WITH containing INSERT...ON CONFLICT DO UPDATE
3000 CREATE TABLE withz AS SELECT i AS k, (i || ' v')::text v FROM generate_series(1, 16, 3) i;
3001 ALTER TABLE withz ADD UNIQUE (k);
3002 WITH t AS (
3003 INSERT INTO withz SELECT i, 'insert'
3004 FROM generate_series(0, 16) i
3005 ON CONFLICT (k) DO UPDATE SET v = withz.v || ', now update'
3006 RETURNING *
3007 )
3008 SELECT * FROM t JOIN y ON t.k = y.a ORDER BY a, k;
3009 k | v | a
3010 ---+--------+---
3011 0 | insert | 0
3012 0 | insert | 0
3013 (2 rows)
3015 -- Test EXCLUDED.* reference within CTE
3016 WITH aa AS (
3017 INSERT INTO withz VALUES(1, 5) ON CONFLICT (k) DO UPDATE SET v = EXCLUDED.v
3018 WHERE withz.k != EXCLUDED.k
3019 RETURNING *
3020 )
3021 SELECT * FROM aa;
3022 k | v
3023 ---+---
3024 (0 rows)
3026 -- New query/snapshot demonstrates side-effects of previous query.
3027 SELECT * FROM withz ORDER BY k;
3028 k | v
3029 ----+------------------
3030 0 | insert
3031 1 | 1 v, now update
3032 2 | insert
3033 3 | insert
3034 4 | 4 v, now update
3035 5 | insert
3036 6 | insert
3037 7 | 7 v, now update
3038 8 | insert
3039 9 | insert
3040 10 | 10 v, now update
3041 11 | insert
3042 12 | insert
3043 13 | 13 v, now update
3044 14 | insert
3045 15 | insert
3046 16 | 16 v, now update
3047 (17 rows)
3049 --
3050 -- Ensure subqueries within the update clause work, even if they
3051 -- reference outside values
3052 --
3053 WITH aa AS (SELECT 1 a, 2 b)
3054 INSERT INTO withz VALUES(1, 'insert')
3055 ON CONFLICT (k) DO UPDATE SET v = (SELECT b || ' update' FROM aa WHERE a = 1 LIMIT 1);
3056 WITH aa AS (SELECT 1 a, 2 b)
3057 INSERT INTO withz VALUES(1, 'insert')
3058 ON CONFLICT (k) DO UPDATE SET v = ' update' WHERE withz.k = (SELECT a FROM aa);
3059 WITH aa AS (SELECT 1 a, 2 b)
3060 INSERT INTO withz VALUES(1, 'insert')
3061 ON CONFLICT (k) DO UPDATE SET v = (SELECT b || ' update' FROM aa WHERE a = 1 LIMIT 1);
3062 WITH aa AS (SELECT 'a' a, 'b' b UNION ALL SELECT 'a' a, 'b' b)
3063 INSERT INTO withz VALUES(1, 'insert')
3064 ON CONFLICT (k) DO UPDATE SET v = (SELECT b || ' update' FROM aa WHERE a = 'a' LIMIT 1);
3065 WITH aa AS (SELECT 1 a, 2 b)
3066 INSERT INTO withz VALUES(1, (SELECT b || ' insert' FROM aa WHERE a = 1 ))
3067 ON CONFLICT (k) DO UPDATE SET v = (SELECT b || ' update' FROM aa WHERE a = 1 LIMIT 1);
3068 -- Update a row more than once, in different parts of a wCTE. That is
3069 -- an allowed, presumably very rare, edge case, but since it was
3070 -- broken in the past, having a test seems worthwhile.
3071 WITH simpletup AS (
3072 SELECT 2 k, 'Green' v),
3073 upsert_cte AS (
3074 INSERT INTO withz VALUES(2, 'Blue') ON CONFLICT (k) DO
3075 UPDATE SET (k, v) = (SELECT k, v FROM simpletup WHERE simpletup.k = withz.k)
3076 RETURNING k, v)
3077 INSERT INTO withz VALUES(2, 'Red') ON CONFLICT (k) DO
3078 UPDATE SET (k, v) = (SELECT k, v FROM upsert_cte WHERE upsert_cte.k = withz.k)
3079 RETURNING k, v;
3080 k | v
3081 ---+---
3082 (0 rows)
3084 DROP TABLE withz;
3085 -- WITH referenced by MERGE statement
3086 CREATE TABLE m AS SELECT i AS k, (i || ' v')::text v FROM generate_series(1, 16, 3) i;
3087 ALTER TABLE m ADD UNIQUE (k);
3088 WITH RECURSIVE cte_basic AS (SELECT 1 a, 'cte_basic val' b)
3089 MERGE INTO m USING (select 0 k, 'merge source SubPlan' v) o ON m.k=o.k
3090 WHEN MATCHED THEN UPDATE SET v = (SELECT b || ' merge update' FROM cte_basic WHERE cte_basic.a = m.k LIMIT 1)
3091 WHEN NOT MATCHED THEN INSERT VALUES(o.k, o.v);
3092 ERROR: WITH RECURSIVE is not supported for MERGE statement
3093 -- Basic:
3094 WITH cte_basic AS MATERIALIZED (SELECT 1 a, 'cte_basic val' b)
3095 MERGE INTO m USING (select 0 k, 'merge source SubPlan' v offset 0) o ON m.k=o.k
3096 WHEN MATCHED THEN UPDATE SET v = (SELECT b || ' merge update' FROM cte_basic WHERE cte_basic.a = m.k LIMIT 1)
3097 WHEN NOT MATCHED THEN INSERT VALUES(o.k, o.v);
3098 -- Examine
3099 SELECT * FROM m where k = 0;
3100 k | v
3101 ---+----------------------
3102 0 | merge source SubPlan
3103 (1 row)
3105 -- See EXPLAIN output for same query:
3106 EXPLAIN (VERBOSE, COSTS OFF)
3107 WITH cte_basic AS MATERIALIZED (SELECT 1 a, 'cte_basic val' b)
3108 MERGE INTO m USING (select 0 k, 'merge source SubPlan' v offset 0) o ON m.k=o.k
3109 WHEN MATCHED THEN UPDATE SET v = (SELECT b || ' merge update' FROM cte_basic WHERE cte_basic.a = m.k LIMIT 1)
3110 WHEN NOT MATCHED THEN INSERT VALUES(o.k, o.v);
3111 QUERY PLAN
3112 -------------------------------------------------------------------
3113 Merge on public.m
3114 CTE cte_basic
3115 -> Result
3116 Output: 1, 'cte_basic val'::text
3117 -> Hash Right Join
3118 Output: m.ctid, o.k, o.v, o.*
3119 Hash Cond: (m.k = o.k)
3120 -> Seq Scan on public.m
3121 Output: m.ctid, m.k
3122 -> Hash
3123 Output: o.k, o.v, o.*
3124 -> Subquery Scan on o
3125 Output: o.k, o.v, o.*
3126 -> Result
3127 Output: 0, 'merge source SubPlan'::text
3128 SubPlan 2
3129 -> Limit
3130 Output: ((cte_basic.b || ' merge update'::text))
3131 -> CTE Scan on cte_basic
3132 Output: (cte_basic.b || ' merge update'::text)
3133 Filter: (cte_basic.a = m.k)
3134 (21 rows)
3136 -- InitPlan
3137 WITH cte_init AS MATERIALIZED (SELECT 1 a, 'cte_init val' b)
3138 MERGE INTO m USING (select 1 k, 'merge source InitPlan' v offset 0) o ON m.k=o.k
3139 WHEN MATCHED THEN UPDATE SET v = (SELECT b || ' merge update' FROM cte_init WHERE a = 1 LIMIT 1)
3140 WHEN NOT MATCHED THEN INSERT VALUES(o.k, o.v);
3141 -- Examine
3142 SELECT * FROM m where k = 1;
3143 k | v
3144 ---+---------------------------
3145 1 | cte_init val merge update
3146 (1 row)
3148 -- See EXPLAIN output for same query:
3149 EXPLAIN (VERBOSE, COSTS OFF)
3150 WITH cte_init AS MATERIALIZED (SELECT 1 a, 'cte_init val' b)
3151 MERGE INTO m USING (select 1 k, 'merge source InitPlan' v offset 0) o ON m.k=o.k
3152 WHEN MATCHED THEN UPDATE SET v = (SELECT b || ' merge update' FROM cte_init WHERE a = 1 LIMIT 1)
3153 WHEN NOT MATCHED THEN INSERT VALUES(o.k, o.v);
3154 QUERY PLAN
3155 --------------------------------------------------------------------
3156 Merge on public.m
3157 CTE cte_init
3158 -> Result
3159 Output: 1, 'cte_init val'::text
3160 InitPlan 2
3161 -> Limit
3162 Output: ((cte_init.b || ' merge update'::text))
3163 -> CTE Scan on cte_init
3164 Output: (cte_init.b || ' merge update'::text)
3165 Filter: (cte_init.a = 1)
3166 -> Hash Right Join
3167 Output: m.ctid, o.k, o.v, o.*
3168 Hash Cond: (m.k = o.k)
3169 -> Seq Scan on public.m
3170 Output: m.ctid, m.k
3171 -> Hash
3172 Output: o.k, o.v, o.*
3173 -> Subquery Scan on o
3174 Output: o.k, o.v, o.*
3175 -> Result
3176 Output: 1, 'merge source InitPlan'::text
3177 (21 rows)
3179 -- MERGE source comes from CTE:
3180 WITH merge_source_cte AS MATERIALIZED (SELECT 15 a, 'merge_source_cte val' b)
3181 MERGE INTO m USING (select * from merge_source_cte) o ON m.k=o.a
3182 WHEN MATCHED THEN UPDATE SET v = (SELECT b || merge_source_cte.*::text || ' merge update' FROM merge_source_cte WHERE a = 15)
3183 WHEN NOT MATCHED THEN INSERT VALUES(o.a, o.b || (SELECT merge_source_cte.*::text || ' merge insert' FROM merge_source_cte));
3184 -- Examine
3185 SELECT * FROM m where k = 15;
3186 k | v
3187 ----+--------------------------------------------------------------
3188 15 | merge_source_cte val(15,"merge_source_cte val") merge insert
3189 (1 row)
3191 -- See EXPLAIN output for same query:
3192 EXPLAIN (VERBOSE, COSTS OFF)
3193 WITH merge_source_cte AS MATERIALIZED (SELECT 15 a, 'merge_source_cte val' b)
3194 MERGE INTO m USING (select * from merge_source_cte) o ON m.k=o.a
3195 WHEN MATCHED THEN UPDATE SET v = (SELECT b || merge_source_cte.*::text || ' merge update' FROM merge_source_cte WHERE a = 15)
3196 WHEN NOT MATCHED THEN INSERT VALUES(o.a, o.b || (SELECT merge_source_cte.*::text || ' merge insert' FROM merge_source_cte));
3197 QUERY PLAN
3198 -----------------------------------------------------------------------------------------------------
3199 Merge on public.m
3200 CTE merge_source_cte
3201 -> Result
3202 Output: 15, 'merge_source_cte val'::text
3203 InitPlan 2
3204 -> CTE Scan on merge_source_cte merge_source_cte_1
3205 Output: ((merge_source_cte_1.b || (merge_source_cte_1.*)::text) || ' merge update'::text)
3206 Filter: (merge_source_cte_1.a = 15)
3207 InitPlan 3
3208 -> CTE Scan on merge_source_cte merge_source_cte_2
3209 Output: ((merge_source_cte_2.*)::text || ' merge insert'::text)
3210 -> Hash Right Join
3211 Output: m.ctid, merge_source_cte.a, merge_source_cte.b, merge_source_cte.*
3212 Hash Cond: (m.k = merge_source_cte.a)
3213 -> Seq Scan on public.m
3214 Output: m.ctid, m.k
3215 -> Hash
3216 Output: merge_source_cte.a, merge_source_cte.b, merge_source_cte.*
3217 -> CTE Scan on merge_source_cte
3218 Output: merge_source_cte.a, merge_source_cte.b, merge_source_cte.*
3219 (20 rows)
3221 DROP TABLE m;
3222 -- check that run to completion happens in proper ordering
3223 TRUNCATE TABLE y;
3224 INSERT INTO y SELECT generate_series(1, 3);
3225 CREATE TEMPORARY TABLE yy (a INTEGER);
3226 WITH RECURSIVE t1 AS (
3227 INSERT INTO y SELECT * FROM y RETURNING *
3228 ), t2 AS (
3229 INSERT INTO yy SELECT * FROM t1 RETURNING *
3230 )
3231 SELECT 1;
3232 ?column?
3233 ----------
3234 1
3235 (1 row)
3237 SELECT * FROM y;
3238 a
3239 ---
3240 1
3241 2
3242 3
3243 1
3244 2
3245 3
3246 (6 rows)
3248 SELECT * FROM yy;
3249 a
3250 ---
3251 1
3252 2
3253 3
3254 (3 rows)
3256 WITH RECURSIVE t1 AS (
3257 INSERT INTO yy SELECT * FROM t2 RETURNING *
3258 ), t2 AS (
3259 INSERT INTO y SELECT * FROM y RETURNING *
3260 )
3261 SELECT 1;
3262 ?column?
3263 ----------
3264 1
3265 (1 row)
3267 SELECT * FROM y;
3268 a
3269 ---
3270 1
3271 2
3272 3
3273 1
3274 2
3275 3
3276 1
3277 2
3278 3
3279 1
3280 2
3281 3
3282 (12 rows)
3284 SELECT * FROM yy;
3285 a
3286 ---
3287 1
3288 2
3289 3
3290 1
3291 2
3292 3
3293 1
3294 2
3295 3
3296 (9 rows)
3298 -- triggers
3299 TRUNCATE TABLE y;
3300 INSERT INTO y SELECT generate_series(1, 10);
3301 CREATE FUNCTION y_trigger() RETURNS trigger AS $$
3302 begin
3303 raise notice 'y_trigger: a = %', new.a;
3304 return new;
3305 end;
3306 $$ LANGUAGE plpgsql;
3307 CREATE TRIGGER y_trig BEFORE INSERT ON y FOR EACH ROW
3308 EXECUTE PROCEDURE y_trigger();
3309 WITH t AS (
3310 INSERT INTO y
3311 VALUES
3312 (21),
3313 (22),
3314 (23)
3315 RETURNING *
3316 )
3317 SELECT * FROM t;
3318 NOTICE: y_trigger: a = 21
3319 NOTICE: y_trigger: a = 22
3320 NOTICE: y_trigger: a = 23
3321 a
3322 ----
3323 21
3324 22
3325 23
3326 (3 rows)
3328 SELECT * FROM y;
3329 a
3330 ----
3331 1
3332 2
3333 3
3334 4
3335 5
3336 6
3337 7
3338 8
3339 9
3340 10
3341 21
3342 22
3343 23
3344 (13 rows)
3346 DROP TRIGGER y_trig ON y;
3347 CREATE TRIGGER y_trig AFTER INSERT ON y FOR EACH ROW
3348 EXECUTE PROCEDURE y_trigger();
3349 WITH t AS (
3350 INSERT INTO y
3351 VALUES
3352 (31),
3353 (32),
3354 (33)
3355 RETURNING *
3356 )
3357 SELECT * FROM t LIMIT 1;
3358 NOTICE: y_trigger: a = 31
3359 NOTICE: y_trigger: a = 32
3360 NOTICE: y_trigger: a = 33
3361 a
3362 ----
3363 31
3364 (1 row)
3366 SELECT * FROM y;
3367 a
3368 ----
3369 1
3370 2
3371 3
3372 4
3373 5
3374 6
3375 7
3376 8
3377 9
3378 10
3379 21
3380 22
3381 23
3382 31
3383 32
3384 33
3385 (16 rows)
3387 DROP TRIGGER y_trig ON y;
3388 CREATE OR REPLACE FUNCTION y_trigger() RETURNS trigger AS $$
3389 begin
3390 raise notice 'y_trigger';
3391 return null;
3392 end;
3393 $$ LANGUAGE plpgsql;
3394 CREATE TRIGGER y_trig AFTER INSERT ON y FOR EACH STATEMENT
3395 EXECUTE PROCEDURE y_trigger();
3396 WITH t AS (
3397 INSERT INTO y
3398 VALUES
3399 (41),
3400 (42),
3401 (43)
3402 RETURNING *
3403 )
3404 SELECT * FROM t;
3405 NOTICE: y_trigger
3406 a
3407 ----
3408 41
3409 42
3410 43
3411 (3 rows)
3413 SELECT * FROM y;
3414 a
3415 ----
3416 1
3417 2
3418 3
3419 4
3420 5
3421 6
3422 7
3423 8
3424 9
3425 10
3426 21
3427 22
3428 23
3429 31
3430 32
3431 33
3432 41
3433 42
3434 43
3435 (19 rows)
3437 DROP TRIGGER y_trig ON y;
3438 DROP FUNCTION y_trigger();
3439 -- WITH attached to inherited UPDATE or DELETE
3440 CREATE TEMP TABLE parent ( id int, val text );
3441 CREATE TEMP TABLE child1 ( ) INHERITS ( parent );
3442 CREATE TEMP TABLE child2 ( ) INHERITS ( parent );
3443 INSERT INTO parent VALUES ( 1, 'p1' );
3444 INSERT INTO child1 VALUES ( 11, 'c11' ),( 12, 'c12' );
3445 INSERT INTO child2 VALUES ( 23, 'c21' ),( 24, 'c22' );
3446 WITH rcte AS ( SELECT sum(id) AS totalid FROM parent )
3447 UPDATE parent SET id = id + totalid FROM rcte;
3448 SELECT * FROM parent;
3449 id | val
3450 ----+-----
3451 72 | p1
3452 82 | c11
3453 83 | c12
3454 94 | c21
3455 95 | c22
3456 (5 rows)
3458 WITH wcte AS ( INSERT INTO child1 VALUES ( 42, 'new' ) RETURNING id AS newid )
3459 UPDATE parent SET id = id + newid FROM wcte;
3460 SELECT * FROM parent;
3461 id | val
3462 -----+-----
3463 114 | p1
3464 42 | new
3465 124 | c11
3466 125 | c12
3467 136 | c21
3468 137 | c22
3469 (6 rows)
3471 WITH rcte AS ( SELECT max(id) AS maxid FROM parent )
3472 DELETE FROM parent USING rcte WHERE id = maxid;
3473 SELECT * FROM parent;
3474 id | val
3475 -----+-----
3476 114 | p1
3477 42 | new
3478 124 | c11
3479 125 | c12
3480 136 | c21
3481 (5 rows)
3483 WITH wcte AS ( INSERT INTO child2 VALUES ( 42, 'new2' ) RETURNING id AS newid )
3484 DELETE FROM parent USING wcte WHERE id = newid;
3485 SELECT * FROM parent;
3486 id | val
3487 -----+------
3488 114 | p1
3489 124 | c11
3490 125 | c12
3491 136 | c21
3492 42 | new2
3493 (5 rows)
3495 -- check EXPLAIN VERBOSE for a wCTE with RETURNING
3496 EXPLAIN (VERBOSE, COSTS OFF)
3497 WITH wcte AS ( INSERT INTO int8_tbl VALUES ( 42, 47 ) RETURNING q2 )
3498 DELETE FROM a_star USING wcte WHERE aa = q2;
3499 QUERY PLAN
3500 ---------------------------------------------------------------------------
3501 Delete on public.a_star
3502 Delete on public.a_star a_star_1
3503 Delete on public.b_star a_star_2
3504 Delete on public.c_star a_star_3
3505 Delete on public.d_star a_star_4
3506 Delete on public.e_star a_star_5
3507 Delete on public.f_star a_star_6
3508 CTE wcte
3509 -> Insert on public.int8_tbl
3510 Output: int8_tbl.q2
3511 -> Result
3512 Output: '42'::bigint, '47'::bigint
3513 -> Hash Join
3514 Output: wcte.*, a_star.tableoid, a_star.ctid
3515 Hash Cond: (a_star.aa = wcte.q2)
3516 -> Append
3517 -> Seq Scan on public.a_star a_star_1
3518 Output: a_star_1.aa, a_star_1.tableoid, a_star_1.ctid
3519 -> Seq Scan on public.b_star a_star_2
3520 Output: a_star_2.aa, a_star_2.tableoid, a_star_2.ctid
3521 -> Seq Scan on public.c_star a_star_3
3522 Output: a_star_3.aa, a_star_3.tableoid, a_star_3.ctid
3523 -> Seq Scan on public.d_star a_star_4
3524 Output: a_star_4.aa, a_star_4.tableoid, a_star_4.ctid
3525 -> Seq Scan on public.e_star a_star_5
3526 Output: a_star_5.aa, a_star_5.tableoid, a_star_5.ctid
3527 -> Seq Scan on public.f_star a_star_6
3528 Output: a_star_6.aa, a_star_6.tableoid, a_star_6.ctid
3529 -> Hash
3530 Output: wcte.*, wcte.q2
3531 -> CTE Scan on wcte
3532 Output: wcte.*, wcte.q2
3533 (32 rows)
3535 -- error cases
3536 -- data-modifying WITH tries to use its own output
3537 WITH RECURSIVE t AS (
3538 INSERT INTO y
3539 SELECT * FROM t
3540 )
3541 VALUES(FALSE);
3542 ERROR: recursive query "t" must not contain data-modifying statements
3543 LINE 1: WITH RECURSIVE t AS (
3544 ^
3545 -- no RETURNING in a referenced data-modifying WITH
3546 WITH t AS (
3547 INSERT INTO y VALUES(0)
3548 )
3549 SELECT * FROM t;
3550 ERROR: WITH query "t" does not have a RETURNING clause
3551 LINE 4: SELECT * FROM t;
3552 ^
3553 -- RETURNING tries to return its own output
3554 WITH RECURSIVE t(action, a) AS (
3555 MERGE INTO y USING (VALUES (11)) v(a) ON y.a = v.a
3556 WHEN NOT MATCHED THEN INSERT VALUES (v.a)
3557 RETURNING merge_action(), (SELECT a FROM t)
3558 )
3559 SELECT * FROM t;
3560 ERROR: recursive query "t" must not contain data-modifying statements
3561 LINE 1: WITH RECURSIVE t(action, a) AS (
3562 ^
3563 -- data-modifying WITH allowed only at the top level
3564 SELECT * FROM (
3565 WITH t AS (UPDATE y SET a=a+1 RETURNING *)
3566 SELECT * FROM t
3567 ) ss;
3568 ERROR: WITH clause containing a data-modifying statement must be at the top level
3569 LINE 2: WITH t AS (UPDATE y SET a=a+1 RETURNING *)
3570 ^
3571 -- most variants of rules aren't allowed
3572 CREATE RULE y_rule AS ON INSERT TO y WHERE a=0 DO INSTEAD DELETE FROM y;
3573 WITH t AS (
3574 INSERT INTO y VALUES(0)
3575 )
3576 VALUES(FALSE);
3577 ERROR: conditional DO INSTEAD rules are not supported for data-modifying statements in WITH
3578 CREATE OR REPLACE RULE y_rule AS ON INSERT TO y DO INSTEAD NOTHING;
3579 WITH t AS (
3580 INSERT INTO y VALUES(0)
3581 )
3582 VALUES(FALSE);
3583 ERROR: DO INSTEAD NOTHING rules are not supported for data-modifying statements in WITH
3584 CREATE OR REPLACE RULE y_rule AS ON INSERT TO y DO INSTEAD NOTIFY foo;
3585 WITH t AS (
3586 INSERT INTO y VALUES(0)
3587 )
3588 VALUES(FALSE);
3589 ERROR: DO INSTEAD NOTIFY rules are not supported for data-modifying statements in WITH
3590 CREATE OR REPLACE RULE y_rule AS ON INSERT TO y DO ALSO NOTIFY foo;
3591 WITH t AS (
3592 INSERT INTO y VALUES(0)
3593 )
3594 VALUES(FALSE);
3595 ERROR: DO ALSO rules are not supported for data-modifying statements in WITH
3596 CREATE OR REPLACE RULE y_rule AS ON INSERT TO y
3597 DO INSTEAD (NOTIFY foo; NOTIFY bar);
3598 WITH t AS (
3599 INSERT INTO y VALUES(0)
3600 )
3601 VALUES(FALSE);
3602 ERROR: multi-statement DO INSTEAD rules are not supported for data-modifying statements in WITH
3603 DROP RULE y_rule ON y;
3604 -- check that parser lookahead for WITH doesn't cause any odd behavior
3605 create table foo (with baz); -- fail, WITH is a reserved word
3606 ERROR: syntax error at or near "with"
3607 LINE 1: create table foo (with baz);
3608 ^
3609 create table foo (with ordinality); -- fail, WITH is a reserved word
3610 ERROR: syntax error at or near "with"
3611 LINE 1: create table foo (with ordinality);
3612 ^
3613 with ordinality as (select 1 as x) select * from ordinality;
3614 x
3615 ---
3616 1
3617 (1 row)
3619 -- check sane response to attempt to modify CTE relation
3620 WITH with_test AS (SELECT 42) INSERT INTO with_test VALUES (1);
3621 ERROR: relation "with_test" does not exist
3622 LINE 1: WITH with_test AS (SELECT 42) INSERT INTO with_test VALUES (...
3623 ^
3624 -- check response to attempt to modify table with same name as a CTE (perhaps
3625 -- surprisingly it works, because CTEs don't hide tables from data-modifying
3626 -- statements)
3627 create temp table with_test (i int);
3628 with with_test as (select 42) insert into with_test select * from with_test;
3629 select * from with_test;
3630 i
3631 ----
3632 42
3633 (1 row)
3635 drop table with_test;