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 |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
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
From | Date | Subject | |
---|---|---|---|
Next Message | Tom Lane | 2011-10-19 20:41:05 | Re: incompatible pointer type |
Previous Message | Robert Young | 2011-10-19 17:31:49 | Re: incompatible pointer type |