From: | Pavel Stehule <pavel(dot)stehule(at)gmail(dot)com> |
---|---|
To: | PostgreSQL Hackers <pgsql-hackers(at)postgresql(dot)org> |
Subject: | ToDo: array aggregates |
Date: | 2009-09-09 14:15:28 |
Message-ID: | 162867790909090715i192bfdfbyddf26db6e32c957d@mail.gmail.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-hackers |
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
From | Date | Subject | |
---|---|---|---|
Next Message | Alvaro Herrera | 2009-09-09 14:18:17 | Re: More robust pg_hba.conf parsing/error logging |
Previous Message | Andrew Dunstan | 2009-09-09 14:14:46 | Re: More robust pg_hba.conf parsing/error logging |