Re: Multiple SELECT statements Using One WITH statement

On Thu, Jan 20, 2022 at 7:42 AM Avi Weinberg <AviW(at)gilat(dot)com> wrote:

>
>
> Thanks David for the reply, but my question was a little different.
>
> I know I can have multiple CTE queries like you showed, but I want to
> have one single WITH query, and use it in multiple queries, not just by one
> query the directly proceed the CTE.
>
> Why do I need to execute the CTE query twice if I have two queries that
> wants to use it?
>
>
>
Sorry, that was a bit of a drive-by for me. I figured you could easily
test whether your proposed query structure would work and figured maybe you
didn't realize that CTEs could be chained together.

The short answer is that a query can only output a single result set so
having two top-level select statements is simply prohibited. And result
sets are not cached between statements so it isn't like there would be any
place to store intermediate CTE results automatically. As you've been
told, you can do that with temporary tables (it's a much bigger pain if you
want something that isn't session-local).

You can always write:

CREATE VIEW cte_view AS
WITH cte AS (...)
SELECT * FROM cte;

And then incorporate that into any queries that require the results of said
CTE.

David J.