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

On Wed, Oct 19, 2011 at 7:36 AM, Robert Haas <robertmhaas@gmail.com> wrote:
> On Tue, Sep 6, 2011 at 1:43 PM, Bruce Momjian <bruce@momjian.us> wrote:
>> Well, this problem isn't isolated to WITH queries:
>>
>> =A0 =A0 =A0 =A0test=3D> do
>> =A0 =A0 =A0 =A0$$begin
>> =A0 =A0 =A0 =A0perform(
>> =A0 =A0 =A0 =A0select 1 UNION ALL select 1
>> =A0 =A0 =A0 =A0);
>> =A0 =A0 =A0 =A0end$$;
>> =A0 =A0 =A0 =A0ERROR: =A0more than one row returned by a subquery used a=
s an expression
>>
>> =A0 =A0 =A0 =A0test=3D> do
>> =A0 =A0 =A0 =A0$$begin
>> =A0 =A0 =A0 =A0perform(
>> =A0 =A0 =A0 =A0select relname from pg_class
>> =A0 =A0 =A0 =A0);
>> =A0 =A0 =A0 =A0end$$;
>> =A0 =A0 =A0 =A0ERROR: =A0more than one row returned by a subquery used a=
s 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. =A0You'd get the same error from:
>
> rhaas=3D# select (select relname from pg_class);
> ERROR: =A0more 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