On Tue, 8 Feb 2022 at 23:54, David G. Johnston <david.g.johns...@gmail.com> wrote:
> On Tue, Feb 8, 2022 at 3:14 PM Erwin Brandstetter <brsaw...@gmail.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
