Re: [HACKERS] 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@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers hm, this is interesting ... cat /tmp/a.sql WITHy 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 -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Combine non-recursive and recursive CTEs?
Magnus Hagander mag...@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 -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Combine non-recursive and recursive CTEs?
On Sat, Jun 16, 2012 at 2:52 PM, Tom Lane t...@sss.pgh.pa.us wrote: Magnus Hagander mag...@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/ -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
