From: | Magnus Hagander <magnus(at)hagander(dot)net> |
---|---|
To: | PostgreSQL-development <pgsql-hackers(at)postgresql(dot)org> |
Subject: | Combine non-recursive and recursive CTEs? |
Date: | 2012-06-16 06:27:07 |
Message-ID: | CABUevEz9D+CqJ_rgbqqfxCPW75yJuSunPnGmxbmVEUhG2woqVQ@mail.gmail.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-hackers |
I'm not sure if this is something I don't know how to do, or if it's
something we simply can't do, or if it's something we could do but the
syntax can't handle :-)
Basically, I'd like to combine a recursive and a non-recursive CTE in
the same query. If I do it non-recursive, I can do something like:
WITH t1(z) AS (
SELECT a FROM x
),
t2 AS (
SELECT z FROM t1
)
SELECT * FROM t2;
But what if I want t2 to be recursive?
Trying something like:
WITH t1 (z,b) AS (
SELECT a,b FROM x
),
RECURSIVE t2(z,b) AS (
SELECT z,b FROM t1 WHERE b IS NULL
UNION ALL
SELECT z,b FROM t2 INNER JOIN t1 ON t2.b=t1.z
)
I get a syntax error on the RECURSIVE.
Is there any other position in this query that I can put the RECURSIVE
in order for it to get through?
--
Magnus Hagander
Me: http://www.hagander.net/
Work: http://www.redpill-linpro.com/
From | Date | Subject | |
---|---|---|---|
Next Message | Tom Lane | 2012-06-16 06:41:00 | Re: Allow WAL information to recover corrupted pg_controldata |
Previous Message | Magnus Hagander | 2012-06-16 06:18:42 | Re: libpq compression |