Thread

1. Combine non-recursive and recursive CTEs?

   Magnus Hagander <magnus@hagander.net> — 2012-06-16T06:27:07Z

   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/
   
   

2. Re: Combine non-recursive and recursive CTEs?

   Hans-Jürgen Schönig <postgres@cybertec.at> — 2012-06-16T06:42:32Z

   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
   
   
   

3. Re: Combine non-recursive and recursive CTEs?

   Tom Lane <tgl@sss.pgh.pa.us> — 2012-06-16T06:52:16Z

   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
   
   

4. Re: Combine non-recursive and recursive CTEs?

   Magnus Hagander <magnus@hagander.net> — 2012-06-16T08:57:11Z

   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/