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.
