PL/pgSQL PERFORM WITH query

From: Erwin Brandstetter <brsaweda(at)gmail(dot)com>
To: pgsql-docs <pgsql-docs(at)postgresql(dot)org>
Subject: PL/pgSQL PERFORM WITH query
Date: 2022-02-08 22:14:05
Message-ID: CAGHENJ6jaTJQ-0nFWg2Vkco28ffsDMLH=txZcgftvCFFPGGSww@mail.gmail.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: 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-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

Responses

Browse pgsql-docs by date

  From Date Subject
Next Message David G. Johnston 2022-02-08 22:53:59 Re: PL/pgSQL PERFORM WITH query
Previous Message Fujii Masao 2022-02-07 16:42:49 Re: maximum number of backtrace frames logged by backtrace_functions