Re: Fwd: PSQL Help from your biggest fan

I tried your code in Postgres 8.2:

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?