Search Postgresql Archives

Re: Multiple SELECT statements Using One WITH statement

[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]

 



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


[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]
[Index of Archives]     [Postgresql Jobs]     [Postgresql Admin]     [Postgresql Performance]     [Linux Clusters]     [PHP Home]     [PHP on Windows]     [Kernel Newbies]     [PHP Classes]     [PHP Databases]     [Postgresql & PHP]     [Yosemite]

  Powered by Linux