Horton
Menu

Thread

  1. Multiple SELECT statements Using One WITH statement

    Avi Weinberg <aviw@gilat.com> — 2022-01-20T12:48:39Z

    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 ....;

IMPORTANT - This email and any attachments is intended for the above named addressee(s), and may contain information which is confidential or privileged. If you are not the intended recipient, please inform the sender immediately and delete this email: you should not copy or use this e-mail for any purpose nor disclose its contents to any person.

  2. Re: Multiple SELECT statements Using One WITH statement

    David G. Johnston <david.g.johnston@gmail.com> — 2022-01-20T12:58:24Z

    On Thursday, January 20, 2022, Avi Weinberg <AviW@gilat.com> wrote:

> 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 ….;
>
>
> With q1 as (), q2 as (), q3 as () main_query

David J.

  3. RE: Multiple SELECT statements Using One WITH statement

    Avi Weinberg <aviw@gilat.com> — 2022-01-20T14:42:13Z

    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?

Thanks

From: David G. Johnston [mailto:david.g.johnston@gmail.com]
Sent: Thursday, January 20, 2022 2:58 PM
To: Avi Weinberg <AviW@gilat.com>
Cc: pgsql-general@lists.postgresql.org
Subject: Re: Multiple SELECT statements Using One WITH statement



On Thursday, January 20, 2022, Avi Weinberg <AviW@gilat.com<mailto:AviW@gilat.com>> wrote:
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 ….;

With q1 as (), q2 as (), q3 as () main_query

David J.

IMPORTANT - This email and any attachments is intended for the above named addressee(s), and may contain information which is confidential or privileged. If you are not the intended recipient, please inform the sender immediately and delete this email: you should not copy or use this e-mail for any purpose nor disclose its contents to any person.

  4. Re: Multiple SELECT statements Using One WITH statement

    Josef Šimánek <josef.simanek@gmail.com> — 2022-01-20T15:13:31Z

    č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 which is confidential or privileged. If you are not the intended recipient, please inform the sender immediately and delete this email: you should not copy or use this e-mail for any purpose nor disclose its contents to any person.

  5. Re: Multiple SELECT statements Using One WITH statement

    Johannes Graën <johannes@selfnet.de> — 2022-01-20T15:28:29Z

    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.

  6. Re: Multiple SELECT statements Using One WITH statement

    David G. Johnston <david.g.johnston@gmail.com> — 2022-01-20T16:45:49Z

    On Thu, Jan 20, 2022 at 7:42 AM Avi Weinberg <AviW@gilat.com> 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?
>
>
>
Sorry, that was a bit of a drive-by for me.  I figured you could easily
test whether your proposed query structure would work and figured maybe you
didn't realize that CTEs could be chained together.

The short answer is that a query can only output a single result set so
having two top-level select statements is simply prohibited.  And result
sets are not cached between statements so it isn't like there would be any
place to store intermediate CTE results automatically.  As you've been
told, you can do that with temporary tables (it's a much bigger pain if you
want something that isn't session-local).

You can always write:

CREATE VIEW cte_view AS
WITH cte AS (...)
SELECT * FROM cte;

And then incorporate that into any queries that require the results of said
CTE.

David J.

  7. Re: Multiple SELECT statements Using One WITH statement

    Ken Tanzer <ken.tanzer@gmail.com> — 2022-01-20T23:32:10Z

    On Thu, Jan 20, 2022 at 8:46 AM David G. Johnston <
david.g.johnston@gmail.com> wrote:

> You can always write:
>
> CREATE VIEW cte_view AS
> WITH cte AS (...)
> SELECT * FROM cte;
>
> And then incorporate that into any queries that require the results of
> said CTE.
>
>
Is there any advantage to still using a CTE inside the view definition, and
then selecting it?  Instead of just

CREATE VIEW cte_view AS
(...);

Just curious, in case I'm missing something!

Cheers,
Ken


-- 
AGENCY Software
A Free Software data system
By and for non-profits
*http://agency-software.org/ <http://agency-software.org/>*
*https://demo.agency-software.org/client
<https://demo.agency-software.org/client>*
ken.tanzer@agency-software.org
(253) 245-3801

Subscribe to the mailing list
<agency-general-request@lists.sourceforge.net?body=subscribe> to
learn more about AGENCY or
follow the discussion.

  8. Re: Multiple SELECT statements Using One WITH statement

    David G. Johnston <david.g.johnston@gmail.com> — 2022-01-20T23:34:15Z

    On Thu, Jan 20, 2022 at 4:32 PM Ken Tanzer <ken.tanzer@gmail.com> wrote:

>
>
> On Thu, Jan 20, 2022 at 8:46 AM David G. Johnston <
> david.g.johnston@gmail.com> wrote:
>
>> You can always write:
>>
>> CREATE VIEW cte_view AS
>> WITH cte AS (...)
>> SELECT * FROM cte;
>>
>> And then incorporate that into any queries that require the results of
>> said CTE.
>>
>>
> Is there any advantage to still using a CTE inside the view
> definition, and then selecting it?  Instead of just
>
> CREATE VIEW cte_view AS
> (...);
>
> Just curious, in case I'm missing something!
>
>
No. That would be tunnel vision and early morning posting on my part.

David J.

  9. Re: Multiple SELECT statements Using One WITH statement

    David G. Johnston <david.g.johnston@gmail.com> — 2022-01-20T23:35:14Z

    On Thu, Jan 20, 2022 at 4:34 PM David G. Johnston <
david.g.johnston@gmail.com> wrote:

> On Thu, Jan 20, 2022 at 4:32 PM Ken Tanzer <ken.tanzer@gmail.com> wrote:
>
>>
>>
>> On Thu, Jan 20, 2022 at 8:46 AM David G. Johnston <
>> david.g.johnston@gmail.com> wrote:
>>
>>> You can always write:
>>>
>>> CREATE VIEW cte_view AS
>>> WITH cte AS (...)
>>> SELECT * FROM cte;
>>>
>>> And then incorporate that into any queries that require the results of
>>> said CTE.
>>>
>>>
>> Is there any advantage to still using a CTE inside the view
>> definition, and then selecting it?  Instead of just
>>
>> CREATE VIEW cte_view AS
>> (...);
>>
>> Just curious, in case I'm missing something!
>>
>>
> No. That would be tunnel vision and early morning posting on my part.
>
>
Though I suspect if the CTE had a MATERIALIZED modifier there would be a
difference.  You can force a view to be materialized.

David J.