Hello
I thing so there are lot of aggregates based on generating array. We
have fast array_agg function, but we cannot be same effective with
custom aggregates. So my proposal is creating some new kind of
aggregates, that are based on arrays. The primary goal is getting same
speed as array_agg has.
Example: Median - http://wiki.postgresql.org/wiki/Aggregate_Median
CREATE OR REPLACE FUNCTION _final_median(numeric[]) RETURNS numeric AS
$$ SELECT AVG(val) FROM ( SELECT val FROM unnest($1) val ORDER BY 1 LIMIT 2 - MOD(array_upper($1, 1), 2)
OFFSET CEIL(array_upper($1, 1) / 2.0) - 1 ) sub;
$$
LANGUAGE 'sql' IMMUTABLE;
CREATE AGGREGATE median(numeric) ( SFUNC=array_append, STYPE=_numeric[], FINALFUNC=_final_median, INITCOND='{}'
);
This function is slower than array_agg because we use sfunc
array_append. If could to use array_agg as base with enhancing final
function then we could this task faster.
Regards
Pavel Stehule
