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?
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 |