Received: from malur.postgresql.org ([217.196.149.56]) by arkaria.postgresql.org with esmtps (TLS1.3:ECDHE_RSA_AES_256_GCM_SHA384:256) (Exim 4.92) (envelope-from ) id 1nICwd-0005yr-HN for pgsql-docs@arkaria.postgresql.org; Thu, 10 Feb 2022 17:09:23 +0000 Received: from localhost ([127.0.0.1] helo=malur.postgresql.org) by malur.postgresql.org with esmtp (Exim 4.92) (envelope-from ) id 1nICwc-0003vI-Bh for pgsql-docs@arkaria.postgresql.org; Thu, 10 Feb 2022 17:09:22 +0000 Received: from magus.postgresql.org ([2a02:c0:301:0:ffff::29]) by malur.postgresql.org with esmtps (TLS1.3:ECDHE_RSA_AES_256_GCM_SHA384:256) (Exim 4.92) (envelope-from ) id 1nICwc-0003v9-3B for pgsql-docs@lists.postgresql.org; Thu, 10 Feb 2022 17:09:22 +0000 Received: from mail-ed1-x529.google.com ([2a00:1450:4864:20::529]) by magus.postgresql.org with esmtps (TLS1.3:ECDHE_RSA_AES_128_GCM_SHA256:128) (Exim 4.92) (envelope-from ) id 1nICwY-0005VH-Mk for pgsql-docs@postgresql.org; Thu, 10 Feb 2022 17:09:21 +0000 Received: by mail-ed1-x529.google.com with SMTP id cf2so11978946edb.9 for ; Thu, 10 Feb 2022 09:09:18 -0800 (PST) DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=gmail.com; s=20210112; h=mime-version:references:in-reply-to:from:date:message-id:subject:to :cc; bh=ANAx1qmtOqaGAuQ+U/KL5GY+alywNG9g/qmWK9AwjwE=; b=ajlMiabnPOzy3AngsVwT/VNjFbr4MIML5oHhHbfqChkCdLiHHEqMT6HtPWTa+HD+gZ uiVn1c1V+DUNrO+2YlhQbqk9qsIcvRQMXAwRsJJodax/E1UF5UFYsnubs6/zEWYeHEDg b4fkw6ts/W41NLrvxkyeJI8u+IoHffLU2DJ1yadJM0NnFjO3Fe58p0HGEWTESnIoPTXU QsseladfcGVdR42W56ooi5+UZ+iAwsxptYfSOl+56eJJ+oOfsggIGDo7CSVOKlwuLFfB nM+km1Tg79i8QEfWG7Yhl3V0cQgdhARA6XbJmUy+gCJ5CEf5Gk0ZE2NgZhU5fOJljSrd 5fTQ== X-Google-DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=1e100.net; s=20210112; h=x-gm-message-state:mime-version:references:in-reply-to:from:date :message-id:subject:to:cc; bh=ANAx1qmtOqaGAuQ+U/KL5GY+alywNG9g/qmWK9AwjwE=; b=gwSXvZ+yCRcBd5b0rFOKkD568ZFVNsGQtrbQt4WGAzojHGXyvXnKQZPjlPL0xkDLek 77T6sIFIODRPJtYJAeB/6WmZLSx8BbaxkHO1ouxJcqtMABBZCB6EDKIkq0hPt6sx7uO/ cUXDZDG5rIm+cW6ZdZBqrCdtzi7Fv9m8eRsnzuLU8rR0gK6zMkm06IMf90KvYr7YwvII qm3wQcvHAwT17cdVlqruwIeGtqm4sVB1EA9tOaHqWdhiI6XkQwl2dQL9RNlD4EUZpCQW 44wM84a3e5C7rj6rzBOEGIIpN4N2HlQWZ8cFSIU9K2J+rTN/yew22GD4FeVVcb3GwLnA 9Dgg== X-Gm-Message-State: AOAM530GwqFX9I8MSGts59WWA66Gadvz3qE0OxEHLhwJhDxIB7YQP1nA n51cLH9AYkgg9dW5r40v+tJyTjMxzIrt11IcZe8= X-Google-Smtp-Source: ABdhPJylhu8Bbj75/HK+2uTkHAhXsbg0nZz+zioxaDjaSr1EZljoWqcb5Le5uxVRW6qiQjb8RsRLSH74OH2LaabyLxY= X-Received: by 2002:a05:6402:344d:: with SMTP id l13mr9590372edc.448.1644512957606; Thu, 10 Feb 2022 09:09:17 -0800 (PST) MIME-Version: 1.0 References: In-Reply-To: From: Erwin Brandstetter Date: Thu, 10 Feb 2022 18:08:51 +0100 Message-ID: Subject: Re: PL/pgSQL PERFORM WITH query To: "David G. Johnston" Cc: pgsql-docs Content-Type: multipart/alternative; boundary="000000000000aaaefe05d7ad02e2" List-Id: List-Help: List-Subscribe: List-Post: List-Owner: List-Archive: Archived-At: Precedence: bulk --000000000000aaaefe05d7ad02e2 Content-Type: text/plain; charset="UTF-8" On Tue, 8 Feb 2022 at 23:54, David G. Johnston wrote: > On Tue, Feb 8, 2022 at 3:14 PM Erwin Brandstetter > 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 --000000000000aaaefe05d7ad02e2 Content-Type: text/html; charset="UTF-8" Content-Transfer-Encoding: quoted-printable
On Tue, 8 Feb 2022 at 23:54, David G. Johnston <david.g.johnston@gmail.com> wrote:
<= /div>
On Tue, Feb 8, 2022 at 3:14= PM Erwin Brandstetter <brsaweda@gmail.com> wrote:
The manual currently reads:
https://www.postgresql.org/docs/current/plpgsql-statements.h= tml#PLPGSQL-STATEMENTS-GENERAL-SQL

> PERFORM query;

&g= t; This executes query and discards the result. Write the query the same wa= y you would write an SQL SELECT command, but replace the initial keyword SE= LECT with PERFORM. For WITH queries, use PERFORM and then place the query i= n parentheses. (In this case, the query can only return one row.)

Bu= t 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 re= lated question on Stackoverflow:

https://stackoverflow.com/questions/71040855/how-to= -perform-a-cte-query-returning-multiple-rows-columns

I suggest t= o 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_a= lias;


We define the term "Scalar Subquery" in= the documentation, we should not be avoiding it here and simply telling th= e user to "use parentheses".=C2=A0 You are using parentheses beca= use you are writing a scalar subquery and placing it in the target list of = the PERFORM command.

So, I&= #39;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.=C2= =A0 This wrapping query will have a FROM clause just like any other query a= nd you can place your WITH query there as a normal subquery.=C2=A0 An alter= native 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, adaptin= g the syntax example already provided) of both forms should suffice.=C2=A0 = We don't need to interleave an example in the prose.

PERFORM select_query; -- must begin with the = SELECT keyword
P= ERFORM * FROM (with_query) AS from_alias; -- normal subquery form
=
PERFORM (with_query);= -- scalar subquery form
=C2=A0

I agree that's clearer. And references to existin= g chapters of the mnanual are a good idea. But since the use case is not very common, I would keep it shor= t. (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=20 https://www.postgresql.org/docs/current/queries-= table-expressions.html#QUERIES-SUBQUERIES) and replace the outer SELECT with PERFORM. Short=20 syntax can be used for a scalar subquery (xref to=20 https://www.postgresql.org/docs/current/sql-expr= essions.html#SQL-SYNTAX-SCALAR-SUBQUERIES).

Re= gards
Erwin
=C2=A0
--000000000000aaaefe05d7ad02e2--