Re: Multiple SELECT statements Using One WITH statement

[Johannes Graën <johannes@xxxxxxxxxx>]

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.