On Thu, Jan 20, 2022 at 7:42 AM Avi Weinberg <AviW@gilat.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.