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-fifthpercentiles. I wanted to modify the code used in the median function to do the percentiles.<br /><br />The
followingcode 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.<br /><br />It seems that
thecreate script returned from pgadmin adds in quotation marks around the function name in the FINALFUNC line, which
causesthe code to no longer work.<br /><br />I am sending this to pgadmin, because I haven't got a clue whether the
codereturned in the create script is parsed by pgadmin or postgresql. I'm sure you folks know better than I.<br /><br
/>Cheers,<br/>Angus Carr.<br /><br />================== code below. ================<br />CREATE OR REPLACE function
code.array_percentile25(numeric[])<br/> RETURNS numeric AS<br />$BODY$<br /> SELECT CASE WHEN array_upper($1,1) = 0
THENnull ELSE asorted[ceiling(array_upper(asorted,1)*0.25)] END<br /> FROM (SELECT ARRAY(SELECT ($1)[n] FROM<br
/> generate_series(1,array_upper($1,1)) AS n<br /> WHERE ($1)[n] IS NOT NULL<br /> ORDER BY
($1)[n]<br/> ) AS asorted <br /> ) AS foo ;<br />$BODY$<br /> LANGUAGE 'sql' IMMUTABLE<br /> COST 100;<br
/>--ALTERFUNCTION code.array_percentile75(numeric[]) OWNER TO postgres;<br />ALTER FUNCTION
code.array_percentile25(numeric[])OWNER TO apc;<br /><br /><br />CREATE AGGREGATE code.percentile25(numeric) (<br />
SFUNC=array_append,<br/> STYPE=numeric[],<br /> FINALFUNC=code.array_percentile25<br />);<br />