Subject: PL/pgSQL PERFORM WITH query

From: Erwin Brandstetter <[email-masked]>
Date: Tue, 8 Feb 2022 23:14:05 +0100

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;

Regards
Erwin

---

From: "David G. Johnston" <[email-masked]>
Date: Tue, 8 Feb 2022 15:53:59 -0700

On Tue, Feb 8, 2022 at 3:14 PM Erwin Brandstetter <[email-masked]>
wrote:

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

David J.

---

From: Erwin Brandstetter <[email-masked]>
Date: Thu, 10 Feb 2022 18:08:51 +0100

On Tue, 8 Feb 2022 at 23:54, David G. Johnston <[email-masked]>
wrote:

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

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