Support WITH clause also in subqueries like PostgreSQL

[beikov]

PostgreSQL supports a with clause in subqueries, but CockroachDB doesn't. Would be nice to add support for that. Here a sample query:

select (
    with recursive a as (
        select 1 v
        union all
        select r.v + 1
        from a r
        where r.v < 10
    )
    select count(*) from a
)

This is just a toy example, but the nice thing about this feature, is that we can do "lateral" recursive queries i.e. a recursive query per outer row.

Jira issue: CRDB-42337

[blathers-crl[bot]]

Hello, I am Blathers. I am here to help you get the issue triaged.

It looks like you have not filled out the issue in the format of any of our templates. To best assist you, we advise you to use one of these templates.

I was unable to automatically find someone to ping.

If we have not gotten back to your issue within a few business days, you can try the following:

• Join our community slack channel and ask on #cockroachdb.
• Try find someone from here if you know they worked closely on the area and CC them.

_{:owl: Hoot! I am a Blathers, a bot for CockroachDB. My owner is dev-inf.}