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

On Wed, Oct 19, 2011 at 7:36 AM, Robert Haas <robertmhaas(at)gmail(dot)com> wrote:
> On Tue, Sep 6, 2011 at 1:43 PM, Bruce Momjian <bruce(at)momjian(dot)us> wrote:
>> 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
>>
>
> Based on previous experience with PL/pgsql, my understanding is that
> PL/pgsql basically replaces "perform" with "select" to get the query
> that it actually runs.  You'd get the same error from:
>
> rhaas=# select (select relname from pg_class);
> ERROR:  more than one row returned by a subquery used as an expression
>
> I've never really liked this behavior, but I don't have a clear idea
> what to do about it.

yeah. it's an interesting thought experiment to try and come up with
a wrapper in the form of
wrap(query);

That's efficient, guarantees that 'query' is completely run, and does
not error no matter how many rows or columns 'query' comes back with.

I've got:
select min(1) from (query) q;

The point being, how do I convert any query to a non WITH variant so
it can be PERFORM'd? Anyways, I always thought having to do perform
at all was pretty weak sauce -- not sure why it's required.

merlin