1-- 2-- Tests for common table expressions (WITH query, ... SELECT ...) 3-- 4-- Basic WITH 5WITH q1(x,y) AS (SELECT 1,2) 6SELECT * FROM q1, q1 AS q2; 7 x | y | x | y 8---+---+---+--- 9 1 | 2 | 1 | 2 10(1 row) 11 12-- Multiple uses are evaluated only once 13SELECT count(*) FROM ( 14 WITH q1(x) AS (SELECT random() FROM generate_series(1, 5)) 15 SELECT * FROM q1 16 UNION 17 SELECT * FROM q1 18) ss; 19 count 20------- 21 5 22(1 row) 23 24-- WITH RECURSIVE 25-- sum of 1..100 26WITH RECURSIVE t(n) AS ( 27 VALUES (1) 28UNION ALL 29 SELECT n+1 FROM t WHERE n < 100 30) 31SELECT sum(n) FROM t; 32 sum 33------ 34 5050 35(1 row) 36 37WITH RECURSIVE t(n) AS ( 38 SELECT (VALUES(1)) 39UNION ALL 40 SELECT n+1 FROM t WHERE n < 5 41) 42SELECT * FROM t; 43 n 44--- 45 1 46 2 47 3 48 4 49 5 50(5 rows) 51 52-- recursive view 53CREATE RECURSIVE VIEW nums (n) AS 54 VALUES (1) 55UNION ALL 56 SELECT n+1 FROM nums WHERE n < 5; 57SELECT * FROM nums; 58 n 59--- 60 1 61 2 62 3 63 4 64 5 65(5 rows) 66 67CREATE OR REPLACE RECURSIVE VIEW nums (n) AS 68 VALUES (1) 69UNION ALL 70 SELECT n+1 FROM nums WHERE n < 6; 71SELECT * FROM nums; 72 n 73--- 74 1 75 2 76 3 77 4 78 5 79 6 80(6 rows) 81 82-- This is an infinite loop with UNION ALL, but not with UNION 83WITH RECURSIVE t(n) AS ( 84 SELECT 1 85UNION 86 SELECT 10-n FROM t) 87SELECT * FROM t; 88 n 89--- 90 1 91 9 92(2 rows) 93 94-- This'd be an infinite loop, but outside query reads only as much as needed 95WITH RECURSIVE t(n) AS ( 96 VALUES (1) 97UNION ALL 98 SELECT n+1 FROM t) 99SELECT * FROM t LIMIT 10; 100 n 101---- 102 1 103 2 104 3 105 4 106 5 107 6 108 7 109 8 110 9 111 10 112(10 rows) 113 114-- UNION case should have same property 115WITH RECURSIVE t(n) AS ( 116 SELECT 1 117UNION 118 SELECT n+1 FROM t) 119SELECT * FROM t LIMIT 10; 120 n 121---- 122 1 123 2 124 3 125 4 126 5 127 6 128 7 129 8 130 9 131 10 132(10 rows) 133 134-- Test behavior with an unknown-type literal in the WITH 135WITH q AS (SELECT 'foo' AS x) 136SELECT x, x IS OF (text) AS is_text FROM q; 137 x | is_text 138-----+--------- 139 foo | t 140(1 row) 141 142WITH RECURSIVE t(n) AS ( 143 SELECT 'foo' 144UNION ALL 145 SELECT n || ' bar' FROM t WHERE length(n) < 20 146) 147SELECT n, n IS OF (text) AS is_text FROM t; 148 n | is_text 149-------------------------+--------- 150 foo | t 151 foo bar | t 152 foo bar bar | t 153 foo bar bar bar | t 154 foo bar bar bar bar | t 155 foo bar bar bar bar bar | t 156(6 rows) 157 158-- In a perfect world, this would work and resolve the literal as int ... 159-- but for now, we have to be content with resolving to text too soon. 160WITH RECURSIVE t(n) AS ( 161 SELECT '7' 162UNION ALL 163 SELECT n+1 FROM t WHERE n < 10 164) 165SELECT n, n IS OF (int) AS is_int FROM t; 166ERROR: operator does not exist: text + integer 167LINE 4: SELECT n+1 FROM t WHERE n < 10 168 ^ 169HINT: No operator matches the given name and argument types. You might need to add explicit type casts. 170-- 171-- Some examples with a tree 172-- 173-- department structure represented here is as follows: 174-- 175-- ROOT-+->A-+->B-+->C 176-- | | 177-- | +->D-+->F 178-- +->E-+->G 179CREATE TEMP TABLE department ( 180 id INTEGER PRIMARY KEY, -- department ID 181 parent_department INTEGER REFERENCES department, -- upper department ID 182 name TEXT -- department name 183); 184INSERT INTO department VALUES (0, NULL, 'ROOT'); 185INSERT INTO department VALUES (1, 0, 'A'); 186INSERT INTO department VALUES (2, 1, 'B'); 187INSERT INTO department VALUES (3, 2, 'C'); 188INSERT INTO department VALUES (4, 2, 'D'); 189INSERT INTO department VALUES (5, 0, 'E'); 190INSERT INTO department VALUES (6, 4, 'F'); 191INSERT INTO department VALUES (7, 5, 'G'); 192-- extract all departments under 'A'. Result should be A, B, C, D and F 193WITH RECURSIVE subdepartment AS 194( 195 -- non recursive term 196 SELECT name as root_name, * FROM department WHERE name = 'A' 197 UNION ALL 198 -- recursive term 199 SELECT sd.root_name, d.* FROM department AS d, subdepartment AS sd 200 WHERE d.parent_department = sd.id 201) 202SELECT * FROM subdepartment ORDER BY name; 203 root_name | id | parent_department | name 204-----------+----+-------------------+------ 205 A | 1 | 0 | A 206 A | 2 | 1 | B 207 A | 3 | 2 | C 208 A | 4 | 2 | D 209 A | 6 | 4 | F 210(5 rows) 211 212-- extract all departments under 'A' with "level" number 213WITH RECURSIVE subdepartment(level, id, parent_department, name) AS 214( 215 -- non recursive term 216 SELECT 1, * FROM department WHERE name = 'A' 217 UNION ALL 218 -- recursive term 219 SELECT sd.level + 1, d.* FROM department AS d, subdepartment AS sd 220 WHERE d.parent_department = sd.id 221) 222SELECT * FROM subdepartment ORDER BY name; 223 level | id | parent_department | name 224-------+----+-------------------+------ 225 1 | 1 | 0 | A 226 2 | 2 | 1 | B 227 3 | 3 | 2 | C 228 3 | 4 | 2 | D 229 4 | 6 | 4 | F 230(5 rows) 231 232-- extract all departments under 'A' with "level" number. 233-- Only shows level 2 or more 234WITH RECURSIVE subdepartment(level, id, parent_department, name) AS 235( 236 -- non recursive term 237 SELECT 1, * FROM department WHERE name = 'A' 238 UNION ALL 239 -- recursive term 240 SELECT sd.level + 1, d.* FROM department AS d, subdepartment AS sd 241 WHERE d.parent_department = sd.id 242) 243SELECT * FROM subdepartment WHERE level >= 2 ORDER BY name; 244 level | id | parent_department | name 245-------+----+-------------------+------ 246 2 | 2 | 1 | B 247 3 | 3 | 2 | C 248 3 | 4 | 2 | D 249 4 | 6 | 4 | F 250(4 rows) 251 252-- "RECURSIVE" is ignored if the query has no self-reference 253WITH RECURSIVE subdepartment AS 254( 255 -- note lack of recursive UNION structure 256 SELECT * FROM department WHERE name = 'A' 257) 258SELECT * FROM subdepartment ORDER BY name; 259 id | parent_department | name 260----+-------------------+------ 261 1 | 0 | A 262(1 row) 263 264-- inside subqueries 265SELECT count(*) FROM ( 266 WITH RECURSIVE t(n) AS ( 267 SELECT 1 UNION ALL SELECT n + 1 FROM t WHERE n < 500 268 ) 269 SELECT * FROM t) AS t WHERE n < ( 270 SELECT count(*) FROM ( 271 WITH RECURSIVE t(n) AS ( 272 SELECT 1 UNION ALL SELECT n + 1 FROM t WHERE n < 100 273 ) 274 SELECT * FROM t WHERE n < 50000 275 ) AS t WHERE n < 100); 276 count 277------- 278 98 279(1 row) 280 281-- use same CTE twice at different subquery levels 282WITH q1(x,y) AS ( 283 SELECT hundred, sum(ten) FROM tenk1 GROUP BY hundred 284 ) 285SELECT count(*) FROM q1 WHERE y > (SELECT sum(y)/100 FROM q1 qsub); 286 count 287------- 288 50 289(1 row) 290 291-- via a VIEW 292CREATE TEMPORARY VIEW vsubdepartment AS 293 WITH RECURSIVE subdepartment AS 294 ( 295 -- non recursive term 296 SELECT * FROM department WHERE name = 'A' 297 UNION ALL 298 -- recursive term 299 SELECT d.* FROM department AS d, subdepartment AS sd 300 WHERE d.parent_department = sd.id 301 ) 302 SELECT * FROM subdepartment; 303SELECT * FROM vsubdepartment ORDER BY name; 304 id | parent_department | name 305----+-------------------+------ 306 1 | 0 | A 307 2 | 1 | B 308 3 | 2 | C 309 4 | 2 | D 310 6 | 4 | F 311(5 rows) 312 313-- Check reverse listing 314SELECT pg_get_viewdef('vsubdepartment'::regclass); 315 pg_get_viewdef 316----------------------------------------------- 317 WITH RECURSIVE subdepartment AS ( + 318 SELECT department.id, + 319 department.parent_department, + 320 department.name + 321 FROM department + 322 WHERE (department.name = 'A'::text)+ 323 UNION ALL + 324 SELECT d.id, + 325 d.parent_department, + 326 d.name + 327 FROM department d, + 328 subdepartment sd + 329 WHERE (d.parent_department = sd.id)+ 330 ) + 331 SELECT subdepartment.id, + 332 subdepartment.parent_department, + 333 subdepartment.name + 334 FROM subdepartment; 335(1 row) 336 337SELECT pg_get_viewdef('vsubdepartment'::regclass, true); 338 pg_get_viewdef 339--------------------------------------------- 340 WITH RECURSIVE subdepartment AS ( + 341 SELECT department.id, + 342 department.parent_department, + 343 department.name + 344 FROM department + 345 WHERE department.name = 'A'::text+ 346 UNION ALL + 347 SELECT d.id, + 348 d.parent_department, + 349 d.name + 350 FROM department d, + 351 subdepartment sd + 352 WHERE d.parent_department = sd.id+ 353 ) + 354 SELECT subdepartment.id, + 355 subdepartment.parent_department, + 356 subdepartment.name + 357 FROM subdepartment; 358(1 row) 359 360-- Another reverse-listing example 361CREATE VIEW sums_1_100 AS 362WITH RECURSIVE t(n) AS ( 363 VALUES (1) 364UNION ALL 365 SELECT n+1 FROM t WHERE n < 100 366) 367SELECT sum(n) FROM t; 368\d+ sums_1_100 369 View "public.sums_1_100" 370 Column | Type | Collation | Nullable | Default | Storage | Description 371--------+--------+-----------+----------+---------+---------+------------- 372 sum | bigint | | | | plain | 373View definition: 374 WITH RECURSIVE t(n) AS ( 375 VALUES (1) 376 UNION ALL 377 SELECT t_1.n + 1 378 FROM t t_1 379 WHERE t_1.n < 100 380 ) 381 SELECT sum(t.n) AS sum 382 FROM t; 383 384-- corner case in which sub-WITH gets initialized first 385with recursive q as ( 386 select * from department 387 union all 388 (with x as (select * from q) 389 select * from x) 390 ) 391select * from q limit 24; 392 id | parent_department | name 393----+-------------------+------ 394 0 | | ROOT 395 1 | 0 | A 396 2 | 1 | B 397 3 | 2 | C 398 4 | 2 | D 399 5 | 0 | E 400 6 | 4 | F 401 7 | 5 | G 402 0 | | ROOT 403 1 | 0 | A 404 2 | 1 | B 405 3 | 2 | C 406 4 | 2 | D 407 5 | 0 | E 408 6 | 4 | F 409 7 | 5 | G 410 0 | | ROOT 411 1 | 0 | A 412 2 | 1 | B 413 3 | 2 | C 414 4 | 2 | D 415 5 | 0 | E 416 6 | 4 | F 417 7 | 5 | G 418(24 rows) 419 420with recursive q as ( 421 select * from department 422 union all 423 (with recursive x as ( 424 select * from department 425 union all 426 (select * from q union all select * from x) 427 ) 428 select * from x) 429 ) 430select * from q limit 32; 431 id | parent_department | name 432----+-------------------+------ 433 0 | | ROOT 434 1 | 0 | A 435 2 | 1 | B 436 3 | 2 | C 437 4 | 2 | D 438 5 | 0 | E 439 6 | 4 | F 440 7 | 5 | G 441 0 | | ROOT 442 1 | 0 | A 443 2 | 1 | B 444 3 | 2 | C 445 4 | 2 | D 446 5 | 0 | E 447 6 | 4 | F 448 7 | 5 | G 449 0 | | ROOT 450 1 | 0 | A 451 2 | 1 | B 452 3 | 2 | C 453 4 | 2 | D 454 5 | 0 | E 455 6 | 4 | F 456 7 | 5 | G 457 0 | | ROOT 458 1 | 0 | A 459 2 | 1 | B 460 3 | 2 | C 461 4 | 2 | D 462 5 | 0 | E 463 6 | 4 | F 464 7 | 5 | G 465(32 rows) 466 467-- recursive term has sub-UNION 468WITH RECURSIVE t(i,j) AS ( 469 VALUES (1,2) 470 UNION ALL 471 SELECT t2.i, t.j+1 FROM 472 (SELECT 2 AS i UNION ALL SELECT 3 AS i) AS t2 473 JOIN t ON (t2.i = t.i+1)) 474 SELECT * FROM t; 475 i | j 476---+--- 477 1 | 2 478 2 | 3 479 3 | 4 480(3 rows) 481 482-- 483-- different tree example 484-- 485CREATE TEMPORARY TABLE tree( 486 id INTEGER PRIMARY KEY, 487 parent_id INTEGER REFERENCES tree(id) 488); 489INSERT INTO tree 490VALUES (1, NULL), (2, 1), (3,1), (4,2), (5,2), (6,2), (7,3), (8,3), 491 (9,4), (10,4), (11,7), (12,7), (13,7), (14, 9), (15,11), (16,11); 492-- 493-- get all paths from "second level" nodes to leaf nodes 494-- 495WITH RECURSIVE t(id, path) AS ( 496 VALUES(1,ARRAY[]::integer[]) 497UNION ALL 498 SELECT tree.id, t.path || tree.id 499 FROM tree JOIN t ON (tree.parent_id = t.id) 500) 501SELECT t1.*, t2.* FROM t AS t1 JOIN t AS t2 ON 502 (t1.path[1] = t2.path[1] AND 503 array_upper(t1.path,1) = 1 AND 504 array_upper(t2.path,1) > 1) 505 ORDER BY t1.id, t2.id; 506 id | path | id | path 507----+------+----+------------- 508 2 | {2} | 4 | {2,4} 509 2 | {2} | 5 | {2,5} 510 2 | {2} | 6 | {2,6} 511 2 | {2} | 9 | {2,4,9} 512 2 | {2} | 10 | {2,4,10} 513 2 | {2} | 14 | {2,4,9,14} 514 3 | {3} | 7 | {3,7} 515 3 | {3} | 8 | {3,8} 516 3 | {3} | 11 | {3,7,11} 517 3 | {3} | 12 | {3,7,12} 518 3 | {3} | 13 | {3,7,13} 519 3 | {3} | 15 | {3,7,11,15} 520 3 | {3} | 16 | {3,7,11,16} 521(13 rows) 522 523-- just count 'em 524WITH RECURSIVE t(id, path) AS ( 525 VALUES(1,ARRAY[]::integer[]) 526UNION ALL 527 SELECT tree.id, t.path || tree.id 528 FROM tree JOIN t ON (tree.parent_id = t.id) 529) 530SELECT t1.id, count(t2.*) FROM t AS t1 JOIN t AS t2 ON 531 (t1.path[1] = t2.path[1] AND 532 array_upper(t1.path,1) = 1 AND 533 array_upper(t2.path,1) > 1) 534 GROUP BY t1.id 535 ORDER BY t1.id; 536 id | count 537----+------- 538 2 | 6 539 3 | 7 540(2 rows) 541 542-- this variant tickled a whole-row-variable bug in 8.4devel 543WITH RECURSIVE t(id, path) AS ( 544 VALUES(1,ARRAY[]::integer[]) 545UNION ALL 546 SELECT tree.id, t.path || tree.id 547 FROM tree JOIN t ON (tree.parent_id = t.id) 548) 549SELECT t1.id, t2.path, t2 FROM t AS t1 JOIN t AS t2 ON 550(t1.id=t2.id); 551 id | path | t2 552----+-------------+-------------------- 553 1 | {} | (1,{}) 554 2 | {2} | (2,{2}) 555 3 | {3} | (3,{3}) 556 4 | {2,4} | (4,"{2,4}") 557 5 | {2,5} | (5,"{2,5}") 558 6 | {2,6} | (6,"{2,6}") 559 7 | {3,7} | (7,"{3,7}") 560 8 | {3,8} | (8,"{3,8}") 561 9 | {2,4,9} | (9,"{2,4,9}") 562 10 | {2,4,10} | (10,"{2,4,10}") 563 11 | {3,7,11} | (11,"{3,7,11}") 564 12 | {3,7,12} | (12,"{3,7,12}") 565 13 | {3,7,13} | (13,"{3,7,13}") 566 14 | {2,4,9,14} | (14,"{2,4,9,14}") 567 15 | {3,7,11,15} | (15,"{3,7,11,15}") 568 16 | {3,7,11,16} | (16,"{3,7,11,16}") 569(16 rows) 570 571-- 572-- test cycle detection 573-- 574create temp table graph( f int, t int, label text ); 575insert into graph values 576 (1, 2, 'arc 1 -> 2'), 577 (1, 3, 'arc 1 -> 3'), 578 (2, 3, 'arc 2 -> 3'), 579 (1, 4, 'arc 1 -> 4'), 580 (4, 5, 'arc 4 -> 5'), 581 (5, 1, 'arc 5 -> 1'); 582with recursive search_graph(f, t, label, path, cycle) as ( 583 select *, array[row(g.f, g.t)], false from graph g 584 union all 585 select g.*, path || row(g.f, g.t), row(g.f, g.t) = any(path) 586 from graph g, search_graph sg 587 where g.f = sg.t and not cycle 588) 589select * from search_graph; 590 f | t | label | path | cycle 591---+---+------------+-------------------------------------------+------- 592 1 | 2 | arc 1 -> 2 | {"(1,2)"} | f 593 1 | 3 | arc 1 -> 3 | {"(1,3)"} | f 594 2 | 3 | arc 2 -> 3 | {"(2,3)"} | f 595 1 | 4 | arc 1 -> 4 | {"(1,4)"} | f 596 4 | 5 | arc 4 -> 5 | {"(4,5)"} | f 597 5 | 1 | arc 5 -> 1 | {"(5,1)"} | f 598 1 | 2 | arc 1 -> 2 | {"(5,1)","(1,2)"} | f 599 1 | 3 | arc 1 -> 3 | {"(5,1)","(1,3)"} | f 600 1 | 4 | arc 1 -> 4 | {"(5,1)","(1,4)"} | f 601 2 | 3 | arc 2 -> 3 | {"(1,2)","(2,3)"} | f 602 4 | 5 | arc 4 -> 5 | {"(1,4)","(4,5)"} | f 603 5 | 1 | arc 5 -> 1 | {"(4,5)","(5,1)"} | f 604 1 | 2 | arc 1 -> 2 | {"(4,5)","(5,1)","(1,2)"} | f 605 1 | 3 | arc 1 -> 3 | {"(4,5)","(5,1)","(1,3)"} | f 606 1 | 4 | arc 1 -> 4 | {"(4,5)","(5,1)","(1,4)"} | f 607 2 | 3 | arc 2 -> 3 | {"(5,1)","(1,2)","(2,3)"} | f 608 4 | 5 | arc 4 -> 5 | {"(5,1)","(1,4)","(4,5)"} | f 609 5 | 1 | arc 5 -> 1 | {"(1,4)","(4,5)","(5,1)"} | f 610 1 | 2 | arc 1 -> 2 | {"(1,4)","(4,5)","(5,1)","(1,2)"} | f 611 1 | 3 | arc 1 -> 3 | {"(1,4)","(4,5)","(5,1)","(1,3)"} | f 612 1 | 4 | arc 1 -> 4 | {"(1,4)","(4,5)","(5,1)","(1,4)"} | t 613 2 | 3 | arc 2 -> 3 | {"(4,5)","(5,1)","(1,2)","(2,3)"} | f 614 4 | 5 | arc 4 -> 5 | {"(4,5)","(5,1)","(1,4)","(4,5)"} | t 615 5 | 1 | arc 5 -> 1 | {"(5,1)","(1,4)","(4,5)","(5,1)"} | t 616 2 | 3 | arc 2 -> 3 | {"(1,4)","(4,5)","(5,1)","(1,2)","(2,3)"} | f 617(25 rows) 618 619-- ordering by the path column has same effect as SEARCH DEPTH FIRST 620with recursive search_graph(f, t, label, path, cycle) as ( 621 select *, array[row(g.f, g.t)], false from graph g 622 union all 623 select g.*, path || row(g.f, g.t), row(g.f, g.t) = any(path) 624 from graph g, search_graph sg 625 where g.f = sg.t and not cycle 626) 627select * from search_graph order by path; 628 f | t | label | path | cycle 629---+---+------------+-------------------------------------------+------- 630 1 | 2 | arc 1 -> 2 | {"(1,2)"} | f 631 2 | 3 | arc 2 -> 3 | {"(1,2)","(2,3)"} | f 632 1 | 3 | arc 1 -> 3 | {"(1,3)"} | f 633 1 | 4 | arc 1 -> 4 | {"(1,4)"} | f 634 4 | 5 | arc 4 -> 5 | {"(1,4)","(4,5)"} | f 635 5 | 1 | arc 5 -> 1 | {"(1,4)","(4,5)","(5,1)"} | f 636 1 | 2 | arc 1 -> 2 | {"(1,4)","(4,5)","(5,1)","(1,2)"} | f 637 2 | 3 | arc 2 -> 3 | {"(1,4)","(4,5)","(5,1)","(1,2)","(2,3)"} | f 638 1 | 3 | arc 1 -> 3 | {"(1,4)","(4,5)","(5,1)","(1,3)"} | f 639 1 | 4 | arc 1 -> 4 | {"(1,4)","(4,5)","(5,1)","(1,4)"} | t 640 2 | 3 | arc 2 -> 3 | {"(2,3)"} | f 641 4 | 5 | arc 4 -> 5 | {"(4,5)"} | f 642 5 | 1 | arc 5 -> 1 | {"(4,5)","(5,1)"} | f 643 1 | 2 | arc 1 -> 2 | {"(4,5)","(5,1)","(1,2)"} | f 644 2 | 3 | arc 2 -> 3 | {"(4,5)","(5,1)","(1,2)","(2,3)"} | f 645 1 | 3 | arc 1 -> 3 | {"(4,5)","(5,1)","(1,3)"} | f 646 1 | 4 | arc 1 -> 4 | {"(4,5)","(5,1)","(1,4)"} | f 647 4 | 5 | arc 4 -> 5 | {"(4,5)","(5,1)","(1,4)","(4,5)"} | t 648 5 | 1 | arc 5 -> 1 | {"(5,1)"} | f 649 1 | 2 | arc 1 -> 2 | {"(5,1)","(1,2)"} | f 650 2 | 3 | arc 2 -> 3 | {"(5,1)","(1,2)","(2,3)"} | f 651 1 | 3 | arc 1 -> 3 | {"(5,1)","(1,3)"} | f 652 1 | 4 | arc 1 -> 4 | {"(5,1)","(1,4)"} | f 653 4 | 5 | arc 4 -> 5 | {"(5,1)","(1,4)","(4,5)"} | f 654 5 | 1 | arc 5 -> 1 | {"(5,1)","(1,4)","(4,5)","(5,1)"} | t 655(25 rows) 656 657-- 658-- test multiple WITH queries 659-- 660WITH RECURSIVE 661 y (id) AS (VALUES (1)), 662 x (id) AS (SELECT * FROM y UNION ALL SELECT id+1 FROM x WHERE id < 5) 663SELECT * FROM x; 664 id 665---- 666 1 667 2 668 3 669 4 670 5 671(5 rows) 672 673-- forward reference OK 674WITH RECURSIVE 675 x(id) AS (SELECT * FROM y UNION ALL SELECT id+1 FROM x WHERE id < 5), 676 y(id) AS (values (1)) 677 SELECT * FROM x; 678 id 679---- 680 1 681 2 682 3 683 4 684 5 685(5 rows) 686 687WITH RECURSIVE 688 x(id) AS 689 (VALUES (1) UNION ALL SELECT id+1 FROM x WHERE id < 5), 690 y(id) AS 691 (VALUES (1) UNION ALL SELECT id+1 FROM y WHERE id < 10) 692 SELECT y.*, x.* FROM y LEFT JOIN x USING (id); 693 id | id 694----+---- 695 1 | 1 696 2 | 2 697 3 | 3 698 4 | 4 699 5 | 5 700 6 | 701 7 | 702 8 | 703 9 | 704 10 | 705(10 rows) 706 707WITH RECURSIVE 708 x(id) AS 709 (VALUES (1) UNION ALL SELECT id+1 FROM x WHERE id < 5), 710 y(id) AS 711 (VALUES (1) UNION ALL SELECT id+1 FROM x WHERE id < 10) 712 SELECT y.*, x.* FROM y LEFT JOIN x USING (id); 713 id | id 714----+---- 715 1 | 1 716 2 | 2 717 3 | 3 718 4 | 4 719 5 | 5 720 6 | 721(6 rows) 722 723WITH RECURSIVE 724 x(id) AS 725 (SELECT 1 UNION ALL SELECT id+1 FROM x WHERE id < 3 ), 726 y(id) AS 727 (SELECT * FROM x UNION ALL SELECT * FROM x), 728 z(id) AS 729 (SELECT * FROM x UNION ALL SELECT id+1 FROM z WHERE id < 10) 730 SELECT * FROM z; 731 id 732---- 733 1 734 2 735 3 736 2 737 3 738 4 739 3 740 4 741 5 742 4 743 5 744 6 745 5 746 6 747 7 748 6 749 7 750 8 751 7 752 8 753 9 754 8 755 9 756 10 757 9 758 10 759 10 760(27 rows) 761 762WITH RECURSIVE 763 x(id) AS 764 (SELECT 1 UNION ALL SELECT id+1 FROM x WHERE id < 3 ), 765 y(id) AS 766 (SELECT * FROM x UNION ALL SELECT * FROM x), 767 z(id) AS 768 (SELECT * FROM y UNION ALL SELECT id+1 FROM z WHERE id < 10) 769 SELECT * FROM z; 770 id 771---- 772 1 773 2 774 3 775 1 776 2 777 3 778 2 779 3 780 4 781 2 782 3 783 4 784 3 785 4 786 5 787 3 788 4 789 5 790 4 791 5 792 6 793 4 794 5 795 6 796 5 797 6 798 7 799 5 800 6 801 7 802 6 803 7 804 8 805 6 806 7 807 8 808 7 809 8 810 9 811 7 812 8 813 9 814 8 815 9 816 10 817 8 818 9 819 10 820 9 821 10 822 9 823 10 824 10 825 10 826(54 rows) 827 828-- 829-- Test WITH attached to a data-modifying statement 830-- 831CREATE TEMPORARY TABLE y (a INTEGER); 832INSERT INTO y SELECT generate_series(1, 10); 833WITH t AS ( 834 SELECT a FROM y 835) 836INSERT INTO y 837SELECT a+20 FROM t RETURNING *; 838 a 839---- 840 21 841 22 842 23 843 24 844 25 845 26 846 27 847 28 848 29 849 30 850(10 rows) 851 852SELECT * FROM y; 853 a 854---- 855 1 856 2 857 3 858 4 859 5 860 6 861 7 862 8 863 9 864 10 865 21 866 22 867 23 868 24 869 25 870 26 871 27 872 28 873 29 874 30 875(20 rows) 876 877WITH t AS ( 878 SELECT a FROM y 879) 880UPDATE y SET a = y.a-10 FROM t WHERE y.a > 20 AND t.a = y.a RETURNING y.a; 881 a 882---- 883 11 884 12 885 13 886 14 887 15 888 16 889 17 890 18 891 19 892 20 893(10 rows) 894 895SELECT * FROM y; 896 a 897---- 898 1 899 2 900 3 901 4 902 5 903 6 904 7 905 8 906 9 907 10 908 11 909 12 910 13 911 14 912 15 913 16 914 17 915 18 916 19 917 20 918(20 rows) 919 920WITH RECURSIVE t(a) AS ( 921 SELECT 11 922 UNION ALL 923 SELECT a+1 FROM t WHERE a < 50 924) 925DELETE FROM y USING t WHERE t.a = y.a RETURNING y.a; 926 a 927---- 928 11 929 12 930 13 931 14 932 15 933 16 934 17 935 18 936 19 937 20 938(10 rows) 939 940SELECT * FROM y; 941 a 942---- 943 1 944 2 945 3 946 4 947 5 948 6 949 7 950 8 951 9 952 10 953(10 rows) 954 955DROP TABLE y; 956-- 957-- error cases 958-- 959-- INTERSECT 960WITH RECURSIVE x(n) AS (SELECT 1 INTERSECT SELECT n+1 FROM x) 961 SELECT * FROM x; 962ERROR: recursive query "x" does not have the form non-recursive-term UNION [ALL] recursive-term 963LINE 1: WITH RECURSIVE x(n) AS (SELECT 1 INTERSECT SELECT n+1 FROM x... 964 ^ 965WITH RECURSIVE x(n) AS (SELECT 1 INTERSECT ALL SELECT n+1 FROM x) 966 SELECT * FROM x; 967ERROR: recursive query "x" does not have the form non-recursive-term UNION [ALL] recursive-term 968LINE 1: WITH RECURSIVE x(n) AS (SELECT 1 INTERSECT ALL SELECT n+1 FR... 969 ^ 970-- EXCEPT 971WITH RECURSIVE x(n) AS (SELECT 1 EXCEPT SELECT n+1 FROM x) 972 SELECT * FROM x; 973ERROR: recursive query "x" does not have the form non-recursive-term UNION [ALL] recursive-term 974LINE 1: WITH RECURSIVE x(n) AS (SELECT 1 EXCEPT SELECT n+1 FROM x) 975 ^ 976WITH RECURSIVE x(n) AS (SELECT 1 EXCEPT ALL SELECT n+1 FROM x) 977 SELECT * FROM x; 978ERROR: recursive query "x" does not have the form non-recursive-term UNION [ALL] recursive-term 979LINE 1: WITH RECURSIVE x(n) AS (SELECT 1 EXCEPT ALL SELECT n+1 FROM ... 980 ^ 981-- no non-recursive term 982WITH RECURSIVE x(n) AS (SELECT n FROM x) 983 SELECT * FROM x; 984ERROR: recursive query "x" does not have the form non-recursive-term UNION [ALL] recursive-term 985LINE 1: WITH RECURSIVE x(n) AS (SELECT n FROM x) 986 ^ 987-- recursive term in the left hand side (strictly speaking, should allow this) 988WITH RECURSIVE x(n) AS (SELECT n FROM x UNION ALL SELECT 1) 989 SELECT * FROM x; 990ERROR: recursive reference to query "x" must not appear within its non-recursive term 991LINE 1: WITH RECURSIVE x(n) AS (SELECT n FROM x UNION ALL SELECT 1) 992 ^ 993CREATE TEMPORARY TABLE y (a INTEGER); 994INSERT INTO y SELECT generate_series(1, 10); 995-- LEFT JOIN 996WITH RECURSIVE x(n) AS (SELECT a FROM y WHERE a = 1 997 UNION ALL 998 SELECT x.n+1 FROM y LEFT JOIN x ON x.n = y.a WHERE n < 10) 999SELECT * FROM x; 1000ERROR: recursive reference to query "x" must not appear within an outer join 1001LINE 3: SELECT x.n+1 FROM y LEFT JOIN x ON x.n = y.a WHERE n < 10) 1002 ^ 1003-- RIGHT JOIN 1004WITH RECURSIVE x(n) AS (SELECT a FROM y WHERE a = 1 1005 UNION ALL 1006 SELECT x.n+1 FROM x RIGHT JOIN y ON x.n = y.a WHERE n < 10) 1007SELECT * FROM x; 1008ERROR: recursive reference to query "x" must not appear within an outer join 1009LINE 3: SELECT x.n+1 FROM x RIGHT JOIN y ON x.n = y.a WHERE n < 10) 1010 ^ 1011-- FULL JOIN 1012WITH RECURSIVE x(n) AS (SELECT a FROM y WHERE a = 1 1013 UNION ALL 1014 SELECT x.n+1 FROM x FULL JOIN y ON x.n = y.a WHERE n < 10) 1015SELECT * FROM x; 1016ERROR: recursive reference to query "x" must not appear within an outer join 1017LINE 3: SELECT x.n+1 FROM x FULL JOIN y ON x.n = y.a WHERE n < 10) 1018 ^ 1019-- subquery 1020WITH RECURSIVE x(n) AS (SELECT 1 UNION ALL SELECT n+1 FROM x 1021 WHERE n IN (SELECT * FROM x)) 1022 SELECT * FROM x; 1023ERROR: recursive reference to query "x" must not appear within a subquery 1024LINE 2: WHERE n IN (SELECT * FROM x)) 1025 ^ 1026-- aggregate functions 1027WITH RECURSIVE x(n) AS (SELECT 1 UNION ALL SELECT count(*) FROM x) 1028 SELECT * FROM x; 1029ERROR: aggregate functions are not allowed in a recursive query's recursive term 1030LINE 1: WITH RECURSIVE x(n) AS (SELECT 1 UNION ALL SELECT count(*) F... 1031 ^ 1032WITH RECURSIVE x(n) AS (SELECT 1 UNION ALL SELECT sum(n) FROM x) 1033 SELECT * FROM x; 1034ERROR: aggregate functions are not allowed in a recursive query's recursive term 1035LINE 1: WITH RECURSIVE x(n) AS (SELECT 1 UNION ALL SELECT sum(n) FRO... 1036 ^ 1037-- ORDER BY 1038WITH RECURSIVE x(n) AS (SELECT 1 UNION ALL SELECT n+1 FROM x ORDER BY 1) 1039 SELECT * FROM x; 1040ERROR: ORDER BY in a recursive query is not implemented 1041LINE 1: ...VE x(n) AS (SELECT 1 UNION ALL SELECT n+1 FROM x ORDER BY 1) 1042 ^ 1043-- LIMIT/OFFSET 1044WITH RECURSIVE x(n) AS (SELECT 1 UNION ALL SELECT n+1 FROM x LIMIT 10 OFFSET 1) 1045 SELECT * FROM x; 1046ERROR: OFFSET in a recursive query is not implemented 1047LINE 1: ... AS (SELECT 1 UNION ALL SELECT n+1 FROM x LIMIT 10 OFFSET 1) 1048 ^ 1049-- FOR UPDATE 1050WITH RECURSIVE x(n) AS (SELECT 1 UNION ALL SELECT n+1 FROM x FOR UPDATE) 1051 SELECT * FROM x; 1052ERROR: FOR UPDATE/SHARE in a recursive query is not implemented 1053-- target list has a recursive query name 1054WITH RECURSIVE x(id) AS (values (1) 1055 UNION ALL 1056 SELECT (SELECT * FROM x) FROM x WHERE id < 5 1057) SELECT * FROM x; 1058ERROR: recursive reference to query "x" must not appear within a subquery 1059LINE 3: SELECT (SELECT * FROM x) FROM x WHERE id < 5 1060 ^ 1061-- mutual recursive query (not implemented) 1062WITH RECURSIVE 1063 x (id) AS (SELECT 1 UNION ALL SELECT id+1 FROM y WHERE id < 5), 1064 y (id) AS (SELECT 1 UNION ALL SELECT id+1 FROM x WHERE id < 5) 1065SELECT * FROM x; 1066ERROR: mutual recursion between WITH items is not implemented 1067LINE 2: x (id) AS (SELECT 1 UNION ALL SELECT id+1 FROM y WHERE id ... 1068 ^ 1069-- non-linear recursion is not allowed 1070WITH RECURSIVE foo(i) AS 1071 (values (1) 1072 UNION ALL 1073 (SELECT i+1 FROM foo WHERE i < 10 1074 UNION ALL 1075 SELECT i+1 FROM foo WHERE i < 5) 1076) SELECT * FROM foo; 1077ERROR: recursive reference to query "foo" must not appear more than once 1078LINE 6: SELECT i+1 FROM foo WHERE i < 5) 1079 ^ 1080WITH RECURSIVE foo(i) AS 1081 (values (1) 1082 UNION ALL 1083 SELECT * FROM 1084 (SELECT i+1 FROM foo WHERE i < 10 1085 UNION ALL 1086 SELECT i+1 FROM foo WHERE i < 5) AS t 1087) SELECT * FROM foo; 1088ERROR: recursive reference to query "foo" must not appear more than once 1089LINE 7: SELECT i+1 FROM foo WHERE i < 5) AS t 1090 ^ 1091WITH RECURSIVE foo(i) AS 1092 (values (1) 1093 UNION ALL 1094 (SELECT i+1 FROM foo WHERE i < 10 1095 EXCEPT 1096 SELECT i+1 FROM foo WHERE i < 5) 1097) SELECT * FROM foo; 1098ERROR: recursive reference to query "foo" must not appear within EXCEPT 1099LINE 6: SELECT i+1 FROM foo WHERE i < 5) 1100 ^ 1101WITH RECURSIVE foo(i) AS 1102 (values (1) 1103 UNION ALL 1104 (SELECT i+1 FROM foo WHERE i < 10 1105 INTERSECT 1106 SELECT i+1 FROM foo WHERE i < 5) 1107) SELECT * FROM foo; 1108ERROR: recursive reference to query "foo" must not appear more than once 1109LINE 6: SELECT i+1 FROM foo WHERE i < 5) 1110 ^ 1111-- Wrong type induced from non-recursive term 1112WITH RECURSIVE foo(i) AS 1113 (SELECT i FROM (VALUES(1),(2)) t(i) 1114 UNION ALL 1115 SELECT (i+1)::numeric(10,0) FROM foo WHERE i < 10) 1116SELECT * FROM foo; 1117ERROR: recursive query "foo" column 1 has type integer in non-recursive term but type numeric overall 1118LINE 2: (SELECT i FROM (VALUES(1),(2)) t(i) 1119 ^ 1120HINT: Cast the output of the non-recursive term to the correct type. 1121-- rejects different typmod, too (should we allow this?) 1122WITH RECURSIVE foo(i) AS 1123 (SELECT i::numeric(3,0) FROM (VALUES(1),(2)) t(i) 1124 UNION ALL 1125 SELECT (i+1)::numeric(10,0) FROM foo WHERE i < 10) 1126SELECT * FROM foo; 1127ERROR: recursive query "foo" column 1 has type numeric(3,0) in non-recursive term but type numeric overall 1128LINE 2: (SELECT i::numeric(3,0) FROM (VALUES(1),(2)) t(i) 1129 ^ 1130HINT: Cast the output of the non-recursive term to the correct type. 1131-- disallow OLD/NEW reference in CTE 1132CREATE TEMPORARY TABLE x (n integer); 1133CREATE RULE r2 AS ON UPDATE TO x DO INSTEAD 1134 WITH t AS (SELECT OLD.*) UPDATE y SET a = t.n FROM t; 1135ERROR: cannot refer to OLD within WITH query 1136-- 1137-- test for bug #4902 1138-- 1139with cte(foo) as ( values(42) ) values((select foo from cte)); 1140 column1 1141--------- 1142 42 1143(1 row) 1144 1145with cte(foo) as ( select 42 ) select * from ((select foo from cte)) q; 1146 foo 1147----- 1148 42 1149(1 row) 1150 1151-- test CTE referencing an outer-level variable (to see that changed-parameter 1152-- signaling still works properly after fixing this bug) 1153select ( with cte(foo) as ( values(f1) ) 1154 select (select foo from cte) ) 1155from int4_tbl; 1156 foo 1157------------- 1158 0 1159 123456 1160 -123456 1161 2147483647 1162 -2147483647 1163(5 rows) 1164 1165select ( with cte(foo) as ( values(f1) ) 1166 values((select foo from cte)) ) 1167from int4_tbl; 1168 column1 1169------------- 1170 0 1171 123456 1172 -123456 1173 2147483647 1174 -2147483647 1175(5 rows) 1176 1177-- 1178-- test for nested-recursive-WITH bug 1179-- 1180WITH RECURSIVE t(j) AS ( 1181 WITH RECURSIVE s(i) AS ( 1182 VALUES (1) 1183 UNION ALL 1184 SELECT i+1 FROM s WHERE i < 10 1185 ) 1186 SELECT i FROM s 1187 UNION ALL 1188 SELECT j+1 FROM t WHERE j < 10 1189) 1190SELECT * FROM t; 1191 j 1192---- 1193 1 1194 2 1195 3 1196 4 1197 5 1198 6 1199 7 1200 8 1201 9 1202 10 1203 2 1204 3 1205 4 1206 5 1207 6 1208 7 1209 8 1210 9 1211 10 1212 3 1213 4 1214 5 1215 6 1216 7 1217 8 1218 9 1219 10 1220 4 1221 5 1222 6 1223 7 1224 8 1225 9 1226 10 1227 5 1228 6 1229 7 1230 8 1231 9 1232 10 1233 6 1234 7 1235 8 1236 9 1237 10 1238 7 1239 8 1240 9 1241 10 1242 8 1243 9 1244 10 1245 9 1246 10 1247 10 1248(55 rows) 1249 1250-- 1251-- test WITH attached to intermediate-level set operation 1252-- 1253WITH outermost(x) AS ( 1254 SELECT 1 1255 UNION (WITH innermost as (SELECT 2) 1256 SELECT * FROM innermost 1257 UNION SELECT 3) 1258) 1259SELECT * FROM outermost ORDER BY 1; 1260 x 1261--- 1262 1 1263 2 1264 3 1265(3 rows) 1266 1267WITH outermost(x) AS ( 1268 SELECT 1 1269 UNION (WITH innermost as (SELECT 2) 1270 SELECT * FROM outermost -- fail 1271 UNION SELECT * FROM innermost) 1272) 1273SELECT * FROM outermost ORDER BY 1; 1274ERROR: relation "outermost" does not exist 1275LINE 4: SELECT * FROM outermost 1276 ^ 1277DETAIL: There is a WITH item named "outermost", but it cannot be referenced from this part of the query. 1278HINT: Use WITH RECURSIVE, or re-order the WITH items to remove forward references. 1279WITH RECURSIVE outermost(x) AS ( 1280 SELECT 1 1281 UNION (WITH innermost as (SELECT 2) 1282 SELECT * FROM outermost 1283 UNION SELECT * FROM innermost) 1284) 1285SELECT * FROM outermost ORDER BY 1; 1286 x 1287--- 1288 1 1289 2 1290(2 rows) 1291 1292WITH RECURSIVE outermost(x) AS ( 1293 WITH innermost as (SELECT 2 FROM outermost) -- fail 1294 SELECT * FROM innermost 1295 UNION SELECT * from outermost 1296) 1297SELECT * FROM outermost ORDER BY 1; 1298ERROR: recursive reference to query "outermost" must not appear within a subquery 1299LINE 2: WITH innermost as (SELECT 2 FROM outermost) 1300 ^ 1301-- 1302-- This test will fail with the old implementation of PARAM_EXEC parameter 1303-- assignment, because the "q1" Var passed down to A's targetlist subselect 1304-- looks exactly like the "A.id" Var passed down to C's subselect, causing 1305-- the old code to give them the same runtime PARAM_EXEC slot. But the 1306-- lifespans of the two parameters overlap, thanks to B also reading A. 1307-- 1308with 1309A as ( select q2 as id, (select q1) as x from int8_tbl ), 1310B as ( select id, row_number() over (partition by id) as r from A ), 1311C as ( select A.id, array(select B.id from B where B.id = A.id) from A ) 1312select * from C; 1313 id | array 1314-------------------+------------------------------------- 1315 456 | {456} 1316 4567890123456789 | {4567890123456789,4567890123456789} 1317 123 | {123} 1318 4567890123456789 | {4567890123456789,4567890123456789} 1319 -4567890123456789 | {-4567890123456789} 1320(5 rows) 1321 1322-- 1323-- Test CTEs read in non-initialization orders 1324-- 1325WITH RECURSIVE 1326 tab(id_key,link) AS (VALUES (1,17), (2,17), (3,17), (4,17), (6,17), (5,17)), 1327 iter (id_key, row_type, link) AS ( 1328 SELECT 0, 'base', 17 1329 UNION ALL ( 1330 WITH remaining(id_key, row_type, link, min) AS ( 1331 SELECT tab.id_key, 'true'::text, iter.link, MIN(tab.id_key) OVER () 1332 FROM tab INNER JOIN iter USING (link) 1333 WHERE tab.id_key > iter.id_key 1334 ), 1335 first_remaining AS ( 1336 SELECT id_key, row_type, link 1337 FROM remaining 1338 WHERE id_key=min 1339 ), 1340 effect AS ( 1341 SELECT tab.id_key, 'new'::text, tab.link 1342 FROM first_remaining e INNER JOIN tab ON e.id_key=tab.id_key 1343 WHERE e.row_type = 'false' 1344 ) 1345 SELECT * FROM first_remaining 1346 UNION ALL SELECT * FROM effect 1347 ) 1348 ) 1349SELECT * FROM iter; 1350 id_key | row_type | link 1351--------+----------+------ 1352 0 | base | 17 1353 1 | true | 17 1354 2 | true | 17 1355 3 | true | 17 1356 4 | true | 17 1357 5 | true | 17 1358 6 | true | 17 1359(7 rows) 1360 1361WITH RECURSIVE 1362 tab(id_key,link) AS (VALUES (1,17), (2,17), (3,17), (4,17), (6,17), (5,17)), 1363 iter (id_key, row_type, link) AS ( 1364 SELECT 0, 'base', 17 1365 UNION ( 1366 WITH remaining(id_key, row_type, link, min) AS ( 1367 SELECT tab.id_key, 'true'::text, iter.link, MIN(tab.id_key) OVER () 1368 FROM tab INNER JOIN iter USING (link) 1369 WHERE tab.id_key > iter.id_key 1370 ), 1371 first_remaining AS ( 1372 SELECT id_key, row_type, link 1373 FROM remaining 1374 WHERE id_key=min 1375 ), 1376 effect AS ( 1377 SELECT tab.id_key, 'new'::text, tab.link 1378 FROM first_remaining e INNER JOIN tab ON e.id_key=tab.id_key 1379 WHERE e.row_type = 'false' 1380 ) 1381 SELECT * FROM first_remaining 1382 UNION ALL SELECT * FROM effect 1383 ) 1384 ) 1385SELECT * FROM iter; 1386 id_key | row_type | link 1387--------+----------+------ 1388 0 | base | 17 1389 1 | true | 17 1390 2 | true | 17 1391 3 | true | 17 1392 4 | true | 17 1393 5 | true | 17 1394 6 | true | 17 1395(7 rows) 1396 1397-- 1398-- Data-modifying statements in WITH 1399-- 1400-- INSERT ... RETURNING 1401WITH t AS ( 1402 INSERT INTO y 1403 VALUES 1404 (11), 1405 (12), 1406 (13), 1407 (14), 1408 (15), 1409 (16), 1410 (17), 1411 (18), 1412 (19), 1413 (20) 1414 RETURNING * 1415) 1416SELECT * FROM t; 1417 a 1418---- 1419 11 1420 12 1421 13 1422 14 1423 15 1424 16 1425 17 1426 18 1427 19 1428 20 1429(10 rows) 1430 1431SELECT * FROM y; 1432 a 1433---- 1434 1 1435 2 1436 3 1437 4 1438 5 1439 6 1440 7 1441 8 1442 9 1443 10 1444 11 1445 12 1446 13 1447 14 1448 15 1449 16 1450 17 1451 18 1452 19 1453 20 1454(20 rows) 1455 1456-- UPDATE ... RETURNING 1457WITH t AS ( 1458 UPDATE y 1459 SET a=a+1 1460 RETURNING * 1461) 1462SELECT * FROM t; 1463 a 1464---- 1465 2 1466 3 1467 4 1468 5 1469 6 1470 7 1471 8 1472 9 1473 10 1474 11 1475 12 1476 13 1477 14 1478 15 1479 16 1480 17 1481 18 1482 19 1483 20 1484 21 1485(20 rows) 1486 1487SELECT * FROM y; 1488 a 1489---- 1490 2 1491 3 1492 4 1493 5 1494 6 1495 7 1496 8 1497 9 1498 10 1499 11 1500 12 1501 13 1502 14 1503 15 1504 16 1505 17 1506 18 1507 19 1508 20 1509 21 1510(20 rows) 1511 1512-- DELETE ... RETURNING 1513WITH t AS ( 1514 DELETE FROM y 1515 WHERE a <= 10 1516 RETURNING * 1517) 1518SELECT * FROM t; 1519 a 1520---- 1521 2 1522 3 1523 4 1524 5 1525 6 1526 7 1527 8 1528 9 1529 10 1530(9 rows) 1531 1532SELECT * FROM y; 1533 a 1534---- 1535 11 1536 12 1537 13 1538 14 1539 15 1540 16 1541 17 1542 18 1543 19 1544 20 1545 21 1546(11 rows) 1547 1548-- forward reference 1549WITH RECURSIVE t AS ( 1550 INSERT INTO y 1551 SELECT a+5 FROM t2 WHERE a > 5 1552 RETURNING * 1553), t2 AS ( 1554 UPDATE y SET a=a-11 RETURNING * 1555) 1556SELECT * FROM t 1557UNION ALL 1558SELECT * FROM t2; 1559 a 1560---- 1561 11 1562 12 1563 13 1564 14 1565 15 1566 0 1567 1 1568 2 1569 3 1570 4 1571 5 1572 6 1573 7 1574 8 1575 9 1576 10 1577(16 rows) 1578 1579SELECT * FROM y; 1580 a 1581---- 1582 0 1583 1 1584 2 1585 3 1586 4 1587 5 1588 6 1589 11 1590 7 1591 12 1592 8 1593 13 1594 9 1595 14 1596 10 1597 15 1598(16 rows) 1599 1600-- unconditional DO INSTEAD rule 1601CREATE RULE y_rule AS ON DELETE TO y DO INSTEAD 1602 INSERT INTO y VALUES(42) RETURNING *; 1603WITH t AS ( 1604 DELETE FROM y RETURNING * 1605) 1606SELECT * FROM t; 1607 a 1608---- 1609 42 1610(1 row) 1611 1612SELECT * FROM y; 1613 a 1614---- 1615 0 1616 1 1617 2 1618 3 1619 4 1620 5 1621 6 1622 11 1623 7 1624 12 1625 8 1626 13 1627 9 1628 14 1629 10 1630 15 1631 42 1632(17 rows) 1633 1634DROP RULE y_rule ON y; 1635-- check merging of outer CTE with CTE in a rule action 1636CREATE TEMP TABLE bug6051 AS 1637 select i from generate_series(1,3) as t(i); 1638SELECT * FROM bug6051; 1639 i 1640--- 1641 1 1642 2 1643 3 1644(3 rows) 1645 1646WITH t1 AS ( DELETE FROM bug6051 RETURNING * ) 1647INSERT INTO bug6051 SELECT * FROM t1; 1648SELECT * FROM bug6051; 1649 i 1650--- 1651 1 1652 2 1653 3 1654(3 rows) 1655 1656CREATE TEMP TABLE bug6051_2 (i int); 1657CREATE RULE bug6051_ins AS ON INSERT TO bug6051 DO INSTEAD 1658 INSERT INTO bug6051_2 1659 VALUES(NEW.i); 1660WITH t1 AS ( DELETE FROM bug6051 RETURNING * ) 1661INSERT INTO bug6051 SELECT * FROM t1; 1662SELECT * FROM bug6051; 1663 i 1664--- 1665(0 rows) 1666 1667SELECT * FROM bug6051_2; 1668 i 1669--- 1670 1 1671 2 1672 3 1673(3 rows) 1674 1675-- check INSERT...SELECT rule actions are disallowed on commands 1676-- that have modifyingCTEs 1677CREATE OR REPLACE RULE bug6051_ins AS ON INSERT TO bug6051 DO INSTEAD 1678 INSERT INTO bug6051_2 1679 SELECT NEW.i; 1680WITH t1 AS ( DELETE FROM bug6051 RETURNING * ) 1681INSERT INTO bug6051 SELECT * FROM t1; 1682ERROR: INSERT...SELECT rule actions are not supported for queries having data-modifying statements in WITH 1683-- silly example to verify that hasModifyingCTE flag is propagated 1684CREATE TEMP TABLE bug6051_3 AS 1685 SELECT a FROM generate_series(11,13) AS a; 1686CREATE RULE bug6051_3_ins AS ON INSERT TO bug6051_3 DO INSTEAD 1687 SELECT i FROM bug6051_2; 1688BEGIN; SET LOCAL force_parallel_mode = on; 1689WITH t1 AS ( DELETE FROM bug6051_3 RETURNING * ) 1690 INSERT INTO bug6051_3 SELECT * FROM t1; 1691 i 1692--- 1693 1 1694 2 1695 3 1696 1 1697 2 1698 3 1699 1 1700 2 1701 3 1702(9 rows) 1703 1704COMMIT; 1705SELECT * FROM bug6051_3; 1706 a 1707--- 1708(0 rows) 1709 1710-- a truly recursive CTE in the same list 1711WITH RECURSIVE t(a) AS ( 1712 SELECT 0 1713 UNION ALL 1714 SELECT a+1 FROM t WHERE a+1 < 5 1715), t2 as ( 1716 INSERT INTO y 1717 SELECT * FROM t RETURNING * 1718) 1719SELECT * FROM t2 JOIN y USING (a) ORDER BY a; 1720 a 1721--- 1722 0 1723 1 1724 2 1725 3 1726 4 1727(5 rows) 1728 1729SELECT * FROM y; 1730 a 1731---- 1732 0 1733 1 1734 2 1735 3 1736 4 1737 5 1738 6 1739 11 1740 7 1741 12 1742 8 1743 13 1744 9 1745 14 1746 10 1747 15 1748 42 1749 0 1750 1 1751 2 1752 3 1753 4 1754(22 rows) 1755 1756-- data-modifying WITH in a modifying statement 1757WITH t AS ( 1758 DELETE FROM y 1759 WHERE a <= 10 1760 RETURNING * 1761) 1762INSERT INTO y SELECT -a FROM t RETURNING *; 1763 a 1764----- 1765 0 1766 -1 1767 -2 1768 -3 1769 -4 1770 -5 1771 -6 1772 -7 1773 -8 1774 -9 1775 -10 1776 0 1777 -1 1778 -2 1779 -3 1780 -4 1781(16 rows) 1782 1783SELECT * FROM y; 1784 a 1785----- 1786 11 1787 12 1788 13 1789 14 1790 15 1791 42 1792 0 1793 -1 1794 -2 1795 -3 1796 -4 1797 -5 1798 -6 1799 -7 1800 -8 1801 -9 1802 -10 1803 0 1804 -1 1805 -2 1806 -3 1807 -4 1808(22 rows) 1809 1810-- check that WITH query is run to completion even if outer query isn't 1811WITH t AS ( 1812 UPDATE y SET a = a * 100 RETURNING * 1813) 1814SELECT * FROM t LIMIT 10; 1815 a 1816------ 1817 1100 1818 1200 1819 1300 1820 1400 1821 1500 1822 4200 1823 0 1824 -100 1825 -200 1826 -300 1827(10 rows) 1828 1829SELECT * FROM y; 1830 a 1831------- 1832 1100 1833 1200 1834 1300 1835 1400 1836 1500 1837 4200 1838 0 1839 -100 1840 -200 1841 -300 1842 -400 1843 -500 1844 -600 1845 -700 1846 -800 1847 -900 1848 -1000 1849 0 1850 -100 1851 -200 1852 -300 1853 -400 1854(22 rows) 1855 1856-- data-modifying WITH containing INSERT...ON CONFLICT DO UPDATE 1857CREATE TABLE withz AS SELECT i AS k, (i || ' v')::text v FROM generate_series(1, 16, 3) i; 1858ALTER TABLE withz ADD UNIQUE (k); 1859WITH t AS ( 1860 INSERT INTO withz SELECT i, 'insert' 1861 FROM generate_series(0, 16) i 1862 ON CONFLICT (k) DO UPDATE SET v = withz.v || ', now update' 1863 RETURNING * 1864) 1865SELECT * FROM t JOIN y ON t.k = y.a ORDER BY a, k; 1866 k | v | a 1867---+--------+--- 1868 0 | insert | 0 1869 0 | insert | 0 1870(2 rows) 1871 1872-- Test EXCLUDED.* reference within CTE 1873WITH aa AS ( 1874 INSERT INTO withz VALUES(1, 5) ON CONFLICT (k) DO UPDATE SET v = EXCLUDED.v 1875 WHERE withz.k != EXCLUDED.k 1876 RETURNING * 1877) 1878SELECT * FROM aa; 1879 k | v 1880---+--- 1881(0 rows) 1882 1883-- New query/snapshot demonstrates side-effects of previous query. 1884SELECT * FROM withz ORDER BY k; 1885 k | v 1886----+------------------ 1887 0 | insert 1888 1 | 1 v, now update 1889 2 | insert 1890 3 | insert 1891 4 | 4 v, now update 1892 5 | insert 1893 6 | insert 1894 7 | 7 v, now update 1895 8 | insert 1896 9 | insert 1897 10 | 10 v, now update 1898 11 | insert 1899 12 | insert 1900 13 | 13 v, now update 1901 14 | insert 1902 15 | insert 1903 16 | 16 v, now update 1904(17 rows) 1905 1906-- 1907-- Ensure subqueries within the update clause work, even if they 1908-- reference outside values 1909-- 1910WITH aa AS (SELECT 1 a, 2 b) 1911INSERT INTO withz VALUES(1, 'insert') 1912ON CONFLICT (k) DO UPDATE SET v = (SELECT b || ' update' FROM aa WHERE a = 1 LIMIT 1); 1913WITH aa AS (SELECT 1 a, 2 b) 1914INSERT INTO withz VALUES(1, 'insert') 1915ON CONFLICT (k) DO UPDATE SET v = ' update' WHERE withz.k = (SELECT a FROM aa); 1916WITH aa AS (SELECT 1 a, 2 b) 1917INSERT INTO withz VALUES(1, 'insert') 1918ON CONFLICT (k) DO UPDATE SET v = (SELECT b || ' update' FROM aa WHERE a = 1 LIMIT 1); 1919WITH aa AS (SELECT 'a' a, 'b' b UNION ALL SELECT 'a' a, 'b' b) 1920INSERT INTO withz VALUES(1, 'insert') 1921ON CONFLICT (k) DO UPDATE SET v = (SELECT b || ' update' FROM aa WHERE a = 'a' LIMIT 1); 1922WITH aa AS (SELECT 1 a, 2 b) 1923INSERT INTO withz VALUES(1, (SELECT b || ' insert' FROM aa WHERE a = 1 )) 1924ON CONFLICT (k) DO UPDATE SET v = (SELECT b || ' update' FROM aa WHERE a = 1 LIMIT 1); 1925-- Update a row more than once, in different parts of a wCTE. That is 1926-- an allowed, presumably very rare, edge case, but since it was 1927-- broken in the past, having a test seems worthwhile. 1928WITH simpletup AS ( 1929 SELECT 2 k, 'Green' v), 1930upsert_cte AS ( 1931 INSERT INTO withz VALUES(2, 'Blue') ON CONFLICT (k) DO 1932 UPDATE SET (k, v) = (SELECT k, v FROM simpletup WHERE simpletup.k = withz.k) 1933 RETURNING k, v) 1934INSERT INTO withz VALUES(2, 'Red') ON CONFLICT (k) DO 1935UPDATE SET (k, v) = (SELECT k, v FROM upsert_cte WHERE upsert_cte.k = withz.k) 1936RETURNING k, v; 1937 k | v 1938---+--- 1939(0 rows) 1940 1941DROP TABLE withz; 1942-- check that run to completion happens in proper ordering 1943TRUNCATE TABLE y; 1944INSERT INTO y SELECT generate_series(1, 3); 1945CREATE TEMPORARY TABLE yy (a INTEGER); 1946WITH RECURSIVE t1 AS ( 1947 INSERT INTO y SELECT * FROM y RETURNING * 1948), t2 AS ( 1949 INSERT INTO yy SELECT * FROM t1 RETURNING * 1950) 1951SELECT 1; 1952 ?column? 1953---------- 1954 1 1955(1 row) 1956 1957SELECT * FROM y; 1958 a 1959--- 1960 1 1961 2 1962 3 1963 1 1964 2 1965 3 1966(6 rows) 1967 1968SELECT * FROM yy; 1969 a 1970--- 1971 1 1972 2 1973 3 1974(3 rows) 1975 1976WITH RECURSIVE t1 AS ( 1977 INSERT INTO yy SELECT * FROM t2 RETURNING * 1978), t2 AS ( 1979 INSERT INTO y SELECT * FROM y RETURNING * 1980) 1981SELECT 1; 1982 ?column? 1983---------- 1984 1 1985(1 row) 1986 1987SELECT * FROM y; 1988 a 1989--- 1990 1 1991 2 1992 3 1993 1 1994 2 1995 3 1996 1 1997 2 1998 3 1999 1 2000 2 2001 3 2002(12 rows) 2003 2004SELECT * FROM yy; 2005 a 2006--- 2007 1 2008 2 2009 3 2010 1 2011 2 2012 3 2013 1 2014 2 2015 3 2016(9 rows) 2017 2018-- triggers 2019TRUNCATE TABLE y; 2020INSERT INTO y SELECT generate_series(1, 10); 2021CREATE FUNCTION y_trigger() RETURNS trigger AS $$ 2022begin 2023 raise notice 'y_trigger: a = %', new.a; 2024 return new; 2025end; 2026$$ LANGUAGE plpgsql; 2027CREATE TRIGGER y_trig BEFORE INSERT ON y FOR EACH ROW 2028 EXECUTE PROCEDURE y_trigger(); 2029WITH t AS ( 2030 INSERT INTO y 2031 VALUES 2032 (21), 2033 (22), 2034 (23) 2035 RETURNING * 2036) 2037SELECT * FROM t; 2038NOTICE: y_trigger: a = 21 2039NOTICE: y_trigger: a = 22 2040NOTICE: y_trigger: a = 23 2041 a 2042---- 2043 21 2044 22 2045 23 2046(3 rows) 2047 2048SELECT * FROM y; 2049 a 2050---- 2051 1 2052 2 2053 3 2054 4 2055 5 2056 6 2057 7 2058 8 2059 9 2060 10 2061 21 2062 22 2063 23 2064(13 rows) 2065 2066DROP TRIGGER y_trig ON y; 2067CREATE TRIGGER y_trig AFTER INSERT ON y FOR EACH ROW 2068 EXECUTE PROCEDURE y_trigger(); 2069WITH t AS ( 2070 INSERT INTO y 2071 VALUES 2072 (31), 2073 (32), 2074 (33) 2075 RETURNING * 2076) 2077SELECT * FROM t LIMIT 1; 2078NOTICE: y_trigger: a = 31 2079NOTICE: y_trigger: a = 32 2080NOTICE: y_trigger: a = 33 2081 a 2082---- 2083 31 2084(1 row) 2085 2086SELECT * FROM y; 2087 a 2088---- 2089 1 2090 2 2091 3 2092 4 2093 5 2094 6 2095 7 2096 8 2097 9 2098 10 2099 21 2100 22 2101 23 2102 31 2103 32 2104 33 2105(16 rows) 2106 2107DROP TRIGGER y_trig ON y; 2108CREATE OR REPLACE FUNCTION y_trigger() RETURNS trigger AS $$ 2109begin 2110 raise notice 'y_trigger'; 2111 return null; 2112end; 2113$$ LANGUAGE plpgsql; 2114CREATE TRIGGER y_trig AFTER INSERT ON y FOR EACH STATEMENT 2115 EXECUTE PROCEDURE y_trigger(); 2116WITH t AS ( 2117 INSERT INTO y 2118 VALUES 2119 (41), 2120 (42), 2121 (43) 2122 RETURNING * 2123) 2124SELECT * FROM t; 2125NOTICE: y_trigger 2126 a 2127---- 2128 41 2129 42 2130 43 2131(3 rows) 2132 2133SELECT * FROM y; 2134 a 2135---- 2136 1 2137 2 2138 3 2139 4 2140 5 2141 6 2142 7 2143 8 2144 9 2145 10 2146 21 2147 22 2148 23 2149 31 2150 32 2151 33 2152 41 2153 42 2154 43 2155(19 rows) 2156 2157DROP TRIGGER y_trig ON y; 2158DROP FUNCTION y_trigger(); 2159-- WITH attached to inherited UPDATE or DELETE 2160CREATE TEMP TABLE parent ( id int, val text ); 2161CREATE TEMP TABLE child1 ( ) INHERITS ( parent ); 2162CREATE TEMP TABLE child2 ( ) INHERITS ( parent ); 2163INSERT INTO parent VALUES ( 1, 'p1' ); 2164INSERT INTO child1 VALUES ( 11, 'c11' ),( 12, 'c12' ); 2165INSERT INTO child2 VALUES ( 23, 'c21' ),( 24, 'c22' ); 2166WITH rcte AS ( SELECT sum(id) AS totalid FROM parent ) 2167UPDATE parent SET id = id + totalid FROM rcte; 2168SELECT * FROM parent; 2169 id | val 2170----+----- 2171 72 | p1 2172 82 | c11 2173 83 | c12 2174 94 | c21 2175 95 | c22 2176(5 rows) 2177 2178WITH wcte AS ( INSERT INTO child1 VALUES ( 42, 'new' ) RETURNING id AS newid ) 2179UPDATE parent SET id = id + newid FROM wcte; 2180SELECT * FROM parent; 2181 id | val 2182-----+----- 2183 114 | p1 2184 42 | new 2185 124 | c11 2186 125 | c12 2187 136 | c21 2188 137 | c22 2189(6 rows) 2190 2191WITH rcte AS ( SELECT max(id) AS maxid FROM parent ) 2192DELETE FROM parent USING rcte WHERE id = maxid; 2193SELECT * FROM parent; 2194 id | val 2195-----+----- 2196 114 | p1 2197 42 | new 2198 124 | c11 2199 125 | c12 2200 136 | c21 2201(5 rows) 2202 2203WITH wcte AS ( INSERT INTO child2 VALUES ( 42, 'new2' ) RETURNING id AS newid ) 2204DELETE FROM parent USING wcte WHERE id = newid; 2205SELECT * FROM parent; 2206 id | val 2207-----+------ 2208 114 | p1 2209 124 | c11 2210 125 | c12 2211 136 | c21 2212 42 | new2 2213(5 rows) 2214 2215-- check EXPLAIN VERBOSE for a wCTE with RETURNING 2216EXPLAIN (VERBOSE, COSTS OFF) 2217WITH wcte AS ( INSERT INTO int8_tbl VALUES ( 42, 47 ) RETURNING q2 ) 2218DELETE FROM a USING wcte WHERE aa = q2; 2219 QUERY PLAN 2220---------------------------------------------------- 2221 Delete on public.a 2222 Delete on public.a 2223 Delete on public.b 2224 Delete on public.c 2225 Delete on public.d 2226 CTE wcte 2227 -> Insert on public.int8_tbl 2228 Output: int8_tbl.q2 2229 -> Result 2230 Output: '42'::bigint, '47'::bigint 2231 -> Nested Loop 2232 Output: a.ctid, wcte.* 2233 Join Filter: (a.aa = wcte.q2) 2234 -> Seq Scan on public.a 2235 Output: a.ctid, a.aa 2236 -> CTE Scan on wcte 2237 Output: wcte.*, wcte.q2 2238 -> Nested Loop 2239 Output: b.ctid, wcte.* 2240 Join Filter: (b.aa = wcte.q2) 2241 -> Seq Scan on public.b 2242 Output: b.ctid, b.aa 2243 -> CTE Scan on wcte 2244 Output: wcte.*, wcte.q2 2245 -> Nested Loop 2246 Output: c.ctid, wcte.* 2247 Join Filter: (c.aa = wcte.q2) 2248 -> Seq Scan on public.c 2249 Output: c.ctid, c.aa 2250 -> CTE Scan on wcte 2251 Output: wcte.*, wcte.q2 2252 -> Nested Loop 2253 Output: d.ctid, wcte.* 2254 Join Filter: (d.aa = wcte.q2) 2255 -> Seq Scan on public.d 2256 Output: d.ctid, d.aa 2257 -> CTE Scan on wcte 2258 Output: wcte.*, wcte.q2 2259(38 rows) 2260 2261-- error cases 2262-- data-modifying WITH tries to use its own output 2263WITH RECURSIVE t AS ( 2264 INSERT INTO y 2265 SELECT * FROM t 2266) 2267VALUES(FALSE); 2268ERROR: recursive query "t" must not contain data-modifying statements 2269LINE 1: WITH RECURSIVE t AS ( 2270 ^ 2271-- no RETURNING in a referenced data-modifying WITH 2272WITH t AS ( 2273 INSERT INTO y VALUES(0) 2274) 2275SELECT * FROM t; 2276ERROR: WITH query "t" does not have a RETURNING clause 2277LINE 4: SELECT * FROM t; 2278 ^ 2279-- data-modifying WITH allowed only at the top level 2280SELECT * FROM ( 2281 WITH t AS (UPDATE y SET a=a+1 RETURNING *) 2282 SELECT * FROM t 2283) ss; 2284ERROR: WITH clause containing a data-modifying statement must be at the top level 2285LINE 2: WITH t AS (UPDATE y SET a=a+1 RETURNING *) 2286 ^ 2287-- most variants of rules aren't allowed 2288CREATE RULE y_rule AS ON INSERT TO y WHERE a=0 DO INSTEAD DELETE FROM y; 2289WITH t AS ( 2290 INSERT INTO y VALUES(0) 2291) 2292VALUES(FALSE); 2293ERROR: conditional DO INSTEAD rules are not supported for data-modifying statements in WITH 2294CREATE OR REPLACE RULE y_rule AS ON INSERT TO y DO INSTEAD NOTHING; 2295WITH t AS ( 2296 INSERT INTO y VALUES(0) 2297) 2298VALUES(FALSE); 2299ERROR: DO INSTEAD NOTHING rules are not supported for data-modifying statements in WITH 2300CREATE OR REPLACE RULE y_rule AS ON INSERT TO y DO INSTEAD NOTIFY foo; 2301WITH t AS ( 2302 INSERT INTO y VALUES(0) 2303) 2304VALUES(FALSE); 2305ERROR: DO INSTEAD NOTIFY rules are not supported for data-modifying statements in WITH 2306CREATE OR REPLACE RULE y_rule AS ON INSERT TO y DO ALSO NOTIFY foo; 2307WITH t AS ( 2308 INSERT INTO y VALUES(0) 2309) 2310VALUES(FALSE); 2311ERROR: DO ALSO rules are not supported for data-modifying statements in WITH 2312CREATE OR REPLACE RULE y_rule AS ON INSERT TO y 2313 DO INSTEAD (NOTIFY foo; NOTIFY bar); 2314WITH t AS ( 2315 INSERT INTO y VALUES(0) 2316) 2317VALUES(FALSE); 2318ERROR: multi-statement DO INSTEAD rules are not supported for data-modifying statements in WITH 2319DROP RULE y_rule ON y; 2320-- check that parser lookahead for WITH doesn't cause any odd behavior 2321create table foo (with baz); -- fail, WITH is a reserved word 2322ERROR: syntax error at or near "with" 2323LINE 1: create table foo (with baz); 2324 ^ 2325create table foo (with ordinality); -- fail, WITH is a reserved word 2326ERROR: syntax error at or near "with" 2327LINE 1: create table foo (with ordinality); 2328 ^ 2329with ordinality as (select 1 as x) select * from ordinality; 2330 x 2331--- 2332 1 2333(1 row) 2334 2335-- check sane response to attempt to modify CTE relation 2336WITH test AS (SELECT 42) INSERT INTO test VALUES (1); 2337ERROR: relation "test" does not exist 2338LINE 1: WITH test AS (SELECT 42) INSERT INTO test VALUES (1); 2339 ^ 2340-- check response to attempt to modify table with same name as a CTE (perhaps 2341-- surprisingly it works, because CTEs don't hide tables from data-modifying 2342-- statements) 2343create temp table test (i int); 2344with test as (select 42) insert into test select * from test; 2345select * from test; 2346 i 2347---- 2348 42 2349(1 row) 2350 2351drop table test; 2352