Re: Multiple SELECT statements Using One WITH statement - Mailing list pgsql-general

On 20/01/2022 15.42, Avi Weinberg 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?

When data is going to be provided to an application via a REST API, I 
find this pattern quite useful:

WITH list AS (
         SELECT generate_series(1,10) AS n
)
SELECT json_build_object(
         'even', (
                 SELECT json_agg(n)
                 FROM (
                         SELECT n
                         FROM list
                         WHERE n%2 = 0
                 ) even
         ),
         'odd', (
                 SELECT json_agg(n)
                 FROM (
                         SELECT n
                         FROM list
                         WHERE n%2 = 1
                 ) odd
         )
) obj;


If data is to be written to separate tables, writing the intermediate 
result to a temporary table as explained by Josef might be the simplest 
solution.