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

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