Re: Can't use WITH in a PERFORM query in PL/pgSQL?

Added to TODO:

Improve PERFORM handling of WITH queries or document limitation

---------------------------------------------------------------------------

depstein(at)alliedtesting(dot)com wrote:
> Update: It has been suggested to wrap perform around a select like this:
>
> do
> $$begin
> perform(
> with A as (select 1 as foo)
> select foo from A
> );
> end$$;
>
> This won't work if select returns more than one statement:
>
> do
> $$begin
> perform(
> with A as (select generate_series(1,3) as foo)
> select foo from A
> );
> end$$;
>
> ERROR: more than one row returned by a subquery used as an expression
>
> So I still say it's broken.
>
> (Sorry for top-posting: I am forced to use Outlook at work...)
>
> From: Dmitry Epstein
> Sent: Sunday, March 06, 2011 4:29 PM
> To: 'pgsql-bugs(at)postgresql(dot)org'
> Cc: Peter Gagarinov; Vladimir Shahov
> Subject: Can't use WITH in a PERFORM query in PL/pgSQL?
>
> PostgreSQL 9.0.1
>
> It seems that PostgreSQL doesn't understand the WITH construct when used in a PERFORM query inside PL/pgSQL functions and code blocks:
>
> Example:
>
> do
> $$begin
> with A as (select 1 as foo)
> perform foo from A;
> end$$;
>
> syntax error at or near "perform"
>
> do
> $$begin
> with A as (select 1 as foo)
> select foo from A;
> end$$;
>
> query has no destination for result data
>
> The only workaround that I can think of is to use a dummy variable to capture the query result. This has to be done even when the query doesn't have a result (as when calling a function returning void).
>
> do
> $$declare
> dummy record;
> begin
> with A as (select 1 as foo)
> select foo into dummy from A;
> end$$;
>
>
> Dmitry Epstein | Developer
>
> Allied Testing
> T + 7 495 544 48 69 Ext 417
> M + 7 926 215 73 36
>
> www.alliedtesting.com<http://www.alliedtesting.com/>
> We Deliver Quality.
>

--
Bruce Momjian <bruce(at)momjian(dot)us> http://momjian.us
EnterpriseDB http://enterprisedb.com

+ It's impossible for everything to be true. +