Practical PostgreSQL

Create Aggregate

Defines a new aggregate function within the database.

Synopsis

CREATE AGGREGATE name ( BASETYPE = input_type
    [ , SFUNC = sfunc, STYPE = state_type ]
    [ , FINALFUNC = ffunc ]
    [ , INITCOND = initial_condition ] )

Parameters

name

The name of the aggregate function you are creating.

input_type

The input data type on which the new function will operate. If the aggregate function ignores input values (as the count() function does), you can use the ANY string constant as the data type.

sfunc

The name of the function you wish to be called to handle all non-NULL input data values. These functions usually follow the same format, having two arguments. The first argument is of the state_type data type, and the second of the input_type data type. If the aggregate does not examine input values, it will take only one argument of type state_type. Either way, the function must return a value of type state_type.

state_type

The data type for the state value of the aggregate.

ffunc

The name of the final function called upon to compute the aggregate’s result after all input has been examined. This function is required to accept a single argument of type state_type.

The output data type of the aggregate function is defined as the return type of this function. If you do not specify ffunc, the ending state value is used as the aggregate’s result, and the output data type is determined by state_type.

initial_condition

The initial value of the aggregate function’s state value. This is a literal constant ...