Re: UPDATE using query; per-row function calling problem

That's interpretation of subselect is ok, when it contains only stable
functions.

Maybe add a warning when subselect contains volatile function.

2011/9/2, Rory Campbell-Lange <rory(at)campbell-lange(dot)net>:
> On 02/09/11, Tom Lane (tgl(at)sss(dot)pgh(dot)pa(dot)us) wrote:
>> Rory Campbell-Lange <rory(at)campbell-lange(dot)net> writes:
>> > I'm doing an UPDATE something like this:
>> > UPDATE
>> > slots
>> > SET
>> > a = 'a'
>> > ,b = (SELECT uuid_generate_v1())
>> > WHERE
>> > c = TRUE;
>>
>> > Each updated row in slots is getting the same value for b.
>
>> That's Postgres' interpretation of an uncorrelated sub-SELECT: there's
>> no reason to do it more than once, so it doesn't.
>>
>> > Is there a way of getting a per-row value from uuid_generate_v1()
>> > without doing a PL loop?
>>
>> Drop the word "SELECT". Why did you put that in in the first place?
>
> Hi Tom
>
> Good question to which I don't know the answer. Thanks very much for the
> advice.
>
> I was able to force a per-row call to uuid_generate_v1 by using this
> pattern
>
> UPDATE
> r_slots
> SET b = (SELECT
> y.x
> FROM
> (select -1 as n, uuid_generate_v1() as x )y
> WHERE
> y.n != r_slots.id)
> ...
>
> But
> b = uuid_generate_v1()
> is a lot simpler!
>
> In my "-1" example, am I right in assuming that I created a correlated
> subquery rather than an correlated one? I'm confused about the
> difference.
>
> Many thanks
> Rory
>
>
>
>
> --
> Sent via pgsql-general mailing list (pgsql-general(at)postgresql(dot)org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-general
>

--
------------
pasman