Re: Multiple SELECT statements Using One WITH statement

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.