Обсуждение: 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 NULLUNION 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@postgresql.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 nUNION ALLSELECT n + 1 AS nFROM xWHERE 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@hagander.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@sss.pgh.pa.us> wrote: > Magnus Hagander <magnus@hagander.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/