Using a WITH inside of a RECURSIVE WITH in PostgreSQL

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 · 2022-11-29T03:49:07.763

17

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

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

sqlfiddle

See: