granicus.if.org Git - postgresql/blob - src/test/regress/sql/with.sql

   1 --
   2 -- Tests for common table expressions (WITH query, ... SELECT ...)
   3 --
   4
   5 -- Basic WITH
   6 WITH q1(x,y) AS (SELECT 1,2)
   7 SELECT * FROM q1, q1 AS q2;
   8
   9 -- Multiple uses are evaluated only once
  10 SELECT count(*) FROM (
  11   WITH q1(x) AS (SELECT random() FROM generate_series(1, 5))
  12     SELECT * FROM q1
  13   UNION
  14     SELECT * FROM q1
  15 ) ss;
  16
  17 -- WITH RECURSIVE
  18
  19 -- sum of 1..100
  20 WITH RECURSIVE t(n) AS (
  21     VALUES (1)
  22 UNION ALL
  23     SELECT n+1 FROM t WHERE n < 100
  24 )
  25 SELECT sum(n) FROM t;
  26
  27 WITH RECURSIVE t(n) AS (
  28     SELECT (VALUES(1))
  29 UNION ALL
  30     SELECT n+1 FROM t WHERE n < 5
  31 )
  32 SELECT * FROM t;
  33
  34 -- This is an infinite loop with UNION ALL, but not with UNION
  35 WITH RECURSIVE t(n) AS (
  36     SELECT 1
  37 UNION
  38     SELECT 10-n FROM t)
  39 SELECT * FROM t;
  40
  41 -- This'd be an infinite loop, but outside query reads only as much as needed
  42 WITH RECURSIVE t(n) AS (
  43     VALUES (1)
  44 UNION ALL
  45     SELECT n+1 FROM t)
  46 SELECT * FROM t LIMIT 10;
  47
  48 -- UNION case should have same property
  49 WITH RECURSIVE t(n) AS (
  50     SELECT 1
  51 UNION
  52     SELECT n+1 FROM t)
  53 SELECT * FROM t LIMIT 10;
  54
  55 -- Test behavior with an unknown-type literal in the WITH
  56 WITH q AS (SELECT 'foo' AS x)
  57 SELECT x, x IS OF (unknown) as is_unknown FROM q;
  58
  59 WITH RECURSIVE t(n) AS (
  60     SELECT 'foo'
  61 UNION ALL
  62     SELECT n || ' bar' FROM t WHERE length(n) < 20
  63 )
  64 SELECT n, n IS OF (text) as is_text FROM t;
  65
  66 --
  67 -- Some examples with a tree
  68 --
  69 -- department structure represented here is as follows:
  70 --
  71 -- ROOT-+->A-+->B-+->C
  72 --      |         |
  73 --      |         +->D-+->F
  74 --      +->E-+->G
  75
  76 CREATE TEMP TABLE department (
  77         id INTEGER PRIMARY KEY,  -- department ID
  78         parent_department INTEGER REFERENCES department, -- upper department ID
  79         name TEXT -- department name
  80 );
  81
  82 INSERT INTO department VALUES (0, NULL, 'ROOT');
  83 INSERT INTO department VALUES (1, 0, 'A');
  84 INSERT INTO department VALUES (2, 1, 'B');
  85 INSERT INTO department VALUES (3, 2, 'C');
  86 INSERT INTO department VALUES (4, 2, 'D');
  87 INSERT INTO department VALUES (5, 0, 'E');
  88 INSERT INTO department VALUES (6, 4, 'F');
  89 INSERT INTO department VALUES (7, 5, 'G');
  90
  91
  92 -- extract all departments under 'A'. Result should be A, B, C, D and F
  93 WITH RECURSIVE subdepartment AS
  94 (
  95         -- non recursive term
  96         SELECT name as root_name, * FROM department WHERE name = 'A'
  97
  98         UNION ALL
  99
 100         -- recursive term
 101         SELECT sd.root_name, d.* FROM department AS d, subdepartment AS sd
 102                 WHERE d.parent_department = sd.id
 103 )
 104 SELECT * FROM subdepartment ORDER BY name;
 105
 106 -- extract all departments under 'A' with "level" number
 107 WITH RECURSIVE subdepartment(level, id, parent_department, name) AS
 108 (
 109         -- non recursive term
 110         SELECT 1, * FROM department WHERE name = 'A'
 111
 112         UNION ALL
 113
 114         -- recursive term
 115         SELECT sd.level + 1, d.* FROM department AS d, subdepartment AS sd
 116                 WHERE d.parent_department = sd.id
 117 )
 118 SELECT * FROM subdepartment ORDER BY name;
 119
 120 -- extract all departments under 'A' with "level" number.
 121 -- Only shows level 2 or more
 122 WITH RECURSIVE subdepartment(level, id, parent_department, name) AS
 123 (
 124         -- non recursive term
 125         SELECT 1, * FROM department WHERE name = 'A'
 126
 127         UNION ALL
 128
 129         -- recursive term
 130         SELECT sd.level + 1, d.* FROM department AS d, subdepartment AS sd
 131                 WHERE d.parent_department = sd.id
 132 )
 133 SELECT * FROM subdepartment WHERE level >= 2 ORDER BY name;
 134
 135 -- "RECURSIVE" is ignored if the query has no self-reference
 136 WITH RECURSIVE subdepartment AS
 137 (
 138         -- note lack of recursive UNION structure
 139         SELECT * FROM department WHERE name = 'A'
 140 )
 141 SELECT * FROM subdepartment ORDER BY name;
 142
 143 -- inside subqueries
 144 SELECT count(*) FROM (
 145     WITH RECURSIVE t(n) AS (
 146         SELECT 1 UNION ALL SELECT n + 1 FROM t WHERE n < 500
 147     )
 148     SELECT * FROM t) AS t WHERE n < (
 149         SELECT count(*) FROM (
 150             WITH RECURSIVE t(n) AS (
 151                    SELECT 1 UNION ALL SELECT n + 1 FROM t WHERE n < 100
 152                 )
 153             SELECT * FROM t WHERE n < 50000
 154          ) AS t WHERE n < 100);
 155
 156 -- use same CTE twice at different subquery levels
 157 WITH q1(x,y) AS (
 158     SELECT hundred, sum(ten) FROM tenk1 GROUP BY hundred
 159   )
 160 SELECT count(*) FROM q1 WHERE y > (SELECT sum(y)/100 FROM q1 qsub);
 161
 162 -- via a VIEW
 163 CREATE TEMPORARY VIEW vsubdepartment AS
 164         WITH RECURSIVE subdepartment AS
 165         (
 166                  -- non recursive term
 167                 SELECT * FROM department WHERE name = 'A'
 168                 UNION ALL
 169                 -- recursive term
 170                 SELECT d.* FROM department AS d, subdepartment AS sd
 171                         WHERE d.parent_department = sd.id
 172         )
 173         SELECT * FROM subdepartment;
 174
 175 SELECT * FROM vsubdepartment ORDER BY name;
 176
 177 -- Check reverse listing
 178 SELECT pg_get_viewdef('vsubdepartment'::regclass);
 179 SELECT pg_get_viewdef('vsubdepartment'::regclass, true);
 180
 181 -- corner case in which sub-WITH gets initialized first
 182 with recursive q as (
 183       select * from department
 184     union all
 185       (with x as (select * from q)
 186        select * from x)
 187     )
 188 select * from q limit 24;
 189
 190 with recursive q as (
 191       select * from department
 192     union all
 193       (with recursive x as (
 194            select * from department
 195          union all
 196            (select * from q union all select * from x)
 197         )
 198        select * from x)
 199     )
 200 select * from q limit 32;
 201
 202 -- recursive term has sub-UNION
 203 WITH RECURSIVE t(i,j) AS (
 204         VALUES (1,2)
 205         UNION ALL
 206         SELECT t2.i, t.j+1 FROM
 207                 (SELECT 2 AS i UNION ALL SELECT 3 AS i) AS t2
 208                 JOIN t ON (t2.i = t.i+1))
 209
 210         SELECT * FROM t;
 211
 212 --
 213 -- different tree example
 214 --
 215 CREATE TEMPORARY TABLE tree(
 216     id INTEGER PRIMARY KEY,
 217     parent_id INTEGER REFERENCES tree(id)
 218 );
 219
 220 INSERT INTO tree
 221 VALUES (1, NULL), (2, 1), (3,1), (4,2), (5,2), (6,2), (7,3), (8,3),
 222        (9,4), (10,4), (11,7), (12,7), (13,7), (14, 9), (15,11), (16,11);
 223
 224 --
 225 -- get all paths from "second level" nodes to leaf nodes
 226 --
 227 WITH RECURSIVE t(id, path) AS (
 228     VALUES(1,ARRAY[]::integer[])
 229 UNION ALL
 230     SELECT tree.id, t.path || tree.id
 231     FROM tree JOIN t ON (tree.parent_id = t.id)
 232 )
 233 SELECT t1.*, t2.* FROM t AS t1 JOIN t AS t2 ON
 234         (t1.path[1] = t2.path[1] AND
 235         array_upper(t1.path,1) = 1 AND
 236         array_upper(t2.path,1) > 1)
 237         ORDER BY t1.id, t2.id;
 238
 239 -- just count 'em
 240 WITH RECURSIVE t(id, path) AS (
 241     VALUES(1,ARRAY[]::integer[])
 242 UNION ALL
 243     SELECT tree.id, t.path || tree.id
 244     FROM tree JOIN t ON (tree.parent_id = t.id)
 245 )
 246 SELECT t1.id, count(t2.*) FROM t AS t1 JOIN t AS t2 ON
 247         (t1.path[1] = t2.path[1] AND
 248         array_upper(t1.path,1) = 1 AND
 249         array_upper(t2.path,1) > 1)
 250         GROUP BY t1.id
 251         ORDER BY t1.id;
 252
 253 -- this variant tickled a whole-row-variable bug in 8.4devel
 254 WITH RECURSIVE t(id, path) AS (
 255     VALUES(1,ARRAY[]::integer[])
 256 UNION ALL
 257     SELECT tree.id, t.path || tree.id
 258     FROM tree JOIN t ON (tree.parent_id = t.id)
 259 )
 260 SELECT t1.id, t2.path, t2 FROM t AS t1 JOIN t AS t2 ON
 261 (t1.id=t2.id);
 262
 263 --
 264 -- test cycle detection
 265 --
 266 create temp table graph( f int, t int, label text );
 267
 268 insert into graph values
 269         (1, 2, 'arc 1 -> 2'),
 270         (1, 3, 'arc 1 -> 3'),
 271         (2, 3, 'arc 2 -> 3'),
 272         (1, 4, 'arc 1 -> 4'),
 273         (4, 5, 'arc 4 -> 5'),
 274         (5, 1, 'arc 5 -> 1');
 275
 276 with recursive search_graph(f, t, label, path, cycle) as (
 277         select *, array[row(g.f, g.t)], false from graph g
 278         union all
 279         select g.*, path || row(g.f, g.t), row(g.f, g.t) = any(path)
 280         from graph g, search_graph sg
 281         where g.f = sg.t and not cycle
 282 )
 283 select * from search_graph;
 284
 285 -- ordering by the path column has same effect as SEARCH DEPTH FIRST
 286 with recursive search_graph(f, t, label, path, cycle) as (
 287         select *, array[row(g.f, g.t)], false from graph g
 288         union all
 289         select g.*, path || row(g.f, g.t), row(g.f, g.t) = any(path)
 290         from graph g, search_graph sg
 291         where g.f = sg.t and not cycle
 292 )
 293 select * from search_graph order by path;
 294
 295 --
 296 -- test multiple WITH queries
 297 --
 298 WITH RECURSIVE
 299   y (id) AS (VALUES (1)),
 300   x (id) AS (SELECT * FROM y UNION ALL SELECT id+1 FROM x WHERE id < 5)
 301 SELECT * FROM x;
 302
 303 -- forward reference OK
 304 WITH RECURSIVE
 305     x(id) AS (SELECT * FROM y UNION ALL SELECT id+1 FROM x WHERE id < 5),
 306     y(id) AS (values (1))
 307  SELECT * FROM x;
 308
 309 WITH RECURSIVE
 310    x(id) AS
 311      (VALUES (1) UNION ALL SELECT id+1 FROM x WHERE id < 5),
 312    y(id) AS
 313      (VALUES (1) UNION ALL SELECT id+1 FROM y WHERE id < 10)
 314  SELECT y.*, x.* FROM y LEFT JOIN x USING (id);
 315
 316 WITH RECURSIVE
 317    x(id) AS
 318      (VALUES (1) UNION ALL SELECT id+1 FROM x WHERE id < 5),
 319    y(id) AS
 320      (VALUES (1) UNION ALL SELECT id+1 FROM x WHERE id < 10)
 321  SELECT y.*, x.* FROM y LEFT JOIN x USING (id);
 322
 323 WITH RECURSIVE
 324    x(id) AS
 325      (SELECT 1 UNION ALL SELECT id+1 FROM x WHERE id < 3 ),
 326    y(id) AS
 327      (SELECT * FROM x UNION ALL SELECT * FROM x),
 328    z(id) AS
 329      (SELECT * FROM x UNION ALL SELECT id+1 FROM z WHERE id < 10)
 330  SELECT * FROM z;
 331
 332 WITH RECURSIVE
 333    x(id) AS
 334      (SELECT 1 UNION ALL SELECT id+1 FROM x WHERE id < 3 ),
 335    y(id) AS
 336      (SELECT * FROM x UNION ALL SELECT * FROM x),
 337    z(id) AS
 338      (SELECT * FROM y UNION ALL SELECT id+1 FROM z WHERE id < 10)
 339  SELECT * FROM z;
 340
 341 --
 342 -- Test WITH attached to a data-modifying statement
 343 --
 344
 345 CREATE TEMPORARY TABLE y (a INTEGER);
 346 INSERT INTO y SELECT generate_series(1, 10);
 347
 348 WITH t AS (
 349         SELECT a FROM y
 350 )
 351 INSERT INTO y
 352 SELECT a+20 FROM t RETURNING *;
 353
 354 SELECT * FROM y;
 355
 356 WITH t AS (
 357         SELECT a FROM y
 358 )
 359 UPDATE y SET a = y.a-10 FROM t WHERE y.a > 20 AND t.a = y.a RETURNING y.a;
 360
 361 SELECT * FROM y;
 362
 363 WITH RECURSIVE t(a) AS (
 364         SELECT 11
 365         UNION ALL
 366         SELECT a+1 FROM t WHERE a < 50
 367 )
 368 DELETE FROM y USING t WHERE t.a = y.a RETURNING y.a;
 369
 370 SELECT * FROM y;
 371
 372 DROP TABLE y;
 373
 374 --
 375 -- error cases
 376 --
 377
 378 -- INTERSECT
 379 WITH RECURSIVE x(n) AS (SELECT 1 INTERSECT SELECT n+1 FROM x)
 380         SELECT * FROM x;
 381
 382 WITH RECURSIVE x(n) AS (SELECT 1 INTERSECT ALL SELECT n+1 FROM x)
 383         SELECT * FROM x;
 384
 385 -- EXCEPT
 386 WITH RECURSIVE x(n) AS (SELECT 1 EXCEPT SELECT n+1 FROM x)
 387         SELECT * FROM x;
 388
 389 WITH RECURSIVE x(n) AS (SELECT 1 EXCEPT ALL SELECT n+1 FROM x)
 390         SELECT * FROM x;
 391
 392 -- no non-recursive term
 393 WITH RECURSIVE x(n) AS (SELECT n FROM x)
 394         SELECT * FROM x;
 395
 396 -- recursive term in the left hand side (strictly speaking, should allow this)
 397 WITH RECURSIVE x(n) AS (SELECT n FROM x UNION ALL SELECT 1)
 398         SELECT * FROM x;
 399
 400 CREATE TEMPORARY TABLE y (a INTEGER);
 401 INSERT INTO y SELECT generate_series(1, 10);
 402
 403 -- LEFT JOIN
 404
 405 WITH RECURSIVE x(n) AS (SELECT a FROM y WHERE a = 1
 406         UNION ALL
 407         SELECT x.n+1 FROM y LEFT JOIN x ON x.n = y.a WHERE n < 10)
 408 SELECT * FROM x;
 409
 410 -- RIGHT JOIN
 411 WITH RECURSIVE x(n) AS (SELECT a FROM y WHERE a = 1
 412         UNION ALL
 413         SELECT x.n+1 FROM x RIGHT JOIN y ON x.n = y.a WHERE n < 10)
 414 SELECT * FROM x;
 415
 416 -- FULL JOIN
 417 WITH RECURSIVE x(n) AS (SELECT a FROM y WHERE a = 1
 418         UNION ALL
 419         SELECT x.n+1 FROM x FULL JOIN y ON x.n = y.a WHERE n < 10)
 420 SELECT * FROM x;
 421
 422 -- subquery
 423 WITH RECURSIVE x(n) AS (SELECT 1 UNION ALL SELECT n+1 FROM x
 424                           WHERE n IN (SELECT * FROM x))
 425   SELECT * FROM x;
 426
 427 -- aggregate functions
 428 WITH RECURSIVE x(n) AS (SELECT 1 UNION ALL SELECT count(*) FROM x)
 429   SELECT * FROM x;
 430
 431 WITH RECURSIVE x(n) AS (SELECT 1 UNION ALL SELECT sum(n) FROM x)
 432   SELECT * FROM x;
 433
 434 -- ORDER BY
 435 WITH RECURSIVE x(n) AS (SELECT 1 UNION ALL SELECT n+1 FROM x ORDER BY 1)
 436   SELECT * FROM x;
 437
 438 -- LIMIT/OFFSET
 439 WITH RECURSIVE x(n) AS (SELECT 1 UNION ALL SELECT n+1 FROM x LIMIT 10 OFFSET 1)
 440   SELECT * FROM x;
 441
 442 -- FOR UPDATE
 443 WITH RECURSIVE x(n) AS (SELECT 1 UNION ALL SELECT n+1 FROM x FOR UPDATE)
 444   SELECT * FROM x;
 445
 446 -- target list has a recursive query name
 447 WITH RECURSIVE x(id) AS (values (1)
 448     UNION ALL
 449     SELECT (SELECT * FROM x) FROM x WHERE id < 5
 450 ) SELECT * FROM x;
 451
 452 -- mutual recursive query (not implemented)
 453 WITH RECURSIVE
 454   x (id) AS (SELECT 1 UNION ALL SELECT id+1 FROM y WHERE id < 5),
 455   y (id) AS (SELECT 1 UNION ALL SELECT id+1 FROM x WHERE id < 5)
 456 SELECT * FROM x;
 457
 458 -- non-linear recursion is not allowed
 459 WITH RECURSIVE foo(i) AS
 460     (values (1)
 461     UNION ALL
 462        (SELECT i+1 FROM foo WHERE i < 10
 463           UNION ALL
 464        SELECT i+1 FROM foo WHERE i < 5)
 465 ) SELECT * FROM foo;
 466
 467 WITH RECURSIVE foo(i) AS
 468     (values (1)
 469     UNION ALL
 470            SELECT * FROM
 471        (SELECT i+1 FROM foo WHERE i < 10
 472           UNION ALL
 473        SELECT i+1 FROM foo WHERE i < 5) AS t
 474 ) SELECT * FROM foo;
 475
 476 WITH RECURSIVE foo(i) AS
 477     (values (1)
 478     UNION ALL
 479        (SELECT i+1 FROM foo WHERE i < 10
 480           EXCEPT
 481        SELECT i+1 FROM foo WHERE i < 5)
 482 ) SELECT * FROM foo;
 483
 484 WITH RECURSIVE foo(i) AS
 485     (values (1)
 486     UNION ALL
 487        (SELECT i+1 FROM foo WHERE i < 10
 488           INTERSECT
 489        SELECT i+1 FROM foo WHERE i < 5)
 490 ) SELECT * FROM foo;
 491
 492 -- Wrong type induced from non-recursive term
 493 WITH RECURSIVE foo(i) AS
 494    (SELECT i FROM (VALUES(1),(2)) t(i)
 495    UNION ALL
 496    SELECT (i+1)::numeric(10,0) FROM foo WHERE i < 10)
 497 SELECT * FROM foo;
 498
 499 -- rejects different typmod, too (should we allow this?)
 500 WITH RECURSIVE foo(i) AS
 501    (SELECT i::numeric(3,0) FROM (VALUES(1),(2)) t(i)
 502    UNION ALL
 503    SELECT (i+1)::numeric(10,0) FROM foo WHERE i < 10)
 504 SELECT * FROM foo;
 505
 506 -- disallow OLD/NEW reference in CTE
 507 CREATE TEMPORARY TABLE x (n integer);
 508 CREATE RULE r2 AS ON UPDATE TO x DO INSTEAD
 509     WITH t AS (SELECT OLD.*) UPDATE y SET a = t.n FROM t;
 510
 511 --
 512 -- test for bug #4902
 513 --
 514 with cte(foo) as ( values(42) ) values((select foo from cte));
 515 with cte(foo) as ( select 42 ) select * from ((select foo from cte)) q;
 516
 517 -- test CTE referencing an outer-level variable (to see that changed-parameter
 518 -- signaling still works properly after fixing this bug)
 519 select ( with cte(foo) as ( values(f1) )
 520          select (select foo from cte) )
 521 from int4_tbl;
 522
 523 select ( with cte(foo) as ( values(f1) )
 524           values((select foo from cte)) )
 525 from int4_tbl;
 526
 527 --
 528 -- test for nested-recursive-WITH bug
 529 --
 530 WITH RECURSIVE t(j) AS (
 531     WITH RECURSIVE s(i) AS (
 532         VALUES (1)
 533         UNION ALL
 534         SELECT i+1 FROM s WHERE i < 10
 535     )
 536     SELECT i FROM s
 537     UNION ALL
 538     SELECT j+1 FROM t WHERE j < 10
 539 )
 540 SELECT * FROM t;
 541
 542 --
 543 -- test WITH attached to intermediate-level set operation
 544 --
 545
 546 WITH outermost(x) AS (
 547   SELECT 1
 548   UNION (WITH innermost as (SELECT 2)
 549          SELECT * FROM innermost
 550          UNION SELECT 3)
 551 )
 552 SELECT * FROM outermost;
 553
 554 WITH outermost(x) AS (
 555   SELECT 1
 556   UNION (WITH innermost as (SELECT 2)
 557          SELECT * FROM outermost  -- fail
 558          UNION SELECT * FROM innermost)
 559 )
 560 SELECT * FROM outermost;
 561
 562 WITH RECURSIVE outermost(x) AS (
 563   SELECT 1
 564   UNION (WITH innermost as (SELECT 2)
 565          SELECT * FROM outermost
 566          UNION SELECT * FROM innermost)
 567 )
 568 SELECT * FROM outermost;
 569
 570 WITH RECURSIVE outermost(x) AS (
 571   WITH innermost as (SELECT 2 FROM outermost) -- fail
 572     SELECT * FROM innermost
 573     UNION SELECT * from outermost
 574 )
 575 SELECT * FROM outermost;
 576
 577 --
 578 -- This test will fail with the old implementation of PARAM_EXEC parameter
 579 -- assignment, because the "q1" Var passed down to A's targetlist subselect
 580 -- looks exactly like the "A.id" Var passed down to C's subselect, causing
 581 -- the old code to give them the same runtime PARAM_EXEC slot.  But the
 582 -- lifespans of the two parameters overlap, thanks to B also reading A.
 583 --
 584
 585 with
 586 A as ( select q2 as id, (select q1) as x from int8_tbl ),
 587 B as ( select id, row_number() over (partition by id) as r from A ),
 588 C as ( select A.id, array(select B.id from B where B.id = A.id) from A )
 589 select * from C;
 590
 591 --
 592 -- Test CTEs read in non-initialization orders
 593 --
 594
 595 WITH RECURSIVE
 596   tab(id_key,link) AS (VALUES (1,17), (2,17), (3,17), (4,17), (6,17), (5,17)),
 597   iter (id_key, row_type, link) AS (
 598       SELECT 0, 'base', 17
 599     UNION ALL (
 600       WITH remaining(id_key, row_type, link, min) AS (
 601         SELECT tab.id_key, 'true'::text, iter.link, MIN(tab.id_key) OVER ()
 602         FROM tab INNER JOIN iter USING (link)
 603         WHERE tab.id_key > iter.id_key
 604       ),
 605       first_remaining AS (
 606         SELECT id_key, row_type, link
 607         FROM remaining
 608         WHERE id_key=min
 609       ),
 610       effect AS (
 611         SELECT tab.id_key, 'new'::text, tab.link
 612         FROM first_remaining e INNER JOIN tab ON e.id_key=tab.id_key
 613         WHERE e.row_type = 'false'
 614       )
 615       SELECT * FROM first_remaining
 616       UNION ALL SELECT * FROM effect
 617     )
 618   )
 619 SELECT * FROM iter;
 620
 621 WITH RECURSIVE
 622   tab(id_key,link) AS (VALUES (1,17), (2,17), (3,17), (4,17), (6,17), (5,17)),
 623   iter (id_key, row_type, link) AS (
 624       SELECT 0, 'base', 17
 625     UNION (
 626       WITH remaining(id_key, row_type, link, min) AS (
 627         SELECT tab.id_key, 'true'::text, iter.link, MIN(tab.id_key) OVER ()
 628         FROM tab INNER JOIN iter USING (link)
 629         WHERE tab.id_key > iter.id_key
 630       ),
 631       first_remaining AS (
 632         SELECT id_key, row_type, link
 633         FROM remaining
 634         WHERE id_key=min
 635       ),
 636       effect AS (
 637         SELECT tab.id_key, 'new'::text, tab.link
 638         FROM first_remaining e INNER JOIN tab ON e.id_key=tab.id_key
 639         WHERE e.row_type = 'false'
 640       )
 641       SELECT * FROM first_remaining
 642       UNION ALL SELECT * FROM effect
 643     )
 644   )
 645 SELECT * FROM iter;
 646
 647 --
 648 -- Data-modifying statements in WITH
 649 --
 650
 651 -- INSERT ... RETURNING
 652 WITH t AS (
 653     INSERT INTO y
 654     VALUES
 655         (11),
 656         (12),
 657         (13),
 658         (14),
 659         (15),
 660         (16),
 661         (17),
 662         (18),
 663         (19),
 664         (20)
 665     RETURNING *
 666 )
 667 SELECT * FROM t;
 668
 669 SELECT * FROM y;
 670
 671 -- UPDATE ... RETURNING
 672 WITH t AS (
 673     UPDATE y
 674     SET a=a+1
 675     RETURNING *
 676 )
 677 SELECT * FROM t;
 678
 679 SELECT * FROM y;
 680
 681 -- DELETE ... RETURNING
 682 WITH t AS (
 683     DELETE FROM y
 684     WHERE a <= 10
 685     RETURNING *
 686 )
 687 SELECT * FROM t;
 688
 689 SELECT * FROM y;
 690
 691 -- forward reference
 692 WITH RECURSIVE t AS (
 693         INSERT INTO y
 694                 SELECT a+5 FROM t2 WHERE a > 5
 695         RETURNING *
 696 ), t2 AS (
 697         UPDATE y SET a=a-11 RETURNING *
 698 )
 699 SELECT * FROM t
 700 UNION ALL
 701 SELECT * FROM t2;
 702
 703 SELECT * FROM y;
 704
 705 -- unconditional DO INSTEAD rule
 706 CREATE RULE y_rule AS ON DELETE TO y DO INSTEAD
 707   INSERT INTO y VALUES(42) RETURNING *;
 708
 709 WITH t AS (
 710         DELETE FROM y RETURNING *
 711 )
 712 SELECT * FROM t;
 713
 714 SELECT * FROM y;
 715
 716 DROP RULE y_rule ON y;
 717
 718 -- check merging of outer CTE with CTE in a rule action
 719 CREATE TEMP TABLE bug6051 AS
 720   select i from generate_series(1,3) as t(i);
 721
 722 SELECT * FROM bug6051;
 723
 724 WITH t1 AS ( DELETE FROM bug6051 RETURNING * )
 725 INSERT INTO bug6051 SELECT * FROM t1;
 726
 727 SELECT * FROM bug6051;
 728
 729 CREATE TEMP TABLE bug6051_2 (i int);
 730
 731 CREATE RULE bug6051_ins AS ON INSERT TO bug6051 DO INSTEAD
 732  INSERT INTO bug6051_2
 733  SELECT NEW.i;
 734
 735 WITH t1 AS ( DELETE FROM bug6051 RETURNING * )
 736 INSERT INTO bug6051 SELECT * FROM t1;
 737
 738 SELECT * FROM bug6051;
 739 SELECT * FROM bug6051_2;
 740
 741 -- a truly recursive CTE in the same list
 742 WITH RECURSIVE t(a) AS (
 743         SELECT 0
 744                 UNION ALL
 745         SELECT a+1 FROM t WHERE a+1 < 5
 746 ), t2 as (
 747         INSERT INTO y
 748                 SELECT * FROM t RETURNING *
 749 )
 750 SELECT * FROM t2 JOIN y USING (a) ORDER BY a;
 751
 752 SELECT * FROM y;
 753
 754 -- data-modifying WITH in a modifying statement
 755 WITH t AS (
 756     DELETE FROM y
 757     WHERE a <= 10
 758     RETURNING *
 759 )
 760 INSERT INTO y SELECT -a FROM t RETURNING *;
 761
 762 SELECT * FROM y;
 763
 764 -- check that WITH query is run to completion even if outer query isn't
 765 WITH t AS (
 766     UPDATE y SET a = a * 100 RETURNING *
 767 )
 768 SELECT * FROM t LIMIT 10;
 769
 770 SELECT * FROM y;
 771
 772 -- check that run to completion happens in proper ordering
 773
 774 TRUNCATE TABLE y;
 775 INSERT INTO y SELECT generate_series(1, 3);
 776 CREATE TEMPORARY TABLE yy (a INTEGER);
 777
 778 WITH RECURSIVE t1 AS (
 779   INSERT INTO y SELECT * FROM y RETURNING *
 780 ), t2 AS (
 781   INSERT INTO yy SELECT * FROM t1 RETURNING *
 782 )
 783 SELECT 1;
 784
 785 SELECT * FROM y;
 786 SELECT * FROM yy;
 787
 788 WITH RECURSIVE t1 AS (
 789   INSERT INTO yy SELECT * FROM t2 RETURNING *
 790 ), t2 AS (
 791   INSERT INTO y SELECT * FROM y RETURNING *
 792 )
 793 SELECT 1;
 794
 795 SELECT * FROM y;
 796 SELECT * FROM yy;
 797
 798 -- triggers
 799
 800 TRUNCATE TABLE y;
 801 INSERT INTO y SELECT generate_series(1, 10);
 802
 803 CREATE FUNCTION y_trigger() RETURNS trigger AS $$
 804 begin
 805   raise notice 'y_trigger: a = %', new.a;
 806   return new;
 807 end;
 808 $$ LANGUAGE plpgsql;
 809
 810 CREATE TRIGGER y_trig BEFORE INSERT ON y FOR EACH ROW
 811     EXECUTE PROCEDURE y_trigger();
 812
 813 WITH t AS (
 814     INSERT INTO y
 815     VALUES
 816         (21),
 817         (22),
 818         (23)
 819     RETURNING *
 820 )
 821 SELECT * FROM t;
 822
 823 SELECT * FROM y;
 824
 825 DROP TRIGGER y_trig ON y;
 826
 827 CREATE TRIGGER y_trig AFTER INSERT ON y FOR EACH ROW
 828     EXECUTE PROCEDURE y_trigger();
 829
 830 WITH t AS (
 831     INSERT INTO y
 832     VALUES
 833         (31),
 834         (32),
 835         (33)
 836     RETURNING *
 837 )
 838 SELECT * FROM t LIMIT 1;
 839
 840 SELECT * FROM y;
 841
 842 DROP TRIGGER y_trig ON y;
 843
 844 CREATE OR REPLACE FUNCTION y_trigger() RETURNS trigger AS $$
 845 begin
 846   raise notice 'y_trigger';
 847   return null;
 848 end;
 849 $$ LANGUAGE plpgsql;
 850
 851 CREATE TRIGGER y_trig AFTER INSERT ON y FOR EACH STATEMENT
 852     EXECUTE PROCEDURE y_trigger();
 853
 854 WITH t AS (
 855     INSERT INTO y
 856     VALUES
 857         (41),
 858         (42),
 859         (43)
 860     RETURNING *
 861 )
 862 SELECT * FROM t;
 863
 864 SELECT * FROM y;
 865
 866 DROP TRIGGER y_trig ON y;
 867 DROP FUNCTION y_trigger();
 868
 869 -- WITH attached to inherited UPDATE or DELETE
 870
 871 CREATE TEMP TABLE parent ( id int, val text );
 872 CREATE TEMP TABLE child1 ( ) INHERITS ( parent );
 873 CREATE TEMP TABLE child2 ( ) INHERITS ( parent );
 874
 875 INSERT INTO parent VALUES ( 1, 'p1' );
 876 INSERT INTO child1 VALUES ( 11, 'c11' ),( 12, 'c12' );
 877 INSERT INTO child2 VALUES ( 23, 'c21' ),( 24, 'c22' );
 878
 879 WITH rcte AS ( SELECT sum(id) AS totalid FROM parent )
 880 UPDATE parent SET id = id + totalid FROM rcte;
 881
 882 SELECT * FROM parent;
 883
 884 WITH wcte AS ( INSERT INTO child1 VALUES ( 42, 'new' ) RETURNING id AS newid )
 885 UPDATE parent SET id = id + newid FROM wcte;
 886
 887 SELECT * FROM parent;
 888
 889 WITH rcte AS ( SELECT max(id) AS maxid FROM parent )
 890 DELETE FROM parent USING rcte WHERE id = maxid;
 891
 892 SELECT * FROM parent;
 893
 894 WITH wcte AS ( INSERT INTO child2 VALUES ( 42, 'new2' ) RETURNING id AS newid )
 895 DELETE FROM parent USING wcte WHERE id = newid;
 896
 897 SELECT * FROM parent;
 898
 899 -- check EXPLAIN VERBOSE for a wCTE with RETURNING
 900
 901 EXPLAIN (VERBOSE, COSTS OFF)
 902 WITH wcte AS ( INSERT INTO int8_tbl VALUES ( 42, 47 ) RETURNING q2 )
 903 DELETE FROM a USING wcte WHERE aa = q2;
 904
 905 -- error cases
 906
 907 -- data-modifying WITH tries to use its own output
 908 WITH RECURSIVE t AS (
 909         INSERT INTO y
 910                 SELECT * FROM t
 911 )
 912 VALUES(FALSE);
 913
 914 -- no RETURNING in a referenced data-modifying WITH
 915 WITH t AS (
 916         INSERT INTO y VALUES(0)
 917 )
 918 SELECT * FROM t;
 919
 920 -- data-modifying WITH allowed only at the top level
 921 SELECT * FROM (
 922         WITH t AS (UPDATE y SET a=a+1 RETURNING *)
 923         SELECT * FROM t
 924 ) ss;
 925
 926 -- most variants of rules aren't allowed
 927 CREATE RULE y_rule AS ON INSERT TO y WHERE a=0 DO INSTEAD DELETE FROM y;
 928 WITH t AS (
 929         INSERT INTO y VALUES(0)
 930 )
 931 VALUES(FALSE);
 932 DROP RULE y_rule ON y;