ERROR: ExecEvalAggref

From: eric soroos <eric-psql(at)soroos(dot)net>
To: pgsql-general(at)postgresql(dot)org
Subject: ERROR: ExecEvalAggref
Date: 2003-01-28 22:37:14
Message-ID: 90555128.1168357862@[4.42.179.151]
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-general

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?

Responses

Browse pgsql-general by date

  From Date Subject
Next Message Uros Gruber 2003-01-28 22:48:33 Re: tsearch comments
Previous Message Tom Lane 2003-01-28 22:26:42 Re: Q: Rename constraint