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

On 02/09/11, Tom Lane (tgl@sss.pgh.pa.us) wrote:
> Rory Campbell-Lange <rory@campbell-lange.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