PostgreSQL custom aggregate functions

Aggregate functions compute a single result from a set of input values. Like sum() or count() they operate on one or more columns and multiple rows and output a single result.

In PostgreSQL is quite easy to create a custom aggregate function, the ingredients are:

  • Create an aggregate function
  • Create a state update function
  • optional: create a final function

For example, if we want to implement the avg function we can write:

CREATE AGGREGATE avg (float8)
(
    sfunc = float8_accum, -- {_count+=1, _sum+=value}
    stype = float8[],
    finalfunc = float8_avg, -- _sum / _count
    initcond = '{0,0}'
);

Now we need a bit more complex aggregate function to write…

continue…