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