Re: Combine non-recursive and recursive CTEs?

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/

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

Magnus Hagander <magnus(at)hagander(dot)net> writes:
> Basically, I'd like to combine a recursive and a non-recursive CTE in
> the same query.

Just mark them all as recursive. There's no harm in marking a CTE as
recursive when it isn't really.

> 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.

The SQL spec says RECURSIVE can only appear immediately after WITH,
so it necessarily applies to all the CTEs in the WITH list.

The reason why it's like that is that RECURSIVE affects the visibility
rules for which CTEs can refer to which other ones. I think the SQL
committee would have done better to keep the two concepts separate,
but they didn't ...

regards, tom lane

On Sat, Jun 16, 2012 at 2:52 PM, Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> wrote:
> Magnus Hagander <magnus(at)hagander(dot)net> writes:
>> Basically, I'd like to combine a recursive and a non-recursive CTE in
>> the same query.
>
> Just mark them all as recursive.  There's no harm in marking a CTE as
> recursive when it isn't really.

Hah. I could've sworn I tried that and got the typical error of "you
need to use the union construct for recursive queries". But clearly I
must've typoed something in that one, because when I did that over
again, it now worked perfectly...

Thanks!

--
 Magnus Hagander
 Me: http://www.hagander.net/
 Work: http://www.redpill-linpro.com/