CREATE OR REPLACE FUNCTION _final_mode(anyarray)
RETURNS anyelement AS
$BODY$
SELECT a
FROM unnest($1) a
GROUP BY 1
ORDER BY COUNT(1) DESC, 1
LIMIT 1;
$BODY$
LANGUAGE 'sql' IMMUTABLE;
DROP AGGREGATE IF EXISTS mode(anyelement);
CREATE AGGREGATE mode(anyelement) (SFUNC=array_append,STYPE=anyarray,FINALFUNC=_final_mode,INITCOND='{}');
I also added the unnest function (although mine seemed to already have it).
I tested it like this:
sdap=# create table Z as (select 7 as value);
sdap=# select mode(value) from Z;
mode
------
7
(1 row) --WORKS
sdap=# insert into Z values (8);
sdap=# insert into Z values (8);
select mode(value) from Z;
mode
------
8
(1 row) --WORKS
sdap=# insert into Z values (NULL);
sdap=# select mode(value) from Z;
ERROR: null array element where not supported (arrayfuncs.c:872)
Any ideas?
