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