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