-----Original Message----- From: pgsql-general-ow...@postgresql.org [mailto:pgsql-general-ow...@postgresql.org] On Behalf Of Rory Campbell-Lange Sent: Monday, September 05, 2011 4:55 PM To: David Johnston Cc: Tom Lane; pgsql-general@postgresql.org Subject: Re: [GENERAL] UPDATE using query; per-row function calling problem
On 02/09/11, David Johnston (pol...@yahoo.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. -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general