Re: PL/pgSQL PERFORM WITH query

On Tue, 8 Feb 2022 at 23:54, David G. Johnston <david(dot)g(dot)johnston(at)gmail(dot)com>
wrote:

> On Tue, Feb 8, 2022 at 3:14 PM Erwin Brandstetter <brsaweda(at)gmail(dot)com>
> 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-columns
>>
>> 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