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@postgresql.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 captu=
re 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.