Hi Grzegorz and Pgsql-General,
Can you forward this to Scott Bailey? I tried sending it to his old email,
but it seems to be closed.
Or could you answer my question yourself?
Thank you so much,
Evan Stanford
---------- Forwarded message ----------
From: Evan Stanford <evanstanfo...@gmail.com>
Date: Fri, Aug 17, 2012 at 3:53 PM
Subject: PSQL Help from your biggest fan
To: arta...@comcast.net
Hi Scott Bailey,
I am a huge fan of the aggregate function you have posted that I seem to
come across all over the internet.
But I think I found a bug in one of them:
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?
Thank you so much,
Your biggest fan,
Evan Stanford
