ERROR: ExecEvalAggref

Started by Eric Soroosabout 23 years ago3 messagesgeneral
Jump to latest
#1Eric Soroos
eric-psql@soroos.net

I'm having trouble with an aggregate function under both 7.2.1 and 7.2.3.

Essentially, the aggreate function returns the last attribute value seen, so for
the example below, profiler(... a ...) = bar and profiler(... b...) = foo.

_date a b
1/1/03 foo null
1/2/03 bar baz
1/3/03 null foo

For the most part it works properly, except that one of my installations
is having trouble.

This query fails: (this is a minimal subset of a larger query)

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

This query succeeds: (same query, different field)

update dl_profile set
_outgoingSubject= profiler(concat(dl_event._eventDate,dl_event._outgoingSubject))::text
from dl_event where dl_event._donorNum='385'
and dl_profile._donorNum='385'
and dl_event._flDeleted='f'

This query also succeeds, giving the expected values:

select profiler(concat(dl_event._eventDate,dl_event.city))::text as city,
profiler(concat(dl_event._eventDate,dl_event._outgoingSubject))::text as outgoingSubject
from dl_event where _donorNum='385'
and dl_event._flDeleted='f';

city | outgoingsubject
-----------+---------------------------------------
Cambridge | ********* News: January 20th, 2003
(1 row)

These are the definitions of the functions that the aggregate relies on:

CREATE FUNCTION "datefromconcat" (text) RETURNS timestamp with time zone AS
'select substring($1 from 0 for (position(''|'' in $1)-1))::timestamp'
LANGUAGE 'sql' WITH ( iscachable, isstrict );

CREATE FUNCTION "valuefromconcat" (text) RETURNS text AS
'select substring($1 from (position(''|'' in $1)+1))'
LANGUAGE 'sql' WITH ( iscachable, isstrict );

CREATE FUNCTION "concat" (timestamp with time zone,text) RETURNS text AS
'select $1::text || ''|'' || $2'
LANGUAGE 'sql' WITH ( iscachable, isstrict );

CREATE FUNCTION "aggregateprofile" (text,text) RETURNS text AS
'select case when $2 is null then $1
when dateFromConcat($1) > dateFromConcat($2) then $1
else $2
end' LANGUAGE 'sql' WITH ( iscachable, isstrict );

CREATE AGGREGATE profiler
( BASETYPE = text,
SFUNC = aggregateprofile,
STYPE = text,
FINALFUNC = valuefromconcat );

Any ideas?

#2Tom Lane
tgl@sss.pgh.pa.us
In reply to: Eric Soroos (#1)
Re: ERROR: ExecEvalAggref

eric soroos <eric-psql@soroos.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

#3Eric Soroos
eric-psql@soroos.net
In reply to: Tom Lane (#2)
Re: ERROR: ExecEvalAggref

In general, aggregates at the top level of an UPDATE are ill-defined
(the SQL spec prohibits them outright, and probably we should too).

In cases like this, it's probably better if it had never worked rather than failing for no apparent reason once it got into production.

You will perhaps have better luck with a query structured like

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

I've recast this as the following to allow for more fields without adding a subselect for each field.

update dl_profile set
_incomingSubject= calc._incomingSubject
...
from (select
profiler(concat(dl_event._eventDate,dl_event._incomingSubject) as _incomingSubject
...
from dl_event where dl_event._donorNum='6'
and dl_event._flDeleted='f'
) as calc
where dl_profile._donorNum='6';

This is working now, thanks for the help.

eric