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 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.