Using a WITH inside of a RECURSIVE WITH in PostgreSQL [duplicate]

Question

In PostgreSQL, a WITH can be used by another WITH, for example:

WITH num AS (
    VALUES (50)
), num2 AS (
    SELECT column1 * 2 AS value FROM num
)
SELECT value FROM num2;

And then there are RECURSIVE WITHs, which can be done like so:

WITH RECURSIVE t(n) AS (
    VALUES (1)
  UNION ALL
    SELECT n+1 FROM t WHERE n < 100
)
SELECT sum(n) FROM t;

But so far, I have not found a way for a RECURSIVE WITH to use a previous WITH. I would think that it should be something like this:

WITH num AS (
    VALUES (50)
), num2 AS (
    SELECT column1 * 2 AS value FROM num
), RECURSIVE  t(n) AS (
        VALUES (1)
    UNION ALL
        SELECT n+1 FROM t WHERE n < (SELECT * FROM num2)
)
SELECT sum(n) FROM t;

But this does not work. So is there a way to do this? If so, how?

Erwin Brandstetter · Accepted Answer · 2024-02-27 10:50:48Z

18

Start with WITH RECURSIVE. You can still add non-recursive CTEs:

WITH RECURSIVE
   num (val)  AS (VALUES (50))
,  num2 (val) AS (SELECT val * 2 AS value FROM num)
,  t (n) AS (
      VALUES (1)
      UNION ALL
      SELECT n+1 FROM t WHERE n < (SELECT * FROM num2)
   )
SELECT sum(n) FROM t;

fiddle
_{Old sqlfiddle}

See:

How to use multiple CTEs in a single SQL query?

edited Feb 27, 2024 at 10:50

answered Feb 18, 2014 at 6:18

Erwin Brandstetter

668k159 gold badges1.2k silver badges1.3k bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

mmey Over a year ago

Nice! I always thought the order would matter, but doesn't seem to matter that much after all...

Collectives™ on Stack Overflow

Using a WITH inside of a RECURSIVE WITH in PostgreSQL [duplicate]

1 Answer 1

1 Comment

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Linked

Related