public inbox for [email protected]help / color / mirror / Atom feed
PL/pgSQL PERFORM WITH query 3+ messages / 2 participants [nested] [flat]
* PL/pgSQL PERFORM WITH query @ 2022-02-08 22:14 Erwin Brandstetter <[email protected]> 0 siblings, 1 reply; 3+ messages in thread From: Erwin Brandstetter @ 2022-02-08 22:14 UTC (permalink / raw) To: pgsql-docs 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; Regards Erwin ^ permalink raw reply [nested|flat] 3+ messages in thread
* Re: PL/pgSQL PERFORM WITH query @ 2022-02-08 22:53 David G. Johnston <[email protected]> parent: Erwin Brandstetter <[email protected]> 0 siblings, 1 reply; 3+ messages in thread From: David G. Johnston @ 2022-02-08 22:53 UTC (permalink / raw) To: Erwin Brandstetter <[email protected]>; +Cc: pgsql-docs 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 David J. ^ permalink raw reply [nested|flat] 3+ messages in thread
* Re: PL/pgSQL PERFORM WITH query @ 2022-02-10 17:08 Erwin Brandstetter <[email protected]> parent: David G. Johnston <[email protected]> 0 siblings, 0 replies; 3+ messages in thread From: Erwin Brandstetter @ 2022-02-10 17:08 UTC (permalink / raw) To: David G. Johnston <[email protected]>; +Cc: pgsql-docs 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 ^ permalink raw reply [nested|flat] 3+ messages in thread
end of thread, other threads:[~2022-02-10 17:08 UTC | newest] Thread overview: 3+ messages (download: mbox.gz follow: Atom feed) -- links below jump to the message on this page -- 2022-02-08 22:14 PL/pgSQL PERFORM WITH query Erwin Brandstetter <[email protected]> 2022-02-08 22:53 ` David G. Johnston <[email protected]> 2022-02-10 17:08 ` Erwin Brandstetter <[email protected]>
This inbox is served by agora; see mirroring instructions for how to clone and mirror all data and code used for this inbox