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

-----Original Message-----
From: pgsql-general-owner(at)postgresql(dot)org
[mailto:pgsql-general-owner(at)postgresql(dot)org] On Behalf Of Rory Campbell-Lange
Sent: Monday, September 05, 2011 4:55 PM
To: David Johnston
Cc: Tom Lane; pgsql-general(at)postgresql(dot)org
Subject: Re: [GENERAL] UPDATE using query; per-row function calling problem

On 02/09/11, David Johnston (polobo(at)yahoo(dot)com) wrote:
> > 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.
> >
> Correlated: has a where clause that references the outer query
> Un-correlated: not correlated
>
> Because of the where clause a correlated sub-query will return a
> different record for each row whereas an un-correlated sub-query will
> return the same record for all rows since the where clause (if any) is
> constant.

Hi David -- thanks for the clarification. However I'm still a little
confused. As I understand it the following is a un-correlated sub-query:

UPDATE
slots
SET
a = 'a'
,b = (SELECT uuid_generate_v1())
WHERE
c = TRUE;

and the following, without a 'WHERE', is a correlated sub-query:

UPDATE
slots
SET
a = 'a'
,b = uuid_generate_v1()
WHERE
c = TRUE;

Is the point that the lower is not a sub-query at all?

----------------------------------------------------------------------------
--------------------------

Correct, the second query uses a simple function call to set the value of
"b"; Using your example you would need to do something like:

UPDATE
slots
SET
a = 'a'
,b = (SELECT something FROM somewhere WHERE somewhere.a = slots.a)
WHERE
c = TRUE;

to use a correlated sub-query. Since "uuid_generate_v1()" doesn't naturally
link to slots (or anything else) there isn't any meaningful way to use a
correlated sub-query in this situation. Since you are using a function (as
opposed to a direct TABLE/VIEW) the use of a sub-query is pointless and,
apparently, results in optimizations that are undesirable.

David J.