Defining your own aggregate function

Solution

Create your own aggregate function with CREATE AGGREGATE and an accumulator function ref.

Discussion

If you're planning on doing this without reading the manuals think again. Basically however you need to define:

• The return-type of the aggregate function (e.g. int4)

• The type of thing it aggregates (e.g. int4 again)

• An initial value (probably 0)

• A state-transition function to accumulate values (takes the running total and the next value)

• Optionally a finalisation function to produce the final value (useful for averages or standard deviations)

You might well end up coding the state-transition and finalisation functions in C, but here is an trivial one-liner.

CREATE AGGREGATE catenate(sfunc1=textcat, basetype=text, stype1=text, initcond1='');
   

This sets the initial condition to '' (the empty string), accepts and returns text and uses the built-in textcat function to concatenate string values.

NOTE - order of concatenated text is not well-defined. You should only use commutable functions such as +, * etc (subnote - commutable might not be right I might mean 'associative'. If you don't know whether I'm right or wrong, make sure you test your aggregate function carefully).