Requesting subselectWith_ combinator

[enobayram]

As far as I can tell, currently the only way to write recursive CTEs with beam is to use the selectWith combinator, which can only happen at the "top-level". However, (at least) Postgres supports recursive CTEs inside subqueries as well:

SELECT * 
FROM (
  WITH RECURSIVE my_cte AS (
    SELECT 1 
    UNION ALL 
    SELECT 1 WHERE FALSE
  ) 
  SELECT * 
  FROM my_cte
) AS t;

 ?column? 
----------
        1
(1 row)

Would it be possible to write a subselectWith_ primitive, analogous to subquery_ and selectWith_ that allows CTEs inside subselects like this (without even exposing the With wrapper outside the subselect)?

[enobayram]

Seems relevant to mention here that I've given up on type safety and managed to work around this using a combination of pgUnnest and customExpr_ in this commit. Looking back at my workaround, I don't think even a subselectWith_ would've solved my problem in combination with lateral_, because the QExpr be (QNested s) I get inside the lateral_ query wouldn't be usable inside the subselectWith_, which would expect a Q be db (QNested (QNested s)), so I'd actually need something like a lateralWith_. A tangent for this GitHub issue, but If I'm not mistaken, I should get a special QExpr be s inside the lateral_ that I can lift into as many layers of QNested as I want in order to fully capture the expressiveness of Postgres' LATERAL joins. If beam's lateral_ behaved that way, a new subselectWith_ requested in this ticket would've allowed me to express the query from my workaround entirely in beam.

[tathougies]

I know it's very late, but one part of your requested functionality is in the issue mentioned above. As for the renesting within lateral_, let me think about that one. It's a bit hard to express using our scoping mechanism.

[Ericson2314]

Excited this is in progress!