Re: Aggregate query for multiple records

Greg Stark <gsstark(at)mit(dot)edu> writes:
> [ nice example snipped ]
> ... Also, you'll have to change it to use reals.

That part, at least, can be worked around as of 7.4: use polymorphic
functions. You can declare the functions and aggregate as working on
anyelement/anyarray, and then they will automatically work on any
datatype that has a + operator.

regression=# create or replace function first_6_accum (anyarray,anyelement) returns anyarray
regression-# language sql immutable as 'select case when array_upper($1,1)>=6 then $1 else $1||$2 end';
CREATE FUNCTION
regression=# create function sum_6(anyarray) returns anyelement immutable language sql as 'select $1[1]+$1[2]+$1[3]+$1[4]+$1[5]+$1[6]';
CREATE FUNCTION
regression=# create aggregate sum_first_6 (basetype=anyelement, sfunc=first_6_accum, stype=anyarray,initcond='{}',finalfunc=sum_6);
CREATE AGGREGATE
regression=# select sum_first_6(i) from (select i from (select 1 as i union select 2 union select 3 union select 4 union select 5 union select 6 union select 7 union select 8) as x order by i desc) as x;
sum_first_6
-------------
33
(1 row)

regression=# select sum_first_6(i) from (select i from (select 1.1 as i union select 2 union select 3 union select 4 union select 5 union select 6 union select 7.7 union select 8) as x order by i desc) as x;
sum_first_6
-------------
33.7
(1 row)

regression=#

regards, tom lane