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