Re: Fwd: PSQL Help from your biggest fan

>
>
> I tried your code in Postgres 8.2:
>

8.2 ?, Seems you have tested it in very Old version.

> 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?
>
>
>
It works very well in the latest version. Have you checked it.

bash-4.1$ psql
psql.bin (9.1.4)
Type "help" for help.

postgres=# \pset null NULL
Null display is "NULL".
postgres=# select * from z;
value
-------
7
NULL
(2 rows)

postgres=# select mode(value) from Z;
mode
------
7
(1 row)

--Raghav