Re: Combine non-recursive and recursive CTEs?

On Jun 16, 2012, at 8:27 AM, Magnus Hagander wrote:

> 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/
>
> --
> Sent via pgsql-hackers mailing list (pgsql-hackers(at)postgresql(dot)org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-hackers
>

hm, this is interesting ...

cat /tmp/a.sql
WITH y AS ( SELECT 1 AS n),
g AS (WITH RECURSIVE x(n) AS
(
SELECT (SELECT n FROM y) AS n
UNION ALL
SELECT n + 1 AS n
FROM x
WHERE n < 10))
SELECT * FROM g;

Hans-Jurgen-Scbonigs-MacBook-Pro:sql hs$ psql test < /tmp/a.sql
ERROR: syntax error at or near ")"
LINE 8: WHERE n < 10))

this gives a syntax error as well ...
if my early morning brain is correct this should be a proper statement ...

regards,

hans

--
Cybertec Schönig & Schönig GmbH
Gröhrmühlgasse 26
A-2700 Wiener Neustadt, Austria
Web: http://www.postgresql-support.de