ERROR: ExecEvalAggref

Поиск
Список
Период
Сортировка
Искать
От
eric soroos
Тема
ERROR: ExecEvalAggref
Дата
Msg-id
90555128.1168357862@[4.42.179.151]
Список
Дерево обсуждения
ERROR: ExecEvalAggref eric soroos <eric-psql@soroos.net>
Re: ERROR: ExecEvalAggref Tom Lane <tgl@sss.pgh.pa.us>
Re: ERROR: ExecEvalAggref eric 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?


В списке pgsql-general по дате отправления
От: ayhan
Дата:
От: Daniel Schuchardt
Дата:
Сообщение: own Function as Default VALUE
FAQ