Postgres Pro
Downloads
Home   >   mailing lists

Thread: Fwd: PSQL Help from your biggest fan

Fwd: PSQL Help from your biggest fan

From
Evan Stanford
Date:
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


Re: Fwd: PSQL Help from your biggest fan

From
Raghavendra
Date:

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

Re: Fwd: PSQL Help from your biggest fan

From
Tom Lane
Date:
Raghavendra <raghavendra.rao@enterprisedb.com> writes:
>> I tried your code in Postgres 8.2:

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

Indeed.  The example works OK for me too, in 8.2.23 which is the last of
that release branch.

            regards, tom lane