PostgreSQL - custom aggregate function

Question

I am going through code for a legacy application backended by PostgreSQL (I think it was PG version 9.1).

I came across this function - which I guess is for custom aggregates, but do not understand what it is doing:

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;

-- Tell Postgres how to use our aggregate
CREATE AGGREGATE mode(anyelement) (
  SFUNC=array_append, --Function to call for each row. Just builds the array
  STYPE=anyarray,
  FINALFUNC=_final_mode, --Function to call after everything has been added to array
  INITCOND='{}' --Initialize an empty array when starting
);

Can anyone explain what the function is doing?

Laurenz Albe · Accepted Answer · 2017-09-29 10:07:21Z

3

This aggregate function will return the element that occurs most often, breaking the tie by using the one that sorts first if there are more elements that occur equally often.

In a table atab like

 x
---
 a
 z
 q
 a
 b
 a
 z
 z

You will get the following:

SELECT mode(x) FROM atab;

 mode
------
    a
(1 row)

because both a and z occur three times, but a sorts before z lexically.

answered Sep 29, 2017 at 10:07

Laurenz Albe

257k22 gold badges312 silver badges388 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Homunculus Reticulli Over a year ago

Fantastic explanation thanks. With the benefit of hindsight, the purpose of the function was indicated by the name ;) !

Collectives™ on Stack Overflow

PostgreSQL - custom aggregate function

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related