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?

score 14 · Accepted Answer · answered Feb 18 '14 at 06:18

Start with WITH RECURSIVE. You can still squeeze in "regular" CTEs before the recursive part:

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 * FROM num2)
   )
SELECT sum(n) FROM t;

-> SQLfiddle demo.

Using a WITH inside of a RECURSIVE WITH in PostgreSQL

1 Answers1