Обсуждение: ERROR: ExecEvalAggref

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?

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

> 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
intoproduction.  

> 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