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 1nHZNO-0004wb-9U for pgsql-docs@arkaria.postgresql.org; Tue, 08 Feb 2022 22:54:22 +0000 Received: from localhost ([127.0.0.1] helo=malur.postgresql.org) by malur.postgresql.org with esmtp (Exim 4.92) (envelope-from ) id 1nHZNM-0004ri-Ec for pgsql-docs@arkaria.postgresql.org; Tue, 08 Feb 2022 22:54:20 +0000 Received: from makus.postgresql.org ([2001:4800:3e1:1::229]) by malur.postgresql.org with esmtps (TLS1.3:ECDHE_RSA_AES_256_GCM_SHA384:256) (Exim 4.92) (envelope-from ) id 1nHZNM-0004rZ-1E for pgsql-docs@lists.postgresql.org; Tue, 08 Feb 2022 22:54:20 +0000 Received: from mail-vk1-xa35.google.com ([2607:f8b0:4864:20::a35]) by makus.postgresql.org with esmtps (TLS1.3:ECDHE_RSA_AES_128_GCM_SHA256:128) (Exim 4.92) (envelope-from ) id 1nHZNJ-0001Og-EE for pgsql-docs@postgresql.org; Tue, 08 Feb 2022 22:54:19 +0000 Received: by mail-vk1-xa35.google.com with SMTP id l14so164156vko.12 for ; Tue, 08 Feb 2022 14:54:17 -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=EPn4P/vJ/rbxHMeapgFZ8/ZUoauKvDXdz81pPpsfovw=; b=O/4+1xXi9uwPS+9f9dcVvHl0uiHPiwD+6X4CETBSdO9uNZSTjXld0kw9qkRPtH9sop ntrRacjMhSiy6pqrBQCNyxottnXZhHwS41+6UIWzGAQOohTAFIegnh0Hb7NGXBd6jLV6 PCnCLlQE0Gq3MOJtdK5HEph8JR7tQ0qzdssjbhH8+BgSxOCX5HF8SsqIan7yiHAVoAff nqu6o7wwrU/+RUEx4uL/Fn4IztACaYetu3yRh22UIhTdPOOUagbR86gWlG9jsnQGz2/h djZmLLn9qa0uXB2ohyUvERBEVPxWdec4AA1hzGWIla9YXTLfgOvqHfJ3nbtA04fVNzRJ Me7Q== 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=EPn4P/vJ/rbxHMeapgFZ8/ZUoauKvDXdz81pPpsfovw=; b=WBtw9LUjR2NPy2MGIZTRX6WBSU9HHpvbcS3eT36mcr5BUoAIhPcNp6ibP24PPHukXT LryCgx1m8OOrXx+tbD5ybC3TmWaCC2ytfp7LNO1F9dEY12nrvMXAKqO0AHNolE/O8Mga 6l76FTBDkZALIc44Ll0xlgZz3seruHlxfqYB/C1nQBUUV6V2jqJ3V3/D8WS59leLPXzY fsCYYJWPHItd8x5T6rYXPWqJvyQO2VLcf9mp6kcCDu505F6gc9sDY9Ig0Ztl0bISKmrb ethCeWfpEAUqKalxbrpke5M49jGqo6gmPfkH1e8+XpomKtMfona7w9a+/7jLGCQqokYO qhxg== X-Gm-Message-State: AOAM532CNCvsTv7aaf6aVan7qSh7RAo/kwjdTDkuTN2MzlFSHOYWaIEt T+ud8mclzsSgWHsniZvYJ6Gd0tUvzhDxfGbxFW8= X-Google-Smtp-Source: ABdhPJy0AAlVYd++NRfUAiPwBOX4ATH8sU6mc+EeACVPF2Xn8MFtYUzG3xSLnKBtisCxGUZTRC3UdJi/F2Dp7xsmN/A= X-Received: by 2002:a05:6122:20a0:: with SMTP id i32mr2580473vkd.36.1644360855826; Tue, 08 Feb 2022 14:54:15 -0800 (PST) MIME-Version: 1.0 References: In-Reply-To: From: "David G. Johnston" Date: Tue, 8 Feb 2022 15:53:59 -0700 Message-ID: Subject: Re: PL/pgSQL PERFORM WITH query To: Erwin Brandstetter Cc: pgsql-docs Content-Type: multipart/alternative; boundary="000000000000b1b25c05d7899898" List-Id: List-Help: List-Subscribe: List-Post: List-Owner: List-Archive: Archived-At: Precedence: bulk --000000000000b1b25c05d7899898 Content-Type: text/plain; charset="UTF-8" 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 David J. --000000000000b1b25c05d7899898 Content-Type: text/html; charset="UTF-8" Content-Transfer-Encoding: quoted-printable
On Tue, Feb 8, 2022 at 3:14 PM Erwin Brandstetter <brsaweda@gmail.com> wrote:<= br>
The manual currently reads:
https://www.postgresql.org/docs/cur= rent/plpgsql-statements.html#PLPGSQL-STATEMENTS-GENERAL-SQL

>= PERFORM query;

> This executes query and discards the result. Wr= ite the query the same way you would write an SQL SELECT command, but repla= ce the initial keyword SELECT with PERFORM. For WITH queries, use PERFORM a= nd then place the query in parentheses. (In this case, the query can only r= eturn 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 w= ith alias. There was a related question on Stackoverflow:

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

I suggest to clarify like:

For WITH queries, use PERFO= RM and place the query in parentheses. If the query returns more than a sin= gle value (one column of one row) you must treat it as subquery, writing PE= RFORM * FROM (query) my_alias;


= We define the term "Scalar Subquery" in the documentation, we sho= uld not be avoiding it here and simply telling the user to "use parent= heses".=C2=A0 You are using parentheses because you are writing a scal= ar 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 inst= ead write your query independently of the PERFORM top-level query.=C2=A0 Th= is wrapping query will have a FROM clause just like any other query and you= can place your WITH query there as a normal subquery.=C2=A0 An alternative= is to use a scalar subquery (provide xref to syntax), in which case you ca= n simply place it after the PERFORM keyword.

Combini= ng that with examples (or, as below, adapting the syntax example already pr= ovided) of both forms should suffice.=C2=A0 We don't need to interleave= an example in the prose.

PERFORM select_query; -- mus= t begin with the SELECT keyword
PERFORM * FROM (with_query) AS from= _alias; -- normal subquery form
PERFORM (with_query); -- scalar= subquery form

David J.

--000000000000b1b25c05d7899898--