Fwd: PSQL Help from your biggest fan
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 <evanstanford1@gmail.com>
Date: Fri, Aug 17, 2012 at 3:53 PM
Subject: PSQL Help from your biggest fan
To: artacus@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
Import Notes
Reply to msg id not found: CAMEe77agV+WAUkfG29MCBrE-JMayh-qY-L5+HV6RQ-J8J9jWAQ@mail.gmail.comReference msg id not found: CAMEe77agV+WAUkfG29MCBrE-JMayh-qY-L5+HV6RQ-J8J9jWAQ@mail.gmail.com
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
