CREATE AGGREGATE name ( BASETYPE = input_data_type
[ , SFUNC1 = sfunc1, STYPE1 = state1_type ]
[ , SFUNC2 = sfunc2, STYPE2 = state2_type ]
[ , FINALFUNC = ffunc ]
[ , INITCOND1 = initial_condition1 ]
[ , INITCOND2 = initial_condition2 ] )
The name of an aggregate function to create.
The input data type on which this aggregate function operates.
A state transition function to be called for every non-NULL input data value. This must be a function of two arguments, the first being of type state1_type and the second of type input_data_type. The function must return a value of type state1_type. This function takes the current state value 1 and the current input data item, and returns the next state value 1.
The data type for the first state value of the aggregate.
A state transition function to be called for every non-NULL input data value. This must be a function of one argument of type state2_type, returning a value of the same type. This function takes the current state value 2 and returns the next state value 2.
The data type for the second state value of the aggregate.
The final function called to compute the aggregate's result after all input data has been traversed. If both state values are used, the final function must take two arguments of types state1_type and state2_type. If only one state value is used, the final function must take a single argument of that state value's type. The output datatype of the aggregate is defined as the return type of this function.
The initial value for state value 1.
The initial value for state value 2.
Message returned if the command completes successfully.
CREATE AGGREGATE allows a user or programmer to extend Postgres functionality by defining new aggregate functions. Some aggregate functions for base types such as min(int4) and avg(float8) are already provided in the base distribution. If one defines new types or needs an aggregate function not already provided then CREATE AGGREGATE can be used to provide the desired features.
An aggregate function is identified by its name and input data type. Two aggregates can have the same name if they operate on different input types. To avoid confusion, do not make an ordinary function of the same name and input data type as an aggregate.
An aggregate function is made from between one and three ordinary functions: two state transition functions, sfunc1 and sfunc2, and a final calculation function, ffunc. These are used as follows:
sfunc1( internal-state1, next-data-item ) ---> next-internal-state1 sfunc2( internal-state2 ) ---> next-internal-state2 ffunc(internal-state1, internal-state2) ---> aggregate-value
Postgres creates one or two temporary variables (of data types stype1 and/or stype2) to hold the current internal states of the aggregate. At each input data item, the state transition function(s) are invoked to calculate new values for the internal state values. After all the data has been processed, the final function is invoked once to calculate the aggregate's output value.
ffunc must be specified if both transition functions are specified. If only one transition function is used, then ffunc is optional. The default behavior when ffunc is not provided is to return the ending value of the internal state value being used (and, therefore, the aggregate's output type is the same as that state value's type).
An aggregate function may also provide one or two initial conditions, that is, initial values for the internal state values being used. These are specified and stored in the database as fields of type text, but they must be valid external representations of constants of the state value datatypes. If sfunc1 is specified without an initcond1 value, then the system does not call sfunc1 at the first input item; instead, the internal state value 1 is initialized with the first input value, and sfunc1 is called beginning at the second input item. This is useful for aggregates like MIN and MAX. Note that an aggregate using this feature will return NULL when called with no input values. There is no comparable provision for state value 2; if sfunc2 is specified then an initcond2 is required.
Use DROP AGGREGATE to drop aggregate functions.
The parameters of CREATE AGGREGATE can be written in any order, not just the order illustrated above.
It is possible to specify aggregate functions that have varying combinations of state and final functions. For example, the count aggregate requires sfunc2 (an incrementing function) but not sfunc1 or ffunc, whereas the sum aggregate requires sfunc1 (an addition function) but not sfunc2 or ffunc, and the avg aggregate requires both state functions as well as a ffunc (a division function) to produce its answer. In any case, at least one state function must be defined, and any sfunc2 must have a corresponding initcond2.
Refer to the chapter on aggregate functions in the PostgreSQL Programmer's Guide for complete examples of usage.
CREATE AGGREGATE is a Postgres language extension. There is no CREATE AGGREGATE in SQL92.