Re: ERROR: ExecEvalAggref

eric soroos <eric-psql(at)soroos(dot)net> writes:
> update dl_profile set
> city= profiler(concat(dl_event._eventDate,dl_event.city))::text
> from dl_event where dl_event._donorNum='385'
> and dl_profile._donorNum='385'
> and dl_event._flDeleted='f'
> ERROR: ExecEvalAggref: no aggregates in this expression context

In general, aggregates at the top level of an UPDATE are ill-defined
(the SQL spec prohibits them outright, and probably we should too).
You will realize the problem when you ask yourself "exactly what
set of rows is the aggregate aggregating over? How would I control
what that set is, separately from controlling which rows of dl_profile
get updated?"

You will perhaps have better luck with a query structured like

update dl_profile set
city = (select profiler(...) from ... where FOO)
where BAR

Here, FOO controls the set of rows aggregated over, and BAR defines
what set of rows of dl_profile get updated. Note you can use
outer references to the current row of dl_profile in the sub-select.

regards, tom lane