[Sqlalchemy-tickets] Issue #4123: Feature Request: Nested CTEs (zzzeek/sqlalchemy)

[rdunklau <iss...@bi...>]

New issue 4123: Feature Request: Nested CTEs
https://bitbucket.org/zzzeek/sqlalchemy/issues/4123/feature-request-nested-ctes

rdunklau:

Hello.

Sorry if this is not a proper feature request, and should have gone to the mailing list first.

It seems like it is impossible to generate nested CTEs since CTEs are only rendered at the top level (when the compiler stack is empty).

The goal would be to generate SQL looking like this:


```
#!sql
WITH t AS (
   WITH t2 AS (
     SELECT 1
   )
   SELECT * FROM t2
)
SELECT * from t
```


The real use case is a bit more complicated: the goal would be to be able to reference a recursive CTE more than once in the recursive term (which is disallowed, at least in PostgreSQL), by materializing it in its own CTE on every recursion, something akin to:

```
#!sql
WITH recursive cte AS (
   SELECT initial_term
   UNION ALL
   (
   WITH mat_previous_result AS (
     SELECT * FROM cte WHERE <recursion_stop_condition>
   )
   SELECT something FROM mat_previous_result
   UNION ALL
   SELECT anotherthing FROM mat_previous_result
  )
)
SELECT * FROM cte WHERE <recursion_stop_condition>
```