I created a median function based on some code from the web. That was a few
months ago. Today, I wanted the 25th and seventy-fifth percentiles. I wanted
to modify the code used in the median function to do the percentiles.
The following code works. However, if I run it, then find the created object
(code.percentile25) in pgadmin, get the create script, and then execute the
create script, it fails, being unable to find the array function.
It seems that the create script returned from pgadmin adds in quotation
marks around the function name in the FINALFUNC line, which causes the code
to no longer work.
I am sending this to pgadmin, because I haven't got a clue whether the code
returned in the create script is parsed by pgadmin or postgresql. I'm sure
you folks know better than I.
Cheers,
Angus Carr.
================== code below. ================
CREATE OR REPLACE function code.array_percentile25(numeric[])
RETURNS numeric AS
$BODY$
SELECT CASE WHEN array_upper($1,1) = 0 THEN null ELSE
asorted[ceiling(array_upper(asorted,1)*0.25)] END
FROM (SELECT ARRAY(SELECT ($1)[n] FROM
generate_series(1,array_upper($1,1)) AS n
WHERE ($1)[n] IS NOT NULL
ORDER BY ($1)[n]
) AS asorted
) AS foo ;
$BODY$
LANGUAGE 'sql' IMMUTABLE
COST 100;
--ALTER FUNCTION code.array_percentile75(numeric[]) OWNER TO postgres;
ALTER FUNCTION code.array_percentile25(numeric[]) OWNER TO apc;
CREATE AGGREGATE code.percentile25(numeric) (
SFUNC=array_append,
STYPE=numeric[],
FINALFUNC=code.array_percentile25
);
