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?
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.
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With