CREATE AGGREGATE name ( BASETYPE = input_data_type [ , SFUNC = sfunc, STYPE = state_type ]</ [ , FINALFUNC = ffunc ] [ , INITCOND = initial_condition ]
Use this parameter to supply the name of the aggregate function you are creating.
Use this parameter to specify the input data type on which the new function will operate. If the aggregate function ignores input values, you can use "ANY" as the data type.
Use this parameter to specify the name of the state function you wish to be called to handle all non-NULL input data values. These functions usually follow the same format: two arguments, the first of the state_type data type and the second of the input_data_type data type. If the aggregate does not examine input values, it should take only one argument of type state_type. Either way, the function must return a value of type state_type.
This parameter contains the data type for the state value of the aggregate.
This parameter takes 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 type is state_type.
This parameter accepts the initial setting for state value. It is required that this be a literal constant that follows the same form as state_type data. This state will be set to NULL if it is not specified.
You can use the CREATE AGGREGATE command to define new aggregate functions in PostgrSQL, thereby increasing its functionality to some extent. Some commonly used aggregate functions are already included with the software (in case you'd thought of creating these), such as min(int4) and avg(float8). However, if you need to create your own aggregate, you'll want to use CREATE AGGREGATE.
Aggregate functions are identified primarily by their input data type. It is possible for two programs to be using two aggregates from your database; each even with the same name, as long as the two (or more) aggregate functions accept different data types.
|Normal functions vs. aggregate functions|
In order to avoid confusion or problems, do not make a normal function of the same name and input type as an aggregate.
An aggregate function is comprised of one or two ordinary functions. The first of the two is the state transition function, the sfunc, and the second (optional) function is the ffunc.
PostgreSQL uses a temporary stype variable that is updated by the state transition function at every input data item. If you have defined an end function for the aggregate, it will be called to calculate the output value after all data has been processed; otherwise, the ending state value is returned without processing.
Aggregate functions can also set an initial value for the internal state value; this is known as an initial condition. PostgreSQL stores this value in the database as a text field, but it must be a representation of a constant of the state value data type. This will be set to NULL if nothing is supplied.
If the state transition function is declared "strict" in pg_proc, it cannot be called with NULL parameters. Transition functions declared in this manner cause aggregate execution to behave differently the normal. NULL input parameters are ignored (the function is not called and the previous state value is retained). If the initial state value is set to NULL, it will be replaced by the first non-NULL parameter value and the transition function is called with the second non-NULL parameter value. This can be useful for creating aggregates such as max.
This behavior will only occur when state_type is the same as input_data_type. If these types are different, you need to provide a non-NULL initial condition or use a non-strict transition function.
When the state transition function is not declared as strict it will be called unconditionally at each input value. This causes it to handle NULL parameters and NULL transition values for itself, which allows the aggregate author to have more control over the aggregate's handling of NULL values.
If the final function of an aggregate is declared strict, it will not be called if the ending state value is a NULL; instead, it will output a NULL result automatically.
The following example defines an aggregate function for use with the simple data type.
CREATE AGGREGATE sum_simple ( sfunc = add_simple; basetype = simple, stype = simple, initcond = '(0,0)' );