Skip site navigation (1) Skip section navigation (2)

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

From: Merlin Moncure <mmoncure(at)gmail(dot)com>
To: Robert Haas <robertmhaas(at)gmail(dot)com>
Cc: Bruce Momjian <bruce(at)momjian(dot)us>, depstein(at)alliedtesting(dot)com, pgsql-bugs(at)postgresql(dot)org, pgagarinov(at)alliedtesting(dot)com, vshahov(at)alliedtesting(dot)com
Subject: Re: Can't use WITH in a PERFORM query in PL/pgSQL?
Date: 2011-10-19 18:31:02
Message-ID: CAHyXU0zEzSSrf3C4nzP3=LGbWhwjpKzL=xpTztUYrPPc3btKAg@mail.gmail.com (view raw or flat)
Thread:
Lists: pgsql-bugs
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

In response to

Responses

pgsql-bugs by date

Next:From: Tom LaneDate: 2011-10-19 20:41:05
Subject: Re: incompatible pointer type
Previous:From: Robert YoungDate: 2011-10-19 17:31:49
Subject: Re: incompatible pointer type

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group