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

depstein@alliedtesting.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.

Well, this problem isn't isolated to WITH queries:

    test=> do
    $$begin
    perform(
    select 1 UNION ALL select 1
    );
    end$$;
    ERROR:  more than one row returned by a subquery used as an expression

    test=> do
    $$begin
    perform(
    select relname from pg_class
    );
    end$$;
    ERROR:  more than one row returned by a subquery used as an expression

perform() can't seem to handle any SELECT that returns more than one
row, but perform replacing the SELECT can:

    test=> do
    $$begin
    perform relname from pg_class;
    end$$;
    DO

That is certainly unsual, and I have documented this suggestion and
limitation in the attached patch that I have applied to 9.0, 9.1, and
head.

I think the idea that PERFORM will replace one or more SELECTs in a WITH
clause is just totally confusing and probably should not be supported.
I guess the only bug is that perform() can't handle more than one
returned row, but at least we have documented that and can fix it later
if we want.

I have to say, those Allied Testing people are very good at finding
bugs.

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


>
> 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
andcode 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
evenwhen 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@momjian.us>        http://momjian.us
  EnterpriseDB                             http://enterprisedb.com

  + It's impossible for everything to be true. +
diff --git a/doc/src/sgml/plpgsql.sgml b/doc/src/sgml/plpgsql.sgml
new file mode 100644
index 08c3658..a2482de
*** a/doc/src/sgml/plpgsql.sgml
--- b/doc/src/sgml/plpgsql.sgml
*************** PERFORM <replaceable>query</replaceable>
*** 940,945 ****
--- 940,948 ----
       result.  Write the <replaceable>query</replaceable> the same
       way you would write an SQL <command>SELECT</> command, but replace the
       initial keyword <command>SELECT</> with <command>PERFORM</command>.
+      For <keyword>WITH</> queries, use <keyword>PERFORM</> and then
+      place the query in parentheses.  (In this case, the query can only
+      return one row.)
       <application>PL/pgSQL</application> variables will be
       substituted into the query just as for commands that return no result,
       and the plan is cached in the same way.  Also, the special variable