Re: Combine non-recursive and recursive CTEs?

Magnus Hagander <magnus(at)hagander(dot)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