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