granicus.if.org Git - postgresql/blob - src/test/regress/sql/with.sql

   1 --
   2 -- Tests for common table expressions (WITH query, ... SELECT ...)
   3 --
   4
   5 -- Basic WITH
   6 WITH q1(x,y) AS (SELECT 1,2)
   7 SELECT * FROM q1, q1 AS q2;
   8
   9 -- Multiple uses are evaluated only once
  10 SELECT count(*) FROM (
  11   WITH q1(x) AS (SELECT random() FROM generate_series(1, 5))
  12     SELECT * FROM q1
  13   UNION
  14     SELECT * FROM q1
  15 ) ss;
  16
  17 -- WITH RECURSIVE
  18
  19 -- sum of 1..100
  20 WITH RECURSIVE t(n) AS (
  21     VALUES (1)
  22 UNION ALL
  23     SELECT n+1 FROM t WHERE n < 100
  24 )
  25 SELECT sum(n) FROM t;
  26
  27 WITH RECURSIVE t(n) AS (
  28     SELECT (VALUES(1))
  29 UNION ALL
  30     SELECT n+1 FROM t WHERE n < 5
  31 )
  32 SELECT * FROM t;
  33
  34 -- This is an infinite loop with UNION ALL, but not with UNION
  35 WITH RECURSIVE t(n) AS (
  36     SELECT 1
  37 UNION
  38     SELECT 10-n FROM t)
  39 SELECT * FROM t;
  40
  41 -- This'd be an infinite loop, but outside query reads only as much as needed
  42 WITH RECURSIVE t(n) AS (
  43     VALUES (1)
  44 UNION ALL
  45     SELECT n+1 FROM t)
  46 SELECT * FROM t LIMIT 10;
  47
  48 -- UNION case should have same property
  49 WITH RECURSIVE t(n) AS (
  50     SELECT 1
  51 UNION
  52     SELECT n+1 FROM t)
  53 SELECT * FROM t LIMIT 10;
  54
  55 -- Test behavior with an unknown-type literal in the WITH
  56 WITH q AS (SELECT 'foo' AS x)
  57 SELECT x, x IS OF (unknown) as is_unknown FROM q;
  58
  59 WITH RECURSIVE t(n) AS (
  60     SELECT 'foo'
  61 UNION ALL
  62     SELECT n || ' bar' FROM t WHERE length(n) < 20
  63 )
  64 SELECT n, n IS OF (text) as is_text FROM t;
  65
  66 --
  67 -- Some examples with a tree
  68 --
  69 -- department structure represented here is as follows:
  70 --
  71 -- ROOT-+->A-+->B-+->C
  72 --      |         |
  73 --      |         +->D-+->F
  74 --      +->E-+->G
  75
  76 CREATE TEMP TABLE department (
  77         id INTEGER PRIMARY KEY,  -- department ID
  78         parent_department INTEGER REFERENCES department, -- upper department ID
  79         name TEXT -- department name
  80 );
  81
  82 INSERT INTO department VALUES (0, NULL, 'ROOT');
  83 INSERT INTO department VALUES (1, 0, 'A');
  84 INSERT INTO department VALUES (2, 1, 'B');
  85 INSERT INTO department VALUES (3, 2, 'C');
  86 INSERT INTO department VALUES (4, 2, 'D');
  87 INSERT INTO department VALUES (5, 0, 'E');
  88 INSERT INTO department VALUES (6, 4, 'F');
  89 INSERT INTO department VALUES (7, 5, 'G');
  90
  91
  92 -- extract all departments under 'A'. Result should be A, B, C, D and F
  93 WITH RECURSIVE subdepartment AS
  94 (
  95         -- non recursive term
  96         SELECT name as root_name, * FROM department WHERE name = 'A'
  97
  98         UNION ALL
  99
 100         -- recursive term
 101         SELECT sd.root_name, d.* FROM department AS d, subdepartment AS sd
 102                 WHERE d.parent_department = sd.id
 103 )
 104 SELECT * FROM subdepartment ORDER BY name;
 105
 106 -- extract all departments under 'A' with "level" number
 107 WITH RECURSIVE subdepartment(level, id, parent_department, name) AS
 108 (
 109         -- non recursive term
 110         SELECT 1, * FROM department WHERE name = 'A'
 111
 112         UNION ALL
 113
 114         -- recursive term
 115         SELECT sd.level + 1, d.* FROM department AS d, subdepartment AS sd
 116                 WHERE d.parent_department = sd.id
 117 )
 118 SELECT * FROM subdepartment ORDER BY name;
 119
 120 -- extract all departments under 'A' with "level" number.
 121 -- Only shows level 2 or more
 122 WITH RECURSIVE subdepartment(level, id, parent_department, name) AS
 123 (
 124         -- non recursive term
 125         SELECT 1, * FROM department WHERE name = 'A'
 126
 127         UNION ALL
 128
 129         -- recursive term
 130         SELECT sd.level + 1, d.* FROM department AS d, subdepartment AS sd
 131                 WHERE d.parent_department = sd.id
 132 )
 133 SELECT * FROM subdepartment WHERE level >= 2 ORDER BY name;
 134
 135 -- "RECURSIVE" is ignored if the query has no self-reference
 136 WITH RECURSIVE subdepartment AS
 137 (
 138         -- note lack of recursive UNION structure
 139         SELECT * FROM department WHERE name = 'A'
 140 )
 141 SELECT * FROM subdepartment ORDER BY name;
 142
 143 -- inside subqueries
 144 SELECT count(*) FROM (
 145     WITH RECURSIVE t(n) AS (
 146         SELECT 1 UNION ALL SELECT n + 1 FROM t WHERE n < 500
 147     )
 148     SELECT * FROM t) AS t WHERE n < (
 149         SELECT count(*) FROM (
 150             WITH RECURSIVE t(n) AS (
 151                    SELECT 1 UNION ALL SELECT n + 1 FROM t WHERE n < 100
 152                 )
 153             SELECT * FROM t WHERE n < 50000
 154          ) AS t WHERE n < 100);
 155
 156 -- use same CTE twice at different subquery levels
 157 WITH q1(x,y) AS (
 158     SELECT hundred, sum(ten) FROM tenk1 GROUP BY hundred
 159   )
 160 SELECT count(*) FROM q1 WHERE y > (SELECT sum(y)/100 FROM q1 qsub);
 161
 162 -- via a VIEW
 163 CREATE TEMPORARY VIEW vsubdepartment AS
 164         WITH RECURSIVE subdepartment AS
 165         (
 166                  -- non recursive term
 167                 SELECT * FROM department WHERE name = 'A'
 168                 UNION ALL
 169                 -- recursive term
 170                 SELECT d.* FROM department AS d, subdepartment AS sd
 171                         WHERE d.parent_department = sd.id
 172         )
 173         SELECT * FROM subdepartment;
 174
 175 SELECT * FROM vsubdepartment ORDER BY name;
 176
 177 -- Check reverse listing
 178 SELECT pg_get_viewdef('vsubdepartment'::regclass);
 179 SELECT pg_get_viewdef('vsubdepartment'::regclass, true);
 180
 181 -- corner case in which sub-WITH gets initialized first
 182 with recursive q as (
 183       select * from department
 184     union all
 185       (with x as (select * from q)
 186        select * from x)
 187     )
 188 select * from q limit 24;
 189
 190 with recursive q as (
 191       select * from department
 192     union all
 193       (with recursive x as (
 194            select * from department
 195          union all
 196            (select * from q union all select * from x)
 197         )
 198        select * from x)
 199     )
 200 select * from q limit 32;
 201
 202 -- recursive term has sub-UNION
 203 WITH RECURSIVE t(i,j) AS (
 204         VALUES (1,2)
 205         UNION ALL
 206         SELECT t2.i, t.j+1 FROM
 207                 (SELECT 2 AS i UNION ALL SELECT 3 AS i) AS t2
 208                 JOIN t ON (t2.i = t.i+1))
 209
 210         SELECT * FROM t;
 211
 212 --
 213 -- different tree example
 214 --
 215 CREATE TEMPORARY TABLE tree(
 216     id INTEGER PRIMARY KEY,
 217     parent_id INTEGER REFERENCES tree(id)
 218 );
 219
 220 INSERT INTO tree
 221 VALUES (1, NULL), (2, 1), (3,1), (4,2), (5,2), (6,2), (7,3), (8,3),
 222        (9,4), (10,4), (11,7), (12,7), (13,7), (14, 9), (15,11), (16,11);
 223
 224 --
 225 -- get all paths from "second level" nodes to leaf nodes
 226 --
 227 WITH RECURSIVE t(id, path) AS (
 228     VALUES(1,ARRAY[]::integer[])
 229 UNION ALL
 230     SELECT tree.id, t.path || tree.id
 231     FROM tree JOIN t ON (tree.parent_id = t.id)
 232 )
 233 SELECT t1.*, t2.* FROM t AS t1 JOIN t AS t2 ON
 234         (t1.path[1] = t2.path[1] AND
 235         array_upper(t1.path,1) = 1 AND
 236         array_upper(t2.path,1) > 1)
 237         ORDER BY t1.id, t2.id;
 238
 239 -- just count 'em
 240 WITH RECURSIVE t(id, path) AS (
 241     VALUES(1,ARRAY[]::integer[])
 242 UNION ALL
 243     SELECT tree.id, t.path || tree.id
 244     FROM tree JOIN t ON (tree.parent_id = t.id)
 245 )
 246 SELECT t1.id, count(t2.*) FROM t AS t1 JOIN t AS t2 ON
 247         (t1.path[1] = t2.path[1] AND
 248         array_upper(t1.path,1) = 1 AND
 249         array_upper(t2.path,1) > 1)
 250         GROUP BY t1.id
 251         ORDER BY t1.id;
 252
 253 -- this variant tickled a whole-row-variable bug in 8.4devel
 254 WITH RECURSIVE t(id, path) AS (
 255     VALUES(1,ARRAY[]::integer[])
 256 UNION ALL
 257     SELECT tree.id, t.path || tree.id
 258     FROM tree JOIN t ON (tree.parent_id = t.id)
 259 )
 260 SELECT t1.id, t2.path, t2 FROM t AS t1 JOIN t AS t2 ON
 261 (t1.id=t2.id);
 262
 263 --
 264 -- test cycle detection
 265 --
 266 create temp table graph( f int, t int, label text );
 267
 268 insert into graph values
 269         (1, 2, 'arc 1 -> 2'),
 270         (1, 3, 'arc 1 -> 3'),
 271         (2, 3, 'arc 2 -> 3'),
 272         (1, 4, 'arc 1 -> 4'),
 273         (4, 5, 'arc 4 -> 5'),
 274         (5, 1, 'arc 5 -> 1');
 275
 276 with recursive search_graph(f, t, label, path, cycle) as (
 277         select *, array[row(g.f, g.t)], false from graph g
 278         union all
 279         select g.*, path || row(g.f, g.t), row(g.f, g.t) = any(path)
 280         from graph g, search_graph sg
 281         where g.f = sg.t and not cycle
 282 )
 283 select * from search_graph;
 284
 285 -- ordering by the path column has same effect as SEARCH DEPTH FIRST
 286 with recursive search_graph(f, t, label, path, cycle) as (
 287         select *, array[row(g.f, g.t)], false from graph g
 288         union all
 289         select g.*, path || row(g.f, g.t), row(g.f, g.t) = any(path)
 290         from graph g, search_graph sg
 291         where g.f = sg.t and not cycle
 292 )
 293 select * from search_graph order by path;
 294
 295 --
 296 -- test multiple WITH queries
 297 --
 298 WITH RECURSIVE
 299   y (id) AS (VALUES (1)),
 300   x (id) AS (SELECT * FROM y UNION ALL SELECT id+1 FROM x WHERE id < 5)
 301 SELECT * FROM x;
 302
 303 -- forward reference OK
 304 WITH RECURSIVE
 305     x(id) AS (SELECT * FROM y UNION ALL SELECT id+1 FROM x WHERE id < 5),
 306     y(id) AS (values (1))
 307  SELECT * FROM x;
 308
 309 WITH RECURSIVE
 310    x(id) AS
 311      (VALUES (1) UNION ALL SELECT id+1 FROM x WHERE id < 5),
 312    y(id) AS
 313      (VALUES (1) UNION ALL SELECT id+1 FROM y WHERE id < 10)
 314  SELECT y.*, x.* FROM y LEFT JOIN x USING (id);
 315
 316 WITH RECURSIVE
 317    x(id) AS
 318      (VALUES (1) UNION ALL SELECT id+1 FROM x WHERE id < 5),
 319    y(id) AS
 320      (VALUES (1) UNION ALL SELECT id+1 FROM x WHERE id < 10)
 321  SELECT y.*, x.* FROM y LEFT JOIN x USING (id);
 322
 323 WITH RECURSIVE
 324    x(id) AS
 325      (SELECT 1 UNION ALL SELECT id+1 FROM x WHERE id < 3 ),
 326    y(id) AS
 327      (SELECT * FROM x UNION ALL SELECT * FROM x),
 328    z(id) AS
 329      (SELECT * FROM x UNION ALL SELECT id+1 FROM z WHERE id < 10)
 330  SELECT * FROM z;
 331
 332 WITH RECURSIVE
 333    x(id) AS
 334      (SELECT 1 UNION ALL SELECT id+1 FROM x WHERE id < 3 ),
 335    y(id) AS
 336      (SELECT * FROM x UNION ALL SELECT * FROM x),
 337    z(id) AS
 338      (SELECT * FROM y UNION ALL SELECT id+1 FROM z WHERE id < 10)
 339  SELECT * FROM z;
 340
 341 --
 342 -- error cases
 343 --
 344
 345 -- INTERSECT
 346 WITH RECURSIVE x(n) AS (SELECT 1 INTERSECT SELECT n+1 FROM x)
 347         SELECT * FROM x;
 348
 349 WITH RECURSIVE x(n) AS (SELECT 1 INTERSECT ALL SELECT n+1 FROM x)
 350         SELECT * FROM x;
 351
 352 -- EXCEPT
 353 WITH RECURSIVE x(n) AS (SELECT 1 EXCEPT SELECT n+1 FROM x)
 354         SELECT * FROM x;
 355
 356 WITH RECURSIVE x(n) AS (SELECT 1 EXCEPT ALL SELECT n+1 FROM x)
 357         SELECT * FROM x;
 358
 359 -- no non-recursive term
 360 WITH RECURSIVE x(n) AS (SELECT n FROM x)
 361         SELECT * FROM x;
 362
 363 -- recursive term in the left hand side (strictly speaking, should allow this)
 364 WITH RECURSIVE x(n) AS (SELECT n FROM x UNION ALL SELECT 1)
 365         SELECT * FROM x;
 366
 367 CREATE TEMPORARY TABLE y (a INTEGER);
 368 INSERT INTO y SELECT generate_series(1, 10);
 369
 370 -- LEFT JOIN
 371
 372 WITH RECURSIVE x(n) AS (SELECT a FROM y WHERE a = 1
 373         UNION ALL
 374         SELECT x.n+1 FROM y LEFT JOIN x ON x.n = y.a WHERE n < 10)
 375 SELECT * FROM x;
 376
 377 -- RIGHT JOIN
 378 WITH RECURSIVE x(n) AS (SELECT a FROM y WHERE a = 1
 379         UNION ALL
 380         SELECT x.n+1 FROM x RIGHT JOIN y ON x.n = y.a WHERE n < 10)
 381 SELECT * FROM x;
 382
 383 -- FULL JOIN
 384 WITH RECURSIVE x(n) AS (SELECT a FROM y WHERE a = 1
 385         UNION ALL
 386         SELECT x.n+1 FROM x FULL JOIN y ON x.n = y.a WHERE n < 10)
 387 SELECT * FROM x;
 388
 389 -- subquery
 390 WITH RECURSIVE x(n) AS (SELECT 1 UNION ALL SELECT n+1 FROM x
 391                           WHERE n IN (SELECT * FROM x))
 392   SELECT * FROM x;
 393
 394 -- aggregate functions
 395 WITH RECURSIVE x(n) AS (SELECT 1 UNION ALL SELECT count(*) FROM x)
 396   SELECT * FROM x;
 397
 398 WITH RECURSIVE x(n) AS (SELECT 1 UNION ALL SELECT sum(n) FROM x)
 399   SELECT * FROM x;
 400
 401 -- ORDER BY
 402 WITH RECURSIVE x(n) AS (SELECT 1 UNION ALL SELECT n+1 FROM x ORDER BY 1)
 403   SELECT * FROM x;
 404
 405 -- LIMIT/OFFSET
 406 WITH RECURSIVE x(n) AS (SELECT 1 UNION ALL SELECT n+1 FROM x LIMIT 10 OFFSET 1)
 407   SELECT * FROM x;
 408
 409 -- FOR UPDATE
 410 WITH RECURSIVE x(n) AS (SELECT 1 UNION ALL SELECT n+1 FROM x FOR UPDATE)
 411   SELECT * FROM x;
 412
 413 -- target list has a recursive query name
 414 WITH RECURSIVE x(id) AS (values (1)
 415     UNION ALL
 416     SELECT (SELECT * FROM x) FROM x WHERE id < 5
 417 ) SELECT * FROM x;
 418
 419 -- mutual recursive query (not implemented)
 420 WITH RECURSIVE
 421   x (id) AS (SELECT 1 UNION ALL SELECT id+1 FROM y WHERE id < 5),
 422   y (id) AS (SELECT 1 UNION ALL SELECT id+1 FROM x WHERE id < 5)
 423 SELECT * FROM x;
 424
 425 -- non-linear recursion is not allowed
 426 WITH RECURSIVE foo(i) AS
 427     (values (1)
 428     UNION ALL
 429        (SELECT i+1 FROM foo WHERE i < 10
 430           UNION ALL
 431        SELECT i+1 FROM foo WHERE i < 5)
 432 ) SELECT * FROM foo;
 433
 434 WITH RECURSIVE foo(i) AS
 435     (values (1)
 436     UNION ALL
 437            SELECT * FROM
 438        (SELECT i+1 FROM foo WHERE i < 10
 439           UNION ALL
 440        SELECT i+1 FROM foo WHERE i < 5) AS t
 441 ) SELECT * FROM foo;
 442
 443 WITH RECURSIVE foo(i) AS
 444     (values (1)
 445     UNION ALL
 446        (SELECT i+1 FROM foo WHERE i < 10
 447           EXCEPT
 448        SELECT i+1 FROM foo WHERE i < 5)
 449 ) SELECT * FROM foo;
 450
 451 WITH RECURSIVE foo(i) AS
 452     (values (1)
 453     UNION ALL
 454        (SELECT i+1 FROM foo WHERE i < 10
 455           INTERSECT
 456        SELECT i+1 FROM foo WHERE i < 5)
 457 ) SELECT * FROM foo;
 458
 459 -- Wrong type induced from non-recursive term
 460 WITH RECURSIVE foo(i) AS
 461    (SELECT i FROM (VALUES(1),(2)) t(i)
 462    UNION ALL
 463    SELECT (i+1)::numeric(10,0) FROM foo WHERE i < 10)
 464 SELECT * FROM foo;
 465
 466 -- rejects different typmod, too (should we allow this?)
 467 WITH RECURSIVE foo(i) AS
 468    (SELECT i::numeric(3,0) FROM (VALUES(1),(2)) t(i)
 469    UNION ALL
 470    SELECT (i+1)::numeric(10,0) FROM foo WHERE i < 10)
 471 SELECT * FROM foo;
 472
 473 --
 474 -- test for bug #4902
 475 --
 476 with cte(foo) as ( values(42) ) values((select foo from cte));
 477 with cte(foo) as ( select 42 ) select * from ((select foo from cte)) q;
 478
 479 -- test CTE referencing an outer-level variable (to see that changed-parameter
 480 -- signaling still works properly after fixing this bug)
 481 select ( with cte(foo) as ( values(f1) )
 482          select (select foo from cte) )
 483 from int4_tbl;
 484
 485 select ( with cte(foo) as ( values(f1) )
 486           values((select foo from cte)) )
 487 from int4_tbl;
 488
 489 --
 490 -- test for nested-recursive-WITH bug
 491 --
 492 WITH RECURSIVE t(j) AS (
 493     WITH RECURSIVE s(i) AS (
 494         VALUES (1)
 495         UNION ALL
 496         SELECT i+1 FROM s WHERE i < 10
 497     )
 498     SELECT i FROM s
 499     UNION ALL
 500     SELECT j+1 FROM t WHERE j < 10
 501 )
 502 SELECT * FROM t;