Re: Aggregate query for multiple records

Greg Stark <gsstark@mit.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
unionselect 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
unionselect 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