DROP AGGREGATE median numeric; 

--###### ADD a value to those to the median array ###### 

--note: NEVER put apostrophes in a function declaration comment! 
DROP FUNCTION median_accum ( text , numeric ) ; 
Create FUNCTION median_accum ( text , numeric ) RETURNS text AS ' 
DECLARE 
-- Written July/2002 by Stephen Dobson 
oldtable alias for $1; 
newval alias for $2; 
tablename  text ;
query text ;
BEGIN 

tablename := oldtable;

IF char_length(tablename) = 0 THEN 
tablename := ''myMedian'' || btrim(to_char(floor(random()*10000000),''00000000''),'' '') ; 
query := ''CREATE TEMPORARY TABLE '' || tablename || '' (avalue numeric);''; 
EXECUTE query; 
END IF; 

IF NOT newval ISNULL THEN 
query := ''INSERT INTO '' || tablename || '' (avalue) VALUES ('' || to_char(cast(newval as numeric) , ''99999999999999.9999999999999999999999'' ) || '' );''; 
EXECUTE query; 
END IF;

RETURN tablename; 
END; 
' LANGUAGE 'plpgsql'; 


--############ Calculate the median ############ 
DROP FUNCTION median_final (text) ; 

CREATE FUNCTION median_final (text) RETURNS numeric AS ' 
-- Written July/2002 by Stephen Dobson 
DECLARE 
tablename alias for $1; 
median numeric; 
query text; 
countRecord RECORD;
recordCount int4; 
medianRecord RECORD; 
BEGIN 

--Ideally, this would be done in an array, 
-- plpgsql does not support arrays properly yet. 

--Create a temporary table name. Hopefully, this will provide 
--enough randomness that even a bunch of simultaenous calls 
--will probably not clobber the it. 
--Itll be as slow as molasses in Canada, but not clobbered.

IF char_length(tablename) = 0 THEN
RETURN NULL; 
END IF;

query := ''CREATE INDEX idxAMedian on '' || tablename || '' (avalue);''; 
EXECUTE query; 

query := ''SELECT COUNT(*) AS Cnt FROM '' || tablename || '';''; 
FOR countRecord IN EXECUTE query LOOP
recordcount := countRecord.cnt;
END LOOP;

IF recordcount = 0 THEN
query := ''DROP TABLE '' || tablename || '';''; 
EXECUTE query; 

RETURN NULL; 
END IF;

IF mod(recordcount,2) = 0 THEN 
--An even number, so we want two records 
query = ''SELECT avg(avalue) AS median FROM (SELECT avalue FROM '' || tablename || '' ORDER BY avalue LIMIT 2 OFFSET '' || to_char(recordcount/2-1,''9999999999'') || '') AS A;''; 
ELSE 
--An odd number, so only use one record 
query = ''SELECT avalue AS median FROM '' || tablename || '' ORDER BY avalue LIMIT 1 OFFSET '' || to_char(ceil(recordcount/2),''9999999999'') || '' ;''; 
END IF; 

-- This is the ONLY way to get the result out of query 
FOR medianRecord IN EXECUTE query LOOP 
median := medianRecord.median; 
END LOOP; 

-- play nice and drop the temporary table 
query := ''DROP TABLE '' || tablename || '';''; 
EXECUTE query; 

RETURN median; 

--median_final 
END; 
' LANGUAGE 'plpgsql'; 

CREATE AGGREGATE median ( 
BASETYPE = numeric, 
SFUNC = median_accum, 
STYPE = text, 
FINALFUNC = median_final, 
INITCOND = '');