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