Обсуждение: Multiple SELECT statements Using One WITH statement

Hi,

 

Can I have multiple select statements using one WITH statement?

 

WITH t AS (

    Select A, B from …

)

SELECT A into tableA FROM t where ….;

 

SELECT B into tableB FROM t where ….;

čt 20. 1. 2022 v 13:48 odesílatel Avi Weinberg <AviW@gilat.com> napsal:
>
> Hi,
Hello!
>
>
> Can I have multiple select statements using one WITH statement?
>
>
>
> WITH t AS (
>
>     Select A, B from …
>
> )
>
> SELECT A into tableA FROM t where ….;
>
>
>
> SELECT B into tableB FROM t where ….;
>

I think it is not possible that way. Instead you can "cache" the query
result into a temporary table. CREATE TEMPORARY TABLE  ... ON COMMIT
DROP could be useful here. There is simple example mentioned in this
(https://stackoverflow.com/a/52384424/319233) stackoverflow answer.
>
> IMPORTANT - This email and any attachments is intended for the above named addressee(s), and may contain information
whichis confidential or privileged. If you are not the intended recipient, please inform the sender immediately and
deletethis email: you should not copy or use this e-mail for any purpose nor disclose its contents to any person. 

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.