Combine non-recursive and recursive CTEs?

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/

Responses

Browse pgsql-hackers by date

  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