public inbox for [email protected]
help / color / mirror / Atom feedFrom: Erwin Brandstetter <[email protected]>
To: David G. Johnston <[email protected]>
Cc: pgsql-docs <[email protected]>
Subject: Re: PL/pgSQL PERFORM WITH query
Date: Thu, 10 Feb 2022 18:08:51 +0100
Message-ID: <CAGHENJ5oxpsr_Pj4aY8Em=P0jLv4ORA6EngUC3yFoqij0HYetg@mail.gmail.com> (raw)
In-Reply-To: <CAKFQuwaf3KO+Q7QOEvbfnMxfJ_X+749MvW+MvE_tZVOBsNHeag@mail.gmail.com>
References: <CAGHENJ6jaTJQ-0nFWg2Vkco28ffsDMLH=txZcgftvCFFPGGSww@mail.gmail.com>
<CAKFQuwaf3KO+Q7QOEvbfnMxfJ_X+749MvW+MvE_tZVOBsNHeag@mail.gmail.com>
On Tue, 8 Feb 2022 at 23:54, David G. Johnston <[email protected]>
wrote:
> On Tue, Feb 8, 2022 at 3:14 PM Erwin Brandstetter <[email protected]>
> wrote:
>
>> The manual currently reads:
>>
>> https://www.postgresql.org/docs/current/plpgsql-statements.html#PLPGSQL-STATEMENTS-GENERAL-SQL
>>
>> > PERFORM query;
>>
>> > This executes query and discards the result. Write the query the same
>> way you would write an SQL SELECT command, but replace the initial keyword
>> SELECT with PERFORM. For WITH queries, use PERFORM and then place the query
>> in parentheses. (In this case, the query can only return one row.)
>>
>> But that only works for a single returned value (one column of one row).
>> Else we need to treat the WITH query like a subquery with alias. There was
>> a related question on Stackoverflow:
>>
>>
>> https://stackoverflow.com/questions/71040855/how-to-perform-a-cte-query-returning-multiple-rows-colu...
>>
>> I suggest to clarify like:
>>
>> For WITH queries, use PERFORM and place the query in parentheses. If the
>> query returns more than a single value (one column of one row) you must
>> treat it as subquery, writing PERFORM * FROM (query) my_alias;
>>
>>
> We define the term "Scalar Subquery" in the documentation, we should not
> be avoiding it here and simply telling the user to "use parentheses". You
> are using parentheses because you are writing a scalar subquery and placing
> it in the target list of the PERFORM command.
>
> So, I'd suggest the following wording:
>
> Since WITH queries do not start with the SELECT keyword you must instead
> write your query independently of the PERFORM top-level query. This
> wrapping query will have a FROM clause just like any other query and you
> can place your WITH query there as a normal subquery. An alternative is to
> use a scalar subquery (provide xref to syntax), in which case you can
> simply place it after the PERFORM keyword.
>
> Combining that with examples (or, as below, adapting the syntax example
> already provided) of both forms should suffice. We don't need to
> interleave an example in the prose.
>
> PERFORM select_query; -- must begin with the SELECT keyword
> PERFORM * FROM (with_query) AS from_alias; -- normal subquery form
> PERFORM (with_query); -- scalar subquery form
>
I agree that's clearer. And references to existing chapters of the mnanual
are a good idea. But since the use case is not very common, I would keep it
short. (Just fix the misinformation!)
How about this:
A WITH query does not start with the SELECT keyword. Wrap the whole query
as subquery (xref to
https://www.postgresql.org/docs/current/queries-table-expressions.html#QUERIES-SUBQUERIES)
and replace the outer SELECT with PERFORM. Short syntax can be used for a
scalar subquery (xref to
https://www.postgresql.org/docs/current/sql-expressions.html#SQL-SYNTAX-SCALAR-SUBQUERIES
).
Regards
Erwin
reply
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: [email protected]
Cc: [email protected], [email protected]
Subject: Re: PL/pgSQL PERFORM WITH query
In-Reply-To: <CAGHENJ5oxpsr_Pj4aY8Em=P0jLv4ORA6EngUC3yFoqij0HYetg@mail.gmail.com>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by agora; see mirroring instructions
for how to clone and mirror all data and code used for this inbox