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'd be an infinite loop, but outside query reads only as much as needed
  35 WITH RECURSIVE t(n) AS (
  36     VALUES (1)
  37 UNION ALL
  38     SELECT n+1 FROM t)
  39 SELECT * FROM t LIMIT 10;
  40
  41 --
  42 -- Some examples with a tree
  43 --
  44 -- department structure represented here is as follows:
  45 --
  46 -- ROOT-+->A-+->B-+->C
  47 --      |         |
  48 --      |         +->D-+->F
  49 --      +->E-+->G
  50
  51 CREATE TEMP TABLE department (
  52         id INTEGER PRIMARY KEY,  -- department ID
  53         parent_department INTEGER REFERENCES department, -- upper department ID
  54         name TEXT -- department name
  55 );
  56
  57 INSERT INTO department VALUES (0, NULL, 'ROOT');
  58 INSERT INTO department VALUES (1, 0, 'A');
  59 INSERT INTO department VALUES (2, 1, 'B');
  60 INSERT INTO department VALUES (3, 2, 'C');
  61 INSERT INTO department VALUES (4, 2, 'D');
  62 INSERT INTO department VALUES (5, 0, 'E');
  63 INSERT INTO department VALUES (6, 4, 'F');
  64 INSERT INTO department VALUES (7, 5, 'G');
  65
  66
  67 -- extract all departments under 'A'. Result should be A, B, C, D and F
  68 WITH RECURSIVE subdepartment AS
  69 (
  70         -- non recursive term
  71         SELECT * FROM department WHERE name = 'A'
  72
  73         UNION ALL
  74
  75         -- recursive term
  76         SELECT d.* FROM department AS d, subdepartment AS sd
  77                 WHERE d.parent_department = sd.id
  78 )
  79 SELECT * FROM subdepartment ORDER BY name;
  80
  81 -- extract all departments under 'A' with "level" number
  82 WITH RECURSIVE subdepartment(level, id, parent_department, name) AS
  83 (
  84         -- non recursive term
  85         SELECT 1, * FROM department WHERE name = 'A'
  86
  87         UNION ALL
  88
  89         -- recursive term
  90         SELECT sd.level + 1, d.* FROM department AS d, subdepartment AS sd
  91                 WHERE d.parent_department = sd.id
  92 )
  93 SELECT * FROM subdepartment ORDER BY name;
  94
  95 -- extract all departments under 'A' with "level" number.
  96 -- Only shows level 2 or more
  97 WITH RECURSIVE subdepartment(level, id, parent_department, name) AS
  98 (
  99         -- non recursive term
 100         SELECT 1, * FROM department WHERE name = 'A'
 101
 102         UNION ALL
 103
 104         -- recursive term
 105         SELECT sd.level + 1, d.* FROM department AS d, subdepartment AS sd
 106                 WHERE d.parent_department = sd.id
 107 )
 108 SELECT * FROM subdepartment WHERE level >= 2 ORDER BY name;
 109
 110 -- "RECURSIVE" is ignored if the query has no self-reference
 111 WITH RECURSIVE subdepartment AS
 112 (
 113         -- note lack of recursive UNION structure
 114         SELECT * FROM department WHERE name = 'A'
 115 )
 116 SELECT * FROM subdepartment ORDER BY name;
 117
 118 -- inside subqueries
 119 SELECT count(*) FROM (
 120     WITH RECURSIVE t(n) AS (
 121         SELECT 1 UNION ALL SELECT n + 1 FROM t WHERE n < 500
 122     )
 123     SELECT * FROM t) AS t WHERE n < (
 124         SELECT count(*) FROM (
 125             WITH RECURSIVE t(n) AS (
 126                    SELECT 1 UNION ALL SELECT n + 1 FROM t WHERE n < 100
 127                 )
 128             SELECT * FROM t WHERE n < 50000
 129          ) AS t WHERE n < 100);
 130
 131 -- use same CTE twice at different subquery levels
 132 WITH q1(x,y) AS (
 133     SELECT hundred, sum(ten) FROM tenk1 GROUP BY hundred
 134   )
 135 SELECT count(*) FROM q1 WHERE y > (SELECT sum(y)/100 FROM q1 qsub);
 136
 137 -- via a VIEW
 138 CREATE TEMPORARY VIEW vsubdepartment AS
 139         WITH RECURSIVE subdepartment AS
 140         (
 141                  -- non recursive term
 142                 SELECT * FROM department WHERE name = 'A'
 143                 UNION ALL
 144                 -- recursive term
 145                 SELECT d.* FROM department AS d, subdepartment AS sd
 146                         WHERE d.parent_department = sd.id
 147         )
 148         SELECT * FROM subdepartment;
 149
 150 SELECT * FROM vsubdepartment ORDER BY name;
 151
 152 -- Check reverse listing
 153 SELECT pg_get_viewdef('vsubdepartment'::regclass);
 154 SELECT pg_get_viewdef('vsubdepartment'::regclass, true);
 155
 156 -- recursive term has sub-UNION
 157 WITH RECURSIVE t(i,j) AS (
 158         VALUES (1,2)
 159         UNION ALL
 160         SELECT t2.i, t.j+1 FROM
 161                 (SELECT 2 AS i UNION ALL SELECT 3 AS i) AS t2
 162                 JOIN t ON (t2.i = t.i+1))
 163
 164         SELECT * FROM t;
 165
 166 --
 167 -- different tree example
 168 --
 169 CREATE TEMPORARY TABLE tree(
 170     id INTEGER PRIMARY KEY,
 171     parent_id INTEGER REFERENCES tree(id)
 172 );
 173
 174 INSERT INTO tree
 175 VALUES (1, NULL), (2, 1), (3,1), (4,2), (5,2), (6,2), (7,3), (8,3),
 176        (9,4), (10,4), (11,7), (12,7), (13,7), (14, 9), (15,11), (16,11);
 177
 178 --
 179 -- get all paths from "second level" nodes to leaf nodes
 180 --
 181 WITH RECURSIVE t(id, path) AS (
 182     VALUES(1,ARRAY[]::integer[])
 183 UNION ALL
 184     SELECT tree.id, t.path || tree.id
 185     FROM tree JOIN t ON (tree.parent_id = t.id)
 186 )
 187 SELECT t1.*, t2.* FROM t AS t1 JOIN t AS t2 ON
 188         (t1.path[1] = t2.path[1] AND
 189         array_upper(t1.path,1) = 1 AND
 190         array_upper(t2.path,1) > 1)
 191         ORDER BY t1.id, t2.id;
 192
 193 -- just count 'em
 194 WITH RECURSIVE t(id, path) AS (
 195     VALUES(1,ARRAY[]::integer[])
 196 UNION ALL
 197     SELECT tree.id, t.path || tree.id
 198     FROM tree JOIN t ON (tree.parent_id = t.id)
 199 )
 200 SELECT t1.id, count(t2.*) FROM t AS t1 JOIN t AS t2 ON
 201         (t1.path[1] = t2.path[1] AND
 202         array_upper(t1.path,1) = 1 AND
 203         array_upper(t2.path,1) > 1)
 204         GROUP BY t1.id
 205         ORDER BY t1.id;
 206
 207 --
 208 -- test multiple WITH queries
 209 --
 210 WITH RECURSIVE
 211   y (id) AS (VALUES (1)),
 212   x (id) AS (SELECT * FROM y UNION ALL SELECT id+1 FROM x WHERE id < 5)
 213 SELECT * FROM x;
 214
 215 -- forward reference OK
 216 WITH RECURSIVE
 217     x(id) AS (SELECT * FROM y UNION ALL SELECT id+1 FROM x WHERE id < 5),
 218     y(id) AS (values (1))
 219  SELECT * FROM x;
 220
 221 WITH RECURSIVE
 222    x(id) AS
 223      (VALUES (1) UNION ALL SELECT id+1 FROM x WHERE id < 5),
 224    y(id) AS
 225      (VALUES (1) UNION ALL SELECT id+1 FROM y WHERE id < 10)
 226  SELECT y.*, x.* FROM y LEFT JOIN x USING (id);
 227
 228 WITH RECURSIVE
 229    x(id) AS
 230      (VALUES (1) UNION ALL SELECT id+1 FROM x WHERE id < 5),
 231    y(id) AS
 232      (VALUES (1) UNION ALL SELECT id+1 FROM x WHERE id < 10)
 233  SELECT y.*, x.* FROM y LEFT JOIN x USING (id);
 234
 235 WITH RECURSIVE
 236    x(id) AS
 237      (SELECT 1 UNION ALL SELECT id+1 FROM x WHERE id < 3 ),
 238    y(id) AS
 239      (SELECT * FROM x UNION ALL SELECT * FROM x),
 240    z(id) AS
 241      (SELECT * FROM x UNION ALL SELECT id+1 FROM z WHERE id < 10)
 242  SELECT * FROM z;
 243
 244 WITH RECURSIVE
 245    x(id) AS
 246      (SELECT 1 UNION ALL SELECT id+1 FROM x WHERE id < 3 ),
 247    y(id) AS
 248      (SELECT * FROM x UNION ALL SELECT * FROM x),
 249    z(id) AS
 250      (SELECT * FROM y UNION ALL SELECT id+1 FROM z WHERE id < 10)
 251  SELECT * FROM z;
 252
 253 --
 254 -- error cases
 255 --
 256
 257 -- UNION (should be supported someday)
 258 WITH RECURSIVE x(n) AS (SELECT 1 UNION SELECT n+1 FROM x)
 259         SELECT * FROM x;
 260
 261 -- INTERSECT
 262 WITH RECURSIVE x(n) AS (SELECT 1 INTERSECT SELECT n+1 FROM x)
 263         SELECT * FROM x;
 264
 265 WITH RECURSIVE x(n) AS (SELECT 1 INTERSECT ALL SELECT n+1 FROM x)
 266         SELECT * FROM x;
 267
 268 -- EXCEPT
 269 WITH RECURSIVE x(n) AS (SELECT 1 EXCEPT SELECT n+1 FROM x)
 270         SELECT * FROM x;
 271
 272 WITH RECURSIVE x(n) AS (SELECT 1 EXCEPT ALL SELECT n+1 FROM x)
 273         SELECT * FROM x;
 274
 275 -- no non-recursive term
 276 WITH RECURSIVE x(n) AS (SELECT n FROM x)
 277         SELECT * FROM x;
 278
 279 -- recursive term in the left hand side (strictly speaking, should allow this)
 280 WITH RECURSIVE x(n) AS (SELECT n FROM x UNION ALL SELECT 1)
 281         SELECT * FROM x;
 282
 283 CREATE TEMPORARY TABLE y (a INTEGER);
 284 INSERT INTO y SELECT generate_series(1, 10);
 285
 286 -- LEFT JOIN
 287
 288 WITH RECURSIVE x(n) AS (SELECT a FROM y WHERE a = 1
 289         UNION ALL
 290         SELECT x.n+1 FROM y LEFT JOIN x ON x.n = y.a WHERE n < 10)
 291 SELECT * FROM x;
 292
 293 -- RIGHT JOIN
 294 WITH RECURSIVE x(n) AS (SELECT a FROM y WHERE a = 1
 295         UNION ALL
 296         SELECT x.n+1 FROM x RIGHT JOIN y ON x.n = y.a WHERE n < 10)
 297 SELECT * FROM x;
 298
 299 -- FULL JOIN
 300 WITH RECURSIVE x(n) AS (SELECT a FROM y WHERE a = 1
 301         UNION ALL
 302         SELECT x.n+1 FROM x FULL JOIN y ON x.n = y.a WHERE n < 10)
 303 SELECT * FROM x;
 304
 305 -- subquery
 306 WITH RECURSIVE x(n) AS (SELECT 1 UNION ALL SELECT n+1 FROM x
 307                           WHERE n IN (SELECT * FROM x))
 308   SELECT * FROM x;
 309
 310 -- aggregate functions
 311 WITH RECURSIVE x(n) AS (SELECT 1 UNION ALL SELECT count(*) FROM x)
 312   SELECT * FROM x;
 313
 314 WITH RECURSIVE x(n) AS (SELECT 1 UNION ALL SELECT sum(n) FROM x)
 315   SELECT * FROM x;
 316
 317 -- ORDER BY
 318 WITH RECURSIVE x(n) AS (SELECT 1 UNION ALL SELECT n+1 FROM x ORDER BY 1)
 319   SELECT * FROM x;
 320
 321 -- LIMIT/OFFSET
 322 WITH RECURSIVE x(n) AS (SELECT 1 UNION ALL SELECT n+1 FROM x LIMIT 10 OFFSET 1)
 323   SELECT * FROM x;
 324
 325 -- FOR UPDATE
 326 WITH RECURSIVE x(n) AS (SELECT 1 UNION ALL SELECT n+1 FROM x FOR UPDATE)
 327   SELECT * FROM x;
 328
 329 -- target list has a recursive query name
 330 WITH RECURSIVE x(id) AS (values (1)
 331     UNION ALL
 332     SELECT (SELECT * FROM x) FROM x WHERE id < 5
 333 ) SELECT * FROM x;
 334
 335 -- mutual recursive query (not implemented)
 336 WITH RECURSIVE
 337   x (id) AS (SELECT 1 UNION ALL SELECT id+1 FROM y WHERE id < 5),
 338   y (id) AS (SELECT 1 UNION ALL SELECT id+1 FROM x WHERE id < 5)
 339 SELECT * FROM x;
 340
 341 -- non-linear recursion is not allowed
 342 WITH RECURSIVE foo(i) AS
 343     (values (1)
 344     UNION ALL
 345        (SELECT i+1 FROM foo WHERE i < 10
 346           UNION ALL
 347        SELECT i+1 FROM foo WHERE i < 5)
 348 ) SELECT * FROM foo;
 349
 350 WITH RECURSIVE foo(i) AS
 351     (values (1)
 352     UNION ALL
 353            SELECT * FROM
 354        (SELECT i+1 FROM foo WHERE i < 10
 355           UNION ALL
 356        SELECT i+1 FROM foo WHERE i < 5) AS t
 357 ) SELECT * FROM foo;
 358
 359 WITH RECURSIVE foo(i) AS
 360     (values (1)
 361     UNION ALL
 362        (SELECT i+1 FROM foo WHERE i < 10
 363           EXCEPT
 364        SELECT i+1 FROM foo WHERE i < 5)
 365 ) SELECT * FROM foo;
 366
 367 WITH RECURSIVE foo(i) AS
 368     (values (1)
 369     UNION ALL
 370        (SELECT i+1 FROM foo WHERE i < 10
 371           INTERSECT
 372        SELECT i+1 FROM foo WHERE i < 5)
 373 ) SELECT * FROM foo;
 374
 375 -- Wrong type induced from non-recursive term
 376 WITH RECURSIVE foo(i) AS
 377    (SELECT i FROM (VALUES(1),(2)) t(i)
 378    UNION ALL
 379    SELECT (i+1)::numeric(10,0) FROM foo WHERE i < 10)
 380 SELECT * FROM foo;
 381
 382 -- rejects different typmod, too (should we allow this?)
 383 WITH RECURSIVE foo(i) AS
 384    (SELECT i::numeric(3,0) FROM (VALUES(1),(2)) t(i)
 385    UNION ALL
 386    SELECT (i+1)::numeric(10,0) FROM foo WHERE i < 10)
 387 SELECT * FROM foo;