2
-- Tests for common table expressions (WITH query, ... SELECT ...)
5
WITH q1(x,y) AS (SELECT 1,2)
6
SELECT * FROM q1, q1 AS q2;
12
-- Multiple uses are evaluated only once
13
SELECT count(*) FROM (
14
WITH q1(x) AS (SELECT random() FROM generate_series(1, 5))
26
WITH RECURSIVE t(n) AS (
29
SELECT n+1 FROM t WHERE n < 100
37
WITH RECURSIVE t(n) AS (
40
SELECT n+1 FROM t WHERE n < 5
52
-- This is an infinite loop with UNION ALL, but not with UNION
53
WITH RECURSIVE t(n) AS (
64
-- This'd be an infinite loop, but outside query reads only as much as needed
65
WITH RECURSIVE t(n) AS (
69
SELECT * FROM t LIMIT 10;
84
-- UNION case should have same property
85
WITH RECURSIVE t(n) AS (
89
SELECT * FROM t LIMIT 10;
104
-- Test behavior with an unknown-type literal in the WITH
105
WITH q AS (SELECT 'foo' AS x)
106
SELECT x, x IS OF (unknown) as is_unknown FROM q;
112
WITH RECURSIVE t(n) AS (
115
SELECT n || ' bar' FROM t WHERE length(n) < 20
117
SELECT n, n IS OF (text) as is_text FROM t;
119
-------------------------+---------
124
foo bar bar bar bar | t
125
foo bar bar bar bar bar | t
129
-- Some examples with a tree
131
-- department structure represented here is as follows:
133
-- ROOT-+->A-+->B-+->C
137
CREATE TEMP TABLE department (
138
id INTEGER PRIMARY KEY, -- department ID
139
parent_department INTEGER REFERENCES department, -- upper department ID
140
name TEXT -- department name
142
NOTICE: CREATE TABLE / PRIMARY KEY will create implicit index "department_pkey" for table "department"
143
INSERT INTO department VALUES (0, NULL, 'ROOT');
144
INSERT INTO department VALUES (1, 0, 'A');
145
INSERT INTO department VALUES (2, 1, 'B');
146
INSERT INTO department VALUES (3, 2, 'C');
147
INSERT INTO department VALUES (4, 2, 'D');
148
INSERT INTO department VALUES (5, 0, 'E');
149
INSERT INTO department VALUES (6, 4, 'F');
150
INSERT INTO department VALUES (7, 5, 'G');
151
-- extract all departments under 'A'. Result should be A, B, C, D and F
152
WITH RECURSIVE subdepartment AS
154
-- non recursive term
155
SELECT name as root_name, * FROM department WHERE name = 'A'
158
SELECT sd.root_name, d.* FROM department AS d, subdepartment AS sd
159
WHERE d.parent_department = sd.id
161
SELECT * FROM subdepartment ORDER BY name;
162
root_name | id | parent_department | name
163
-----------+----+-------------------+------
171
-- extract all departments under 'A' with "level" number
172
WITH RECURSIVE subdepartment(level, id, parent_department, name) AS
174
-- non recursive term
175
SELECT 1, * FROM department WHERE name = 'A'
178
SELECT sd.level + 1, d.* FROM department AS d, subdepartment AS sd
179
WHERE d.parent_department = sd.id
181
SELECT * FROM subdepartment ORDER BY name;
182
level | id | parent_department | name
183
-------+----+-------------------+------
191
-- extract all departments under 'A' with "level" number.
192
-- Only shows level 2 or more
193
WITH RECURSIVE subdepartment(level, id, parent_department, name) AS
195
-- non recursive term
196
SELECT 1, * FROM department WHERE name = 'A'
199
SELECT sd.level + 1, d.* FROM department AS d, subdepartment AS sd
200
WHERE d.parent_department = sd.id
202
SELECT * FROM subdepartment WHERE level >= 2 ORDER BY name;
203
level | id | parent_department | name
204
-------+----+-------------------+------
211
-- "RECURSIVE" is ignored if the query has no self-reference
212
WITH RECURSIVE subdepartment AS
214
-- note lack of recursive UNION structure
215
SELECT * FROM department WHERE name = 'A'
217
SELECT * FROM subdepartment ORDER BY name;
218
id | parent_department | name
219
----+-------------------+------
224
SELECT count(*) FROM (
225
WITH RECURSIVE t(n) AS (
226
SELECT 1 UNION ALL SELECT n + 1 FROM t WHERE n < 500
228
SELECT * FROM t) AS t WHERE n < (
229
SELECT count(*) FROM (
230
WITH RECURSIVE t(n) AS (
231
SELECT 1 UNION ALL SELECT n + 1 FROM t WHERE n < 100
233
SELECT * FROM t WHERE n < 50000
234
) AS t WHERE n < 100);
240
-- use same CTE twice at different subquery levels
242
SELECT hundred, sum(ten) FROM tenk1 GROUP BY hundred
244
SELECT count(*) FROM q1 WHERE y > (SELECT sum(y)/100 FROM q1 qsub);
251
CREATE TEMPORARY VIEW vsubdepartment AS
252
WITH RECURSIVE subdepartment AS
254
-- non recursive term
255
SELECT * FROM department WHERE name = 'A'
258
SELECT d.* FROM department AS d, subdepartment AS sd
259
WHERE d.parent_department = sd.id
261
SELECT * FROM subdepartment;
262
SELECT * FROM vsubdepartment ORDER BY name;
263
id | parent_department | name
264
----+-------------------+------
272
-- Check reverse listing
273
SELECT pg_get_viewdef('vsubdepartment'::regclass);
275
---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
276
WITH RECURSIVE subdepartment AS (SELECT department.id, department.parent_department, department.name FROM department WHERE (department.name = 'A'::text) UNION ALL SELECT d.id, d.parent_department, d.name FROM department d, subdepartment sd WHERE (d.parent_department = sd.id)) SELECT subdepartment.id, subdepartment.parent_department, subdepartment.name FROM subdepartment;
279
SELECT pg_get_viewdef('vsubdepartment'::regclass, true);
281
--------------------------------------------------------------------------------------
282
WITH RECURSIVE subdepartment AS ( +
283
SELECT department.id, department.parent_department, department.name+
285
WHERE department.name = 'A'::text +
287
SELECT d.id, d.parent_department, d.name +
288
FROM department d, subdepartment sd +
289
WHERE d.parent_department = sd.id +
291
SELECT subdepartment.id, subdepartment.parent_department, subdepartment.name +
295
-- corner case in which sub-WITH gets initialized first
296
with recursive q as (
297
select * from department
299
(with x as (select * from q)
302
select * from q limit 24;
303
id | parent_department | name
304
----+-------------------+------
331
with recursive q as (
332
select * from department
334
(with recursive x as (
335
select * from department
337
(select * from q union all select * from x)
341
select * from q limit 32;
342
id | parent_department | name
343
----+-------------------+------
378
-- recursive term has sub-UNION
379
WITH RECURSIVE t(i,j) AS (
382
SELECT t2.i, t.j+1 FROM
383
(SELECT 2 AS i UNION ALL SELECT 3 AS i) AS t2
384
JOIN t ON (t2.i = t.i+1))
394
-- different tree example
396
CREATE TEMPORARY TABLE tree(
397
id INTEGER PRIMARY KEY,
398
parent_id INTEGER REFERENCES tree(id)
400
NOTICE: CREATE TABLE / PRIMARY KEY will create implicit index "tree_pkey" for table "tree"
402
VALUES (1, NULL), (2, 1), (3,1), (4,2), (5,2), (6,2), (7,3), (8,3),
403
(9,4), (10,4), (11,7), (12,7), (13,7), (14, 9), (15,11), (16,11);
405
-- get all paths from "second level" nodes to leaf nodes
407
WITH RECURSIVE t(id, path) AS (
408
VALUES(1,ARRAY[]::integer[])
410
SELECT tree.id, t.path || tree.id
411
FROM tree JOIN t ON (tree.parent_id = t.id)
413
SELECT t1.*, t2.* FROM t AS t1 JOIN t AS t2 ON
414
(t1.path[1] = t2.path[1] AND
415
array_upper(t1.path,1) = 1 AND
416
array_upper(t2.path,1) > 1)
417
ORDER BY t1.id, t2.id;
418
id | path | id | path
419
----+------+----+-------------
423
2 | {2} | 9 | {2,4,9}
424
2 | {2} | 10 | {2,4,10}
425
2 | {2} | 14 | {2,4,9,14}
428
3 | {3} | 11 | {3,7,11}
429
3 | {3} | 12 | {3,7,12}
430
3 | {3} | 13 | {3,7,13}
431
3 | {3} | 15 | {3,7,11,15}
432
3 | {3} | 16 | {3,7,11,16}
436
WITH RECURSIVE t(id, path) AS (
437
VALUES(1,ARRAY[]::integer[])
439
SELECT tree.id, t.path || tree.id
440
FROM tree JOIN t ON (tree.parent_id = t.id)
442
SELECT t1.id, count(t2.*) FROM t AS t1 JOIN t AS t2 ON
443
(t1.path[1] = t2.path[1] AND
444
array_upper(t1.path,1) = 1 AND
445
array_upper(t2.path,1) > 1)
454
-- this variant tickled a whole-row-variable bug in 8.4devel
455
WITH RECURSIVE t(id, path) AS (
456
VALUES(1,ARRAY[]::integer[])
458
SELECT tree.id, t.path || tree.id
459
FROM tree JOIN t ON (tree.parent_id = t.id)
461
SELECT t1.id, t2.path, t2 FROM t AS t1 JOIN t AS t2 ON
464
----+-------------+--------------------
468
4 | {2,4} | (4,"{2,4}")
469
5 | {2,5} | (5,"{2,5}")
470
6 | {2,6} | (6,"{2,6}")
471
7 | {3,7} | (7,"{3,7}")
472
8 | {3,8} | (8,"{3,8}")
473
9 | {2,4,9} | (9,"{2,4,9}")
474
10 | {2,4,10} | (10,"{2,4,10}")
475
11 | {3,7,11} | (11,"{3,7,11}")
476
12 | {3,7,12} | (12,"{3,7,12}")
477
13 | {3,7,13} | (13,"{3,7,13}")
478
14 | {2,4,9,14} | (14,"{2,4,9,14}")
479
15 | {3,7,11,15} | (15,"{3,7,11,15}")
480
16 | {3,7,11,16} | (16,"{3,7,11,16}")
484
-- test cycle detection
486
create temp table graph( f int, t int, label text );
487
insert into graph values
488
(1, 2, 'arc 1 -> 2'),
489
(1, 3, 'arc 1 -> 3'),
490
(2, 3, 'arc 2 -> 3'),
491
(1, 4, 'arc 1 -> 4'),
492
(4, 5, 'arc 4 -> 5'),
493
(5, 1, 'arc 5 -> 1');
494
with recursive search_graph(f, t, label, path, cycle) as (
495
select *, array[row(g.f, g.t)], false from graph g
497
select g.*, path || row(g.f, g.t), row(g.f, g.t) = any(path)
498
from graph g, search_graph sg
499
where g.f = sg.t and not cycle
501
select * from search_graph;
502
f | t | label | path | cycle
503
---+---+------------+-------------------------------------------+-------
504
1 | 2 | arc 1 -> 2 | {"(1,2)"} | f
505
1 | 3 | arc 1 -> 3 | {"(1,3)"} | f
506
2 | 3 | arc 2 -> 3 | {"(2,3)"} | f
507
1 | 4 | arc 1 -> 4 | {"(1,4)"} | f
508
4 | 5 | arc 4 -> 5 | {"(4,5)"} | f
509
5 | 1 | arc 5 -> 1 | {"(5,1)"} | f
510
1 | 2 | arc 1 -> 2 | {"(5,1)","(1,2)"} | f
511
1 | 3 | arc 1 -> 3 | {"(5,1)","(1,3)"} | f
512
1 | 4 | arc 1 -> 4 | {"(5,1)","(1,4)"} | f
513
2 | 3 | arc 2 -> 3 | {"(1,2)","(2,3)"} | f
514
4 | 5 | arc 4 -> 5 | {"(1,4)","(4,5)"} | f
515
5 | 1 | arc 5 -> 1 | {"(4,5)","(5,1)"} | f
516
1 | 2 | arc 1 -> 2 | {"(4,5)","(5,1)","(1,2)"} | f
517
1 | 3 | arc 1 -> 3 | {"(4,5)","(5,1)","(1,3)"} | f
518
1 | 4 | arc 1 -> 4 | {"(4,5)","(5,1)","(1,4)"} | f
519
2 | 3 | arc 2 -> 3 | {"(5,1)","(1,2)","(2,3)"} | f
520
4 | 5 | arc 4 -> 5 | {"(5,1)","(1,4)","(4,5)"} | f
521
5 | 1 | arc 5 -> 1 | {"(1,4)","(4,5)","(5,1)"} | f
522
1 | 2 | arc 1 -> 2 | {"(1,4)","(4,5)","(5,1)","(1,2)"} | f
523
1 | 3 | arc 1 -> 3 | {"(1,4)","(4,5)","(5,1)","(1,3)"} | f
524
1 | 4 | arc 1 -> 4 | {"(1,4)","(4,5)","(5,1)","(1,4)"} | t
525
2 | 3 | arc 2 -> 3 | {"(4,5)","(5,1)","(1,2)","(2,3)"} | f
526
4 | 5 | arc 4 -> 5 | {"(4,5)","(5,1)","(1,4)","(4,5)"} | t
527
5 | 1 | arc 5 -> 1 | {"(5,1)","(1,4)","(4,5)","(5,1)"} | t
528
2 | 3 | arc 2 -> 3 | {"(1,4)","(4,5)","(5,1)","(1,2)","(2,3)"} | f
531
-- ordering by the path column has same effect as SEARCH DEPTH FIRST
532
with recursive search_graph(f, t, label, path, cycle) as (
533
select *, array[row(g.f, g.t)], false from graph g
535
select g.*, path || row(g.f, g.t), row(g.f, g.t) = any(path)
536
from graph g, search_graph sg
537
where g.f = sg.t and not cycle
539
select * from search_graph order by path;
540
f | t | label | path | cycle
541
---+---+------------+-------------------------------------------+-------
542
1 | 2 | arc 1 -> 2 | {"(1,2)"} | f
543
2 | 3 | arc 2 -> 3 | {"(1,2)","(2,3)"} | f
544
1 | 3 | arc 1 -> 3 | {"(1,3)"} | f
545
1 | 4 | arc 1 -> 4 | {"(1,4)"} | f
546
4 | 5 | arc 4 -> 5 | {"(1,4)","(4,5)"} | f
547
5 | 1 | arc 5 -> 1 | {"(1,4)","(4,5)","(5,1)"} | f
548
1 | 2 | arc 1 -> 2 | {"(1,4)","(4,5)","(5,1)","(1,2)"} | f
549
2 | 3 | arc 2 -> 3 | {"(1,4)","(4,5)","(5,1)","(1,2)","(2,3)"} | f
550
1 | 3 | arc 1 -> 3 | {"(1,4)","(4,5)","(5,1)","(1,3)"} | f
551
1 | 4 | arc 1 -> 4 | {"(1,4)","(4,5)","(5,1)","(1,4)"} | t
552
2 | 3 | arc 2 -> 3 | {"(2,3)"} | f
553
4 | 5 | arc 4 -> 5 | {"(4,5)"} | f
554
5 | 1 | arc 5 -> 1 | {"(4,5)","(5,1)"} | f
555
1 | 2 | arc 1 -> 2 | {"(4,5)","(5,1)","(1,2)"} | f
556
2 | 3 | arc 2 -> 3 | {"(4,5)","(5,1)","(1,2)","(2,3)"} | f
557
1 | 3 | arc 1 -> 3 | {"(4,5)","(5,1)","(1,3)"} | f
558
1 | 4 | arc 1 -> 4 | {"(4,5)","(5,1)","(1,4)"} | f
559
4 | 5 | arc 4 -> 5 | {"(4,5)","(5,1)","(1,4)","(4,5)"} | t
560
5 | 1 | arc 5 -> 1 | {"(5,1)"} | f
561
1 | 2 | arc 1 -> 2 | {"(5,1)","(1,2)"} | f
562
2 | 3 | arc 2 -> 3 | {"(5,1)","(1,2)","(2,3)"} | f
563
1 | 3 | arc 1 -> 3 | {"(5,1)","(1,3)"} | f
564
1 | 4 | arc 1 -> 4 | {"(5,1)","(1,4)"} | f
565
4 | 5 | arc 4 -> 5 | {"(5,1)","(1,4)","(4,5)"} | f
566
5 | 1 | arc 5 -> 1 | {"(5,1)","(1,4)","(4,5)","(5,1)"} | t
570
-- test multiple WITH queries
573
y (id) AS (VALUES (1)),
574
x (id) AS (SELECT * FROM y UNION ALL SELECT id+1 FROM x WHERE id < 5)
585
-- forward reference OK
587
x(id) AS (SELECT * FROM y UNION ALL SELECT id+1 FROM x WHERE id < 5),
588
y(id) AS (values (1))
601
(VALUES (1) UNION ALL SELECT id+1 FROM x WHERE id < 5),
603
(VALUES (1) UNION ALL SELECT id+1 FROM y WHERE id < 10)
604
SELECT y.*, x.* FROM y LEFT JOIN x USING (id);
621
(VALUES (1) UNION ALL SELECT id+1 FROM x WHERE id < 5),
623
(VALUES (1) UNION ALL SELECT id+1 FROM x WHERE id < 10)
624
SELECT y.*, x.* FROM y LEFT JOIN x USING (id);
637
(SELECT 1 UNION ALL SELECT id+1 FROM x WHERE id < 3 ),
639
(SELECT * FROM x UNION ALL SELECT * FROM x),
641
(SELECT * FROM x UNION ALL SELECT id+1 FROM z WHERE id < 10)
676
(SELECT 1 UNION ALL SELECT id+1 FROM x WHERE id < 3 ),
678
(SELECT * FROM x UNION ALL SELECT * FROM x),
680
(SELECT * FROM y UNION ALL SELECT id+1 FROM z WHERE id < 10)
741
-- Test WITH attached to a data-modifying statement
743
CREATE TEMPORARY TABLE y (a INTEGER);
744
INSERT INTO y SELECT generate_series(1, 10);
749
SELECT a+20 FROM t RETURNING *;
792
UPDATE y SET a = y.a-10 FROM t WHERE y.a > 20 AND t.a = y.a RETURNING y.a;
832
WITH RECURSIVE t(a) AS (
835
SELECT a+1 FROM t WHERE a < 50
837
DELETE FROM y USING t WHERE t.a = y.a RETURNING y.a;
872
WITH RECURSIVE x(n) AS (SELECT 1 INTERSECT SELECT n+1 FROM x)
874
ERROR: recursive query "x" does not have the form non-recursive-term UNION [ALL] recursive-term
875
LINE 1: WITH RECURSIVE x(n) AS (SELECT 1 INTERSECT SELECT n+1 FROM x...
877
WITH RECURSIVE x(n) AS (SELECT 1 INTERSECT ALL SELECT n+1 FROM x)
879
ERROR: recursive query "x" does not have the form non-recursive-term UNION [ALL] recursive-term
880
LINE 1: WITH RECURSIVE x(n) AS (SELECT 1 INTERSECT ALL SELECT n+1 FR...
883
WITH RECURSIVE x(n) AS (SELECT 1 EXCEPT SELECT n+1 FROM x)
885
ERROR: recursive query "x" does not have the form non-recursive-term UNION [ALL] recursive-term
886
LINE 1: WITH RECURSIVE x(n) AS (SELECT 1 EXCEPT SELECT n+1 FROM x)
888
WITH RECURSIVE x(n) AS (SELECT 1 EXCEPT ALL SELECT n+1 FROM x)
890
ERROR: recursive query "x" does not have the form non-recursive-term UNION [ALL] recursive-term
891
LINE 1: WITH RECURSIVE x(n) AS (SELECT 1 EXCEPT ALL SELECT n+1 FROM ...
893
-- no non-recursive term
894
WITH RECURSIVE x(n) AS (SELECT n FROM x)
896
ERROR: recursive query "x" does not have the form non-recursive-term UNION [ALL] recursive-term
897
LINE 1: WITH RECURSIVE x(n) AS (SELECT n FROM x)
899
-- recursive term in the left hand side (strictly speaking, should allow this)
900
WITH RECURSIVE x(n) AS (SELECT n FROM x UNION ALL SELECT 1)
902
ERROR: recursive reference to query "x" must not appear within its non-recursive term
903
LINE 1: WITH RECURSIVE x(n) AS (SELECT n FROM x UNION ALL SELECT 1)
905
CREATE TEMPORARY TABLE y (a INTEGER);
906
INSERT INTO y SELECT generate_series(1, 10);
908
WITH RECURSIVE x(n) AS (SELECT a FROM y WHERE a = 1
910
SELECT x.n+1 FROM y LEFT JOIN x ON x.n = y.a WHERE n < 10)
912
ERROR: recursive reference to query "x" must not appear within an outer join
913
LINE 3: SELECT x.n+1 FROM y LEFT JOIN x ON x.n = y.a WHERE n < 10)
916
WITH RECURSIVE x(n) AS (SELECT a FROM y WHERE a = 1
918
SELECT x.n+1 FROM x RIGHT JOIN y ON x.n = y.a WHERE n < 10)
920
ERROR: recursive reference to query "x" must not appear within an outer join
921
LINE 3: SELECT x.n+1 FROM x RIGHT JOIN y ON x.n = y.a WHERE n < 10)
924
WITH RECURSIVE x(n) AS (SELECT a FROM y WHERE a = 1
926
SELECT x.n+1 FROM x FULL JOIN y ON x.n = y.a WHERE n < 10)
928
ERROR: recursive reference to query "x" must not appear within an outer join
929
LINE 3: SELECT x.n+1 FROM x FULL JOIN y ON x.n = y.a WHERE n < 10)
932
WITH RECURSIVE x(n) AS (SELECT 1 UNION ALL SELECT n+1 FROM x
933
WHERE n IN (SELECT * FROM x))
935
ERROR: recursive reference to query "x" must not appear within a subquery
936
LINE 2: WHERE n IN (SELECT * FROM x))
938
-- aggregate functions
939
WITH RECURSIVE x(n) AS (SELECT 1 UNION ALL SELECT count(*) FROM x)
941
ERROR: aggregate functions not allowed in a recursive query's recursive term
942
LINE 1: WITH RECURSIVE x(n) AS (SELECT 1 UNION ALL SELECT count(*) F...
944
WITH RECURSIVE x(n) AS (SELECT 1 UNION ALL SELECT sum(n) FROM x)
946
ERROR: aggregate functions not allowed in a recursive query's recursive term
947
LINE 1: WITH RECURSIVE x(n) AS (SELECT 1 UNION ALL SELECT sum(n) FRO...
950
WITH RECURSIVE x(n) AS (SELECT 1 UNION ALL SELECT n+1 FROM x ORDER BY 1)
952
ERROR: ORDER BY in a recursive query is not implemented
953
LINE 1: ...VE x(n) AS (SELECT 1 UNION ALL SELECT n+1 FROM x ORDER BY 1)
956
WITH RECURSIVE x(n) AS (SELECT 1 UNION ALL SELECT n+1 FROM x LIMIT 10 OFFSET 1)
958
ERROR: OFFSET in a recursive query is not implemented
959
LINE 1: ... AS (SELECT 1 UNION ALL SELECT n+1 FROM x LIMIT 10 OFFSET 1)
962
WITH RECURSIVE x(n) AS (SELECT 1 UNION ALL SELECT n+1 FROM x FOR UPDATE)
964
ERROR: FOR UPDATE/SHARE in a recursive query is not implemented
965
-- target list has a recursive query name
966
WITH RECURSIVE x(id) AS (values (1)
968
SELECT (SELECT * FROM x) FROM x WHERE id < 5
970
ERROR: recursive reference to query "x" must not appear within a subquery
971
LINE 3: SELECT (SELECT * FROM x) FROM x WHERE id < 5
973
-- mutual recursive query (not implemented)
975
x (id) AS (SELECT 1 UNION ALL SELECT id+1 FROM y WHERE id < 5),
976
y (id) AS (SELECT 1 UNION ALL SELECT id+1 FROM x WHERE id < 5)
978
ERROR: mutual recursion between WITH items is not implemented
979
LINE 2: x (id) AS (SELECT 1 UNION ALL SELECT id+1 FROM y WHERE id ...
981
-- non-linear recursion is not allowed
982
WITH RECURSIVE foo(i) AS
985
(SELECT i+1 FROM foo WHERE i < 10
987
SELECT i+1 FROM foo WHERE i < 5)
989
ERROR: recursive reference to query "foo" must not appear more than once
990
LINE 6: SELECT i+1 FROM foo WHERE i < 5)
992
WITH RECURSIVE foo(i) AS
996
(SELECT i+1 FROM foo WHERE i < 10
998
SELECT i+1 FROM foo WHERE i < 5) AS t
1000
ERROR: recursive reference to query "foo" must not appear more than once
1001
LINE 7: SELECT i+1 FROM foo WHERE i < 5) AS t
1003
WITH RECURSIVE foo(i) AS
1006
(SELECT i+1 FROM foo WHERE i < 10
1008
SELECT i+1 FROM foo WHERE i < 5)
1009
) SELECT * FROM foo;
1010
ERROR: recursive reference to query "foo" must not appear within EXCEPT
1011
LINE 6: SELECT i+1 FROM foo WHERE i < 5)
1013
WITH RECURSIVE foo(i) AS
1016
(SELECT i+1 FROM foo WHERE i < 10
1018
SELECT i+1 FROM foo WHERE i < 5)
1019
) SELECT * FROM foo;
1020
ERROR: recursive reference to query "foo" must not appear more than once
1021
LINE 6: SELECT i+1 FROM foo WHERE i < 5)
1023
-- Wrong type induced from non-recursive term
1024
WITH RECURSIVE foo(i) AS
1025
(SELECT i FROM (VALUES(1),(2)) t(i)
1027
SELECT (i+1)::numeric(10,0) FROM foo WHERE i < 10)
1029
ERROR: recursive query "foo" column 1 has type integer in non-recursive term but type numeric overall
1030
LINE 2: (SELECT i FROM (VALUES(1),(2)) t(i)
1032
HINT: Cast the output of the non-recursive term to the correct type.
1033
-- rejects different typmod, too (should we allow this?)
1034
WITH RECURSIVE foo(i) AS
1035
(SELECT i::numeric(3,0) FROM (VALUES(1),(2)) t(i)
1037
SELECT (i+1)::numeric(10,0) FROM foo WHERE i < 10)
1039
ERROR: recursive query "foo" column 1 has type numeric(3,0) in non-recursive term but type numeric overall
1040
LINE 2: (SELECT i::numeric(3,0) FROM (VALUES(1),(2)) t(i)
1042
HINT: Cast the output of the non-recursive term to the correct type.
1043
-- disallow OLD/NEW reference in CTE
1044
CREATE TEMPORARY TABLE x (n integer);
1045
CREATE RULE r2 AS ON UPDATE TO x DO INSTEAD
1046
WITH t AS (SELECT OLD.*) UPDATE y SET a = t.n FROM t;
1047
ERROR: cannot refer to OLD within WITH query
1049
-- test for bug #4902
1051
with cte(foo) as ( values(42) ) values((select foo from cte));
1057
with cte(foo) as ( select 42 ) select * from ((select foo from cte)) q;
1063
-- test CTE referencing an outer-level variable (to see that changed-parameter
1064
-- signaling still works properly after fixing this bug)
1065
select ( with cte(foo) as ( values(f1) )
1066
select (select foo from cte) )
1077
select ( with cte(foo) as ( values(f1) )
1078
values((select foo from cte)) )
1090
-- test for nested-recursive-WITH bug
1092
WITH RECURSIVE t(j) AS (
1093
WITH RECURSIVE s(i) AS (
1096
SELECT i+1 FROM s WHERE i < 10
1100
SELECT j+1 FROM t WHERE j < 10
1163
-- Data-modifying statements in WITH
1165
-- INSERT ... RETURNING
1221
-- UPDATE ... RETURNING
1277
-- DELETE ... RETURNING
1313
-- forward reference
1314
WITH RECURSIVE t AS (
1316
SELECT a+5 FROM t2 WHERE a > 5
1319
UPDATE y SET a=a-11 RETURNING *
1365
-- unconditional DO INSTEAD rule
1366
CREATE RULE y_rule AS ON DELETE TO y DO INSTEAD
1367
INSERT INTO y VALUES(42) RETURNING *;
1369
DELETE FROM y RETURNING *
1399
DROP RULE y_rule ON y;
1400
-- a truly recursive CTE in the same list
1401
WITH RECURSIVE t(a) AS (
1404
SELECT a+1 FROM t WHERE a+1 < 5
1407
SELECT * FROM t RETURNING *
1409
SELECT * FROM t2 JOIN y USING (a) ORDER BY a;
1446
-- data-modifying WITH in a modifying statement
1452
INSERT INTO y SELECT -a FROM t RETURNING *;
1500
-- check that WITH query is run to completion even if outer query isn't
1502
UPDATE y SET a = a * 100 RETURNING *
1504
SELECT * FROM t LIMIT 10;
1546
-- check that run to completion happens in proper ordering
1548
INSERT INTO y SELECT generate_series(1, 3);
1549
CREATE TEMPORARY TABLE yy (a INTEGER);
1550
WITH RECURSIVE t1 AS (
1551
INSERT INTO y SELECT * FROM y RETURNING *
1553
INSERT INTO yy SELECT * FROM t1 RETURNING *
1580
WITH RECURSIVE t1 AS (
1581
INSERT INTO yy SELECT * FROM t2 RETURNING *
1583
INSERT INTO y SELECT * FROM y RETURNING *
1624
INSERT INTO y SELECT generate_series(1, 10);
1625
CREATE FUNCTION y_trigger() RETURNS trigger AS $$
1627
raise notice 'y_trigger: a = %', new.a;
1630
$$ LANGUAGE plpgsql;
1631
CREATE TRIGGER y_trig BEFORE INSERT ON y FOR EACH ROW
1632
EXECUTE PROCEDURE y_trigger();
1642
NOTICE: y_trigger: a = 21
1643
NOTICE: y_trigger: a = 22
1644
NOTICE: y_trigger: a = 23
1670
DROP TRIGGER y_trig ON y;
1671
CREATE TRIGGER y_trig AFTER INSERT ON y FOR EACH ROW
1672
EXECUTE PROCEDURE y_trigger();
1681
SELECT * FROM t LIMIT 1;
1682
NOTICE: y_trigger: a = 31
1683
NOTICE: y_trigger: a = 32
1684
NOTICE: y_trigger: a = 33
1711
DROP TRIGGER y_trig ON y;
1712
CREATE OR REPLACE FUNCTION y_trigger() RETURNS trigger AS $$
1714
raise notice 'y_trigger';
1717
$$ LANGUAGE plpgsql;
1718
CREATE TRIGGER y_trig AFTER INSERT ON y FOR EACH STATEMENT
1719
EXECUTE PROCEDURE y_trigger();
1761
DROP TRIGGER y_trig ON y;
1762
DROP FUNCTION y_trigger();
1764
-- data-modifying WITH tries to use its own output
1765
WITH RECURSIVE t AS (
1770
ERROR: recursive query "t" must not contain data-modifying statements
1771
LINE 1: WITH RECURSIVE t AS (
1773
-- no RETURNING in a referenced data-modifying WITH
1775
INSERT INTO y VALUES(0)
1778
ERROR: WITH query "t" does not have a RETURNING clause
1779
LINE 4: SELECT * FROM t;
1781
-- data-modifying WITH allowed only at the top level
1783
WITH t AS (UPDATE y SET a=a+1 RETURNING *)
1786
ERROR: WITH clause containing a data-modifying statement must be at the top level
1787
LINE 2: WITH t AS (UPDATE y SET a=a+1 RETURNING *)
1789
-- most variants of rules aren't allowed
1790
CREATE RULE y_rule AS ON INSERT TO y WHERE a=0 DO INSTEAD DELETE FROM y;
1792
INSERT INTO y VALUES(0)
1795
ERROR: conditional DO INSTEAD rules are not supported for data-modifying statements in WITH
1796
DROP RULE y_rule ON y;
added
removed
src/test/regress/expected/with.out
