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