CREATE AGGREGATE[ AS ] ( BASETYPE =name[ , SFUNC1 =data_type, STYPE1 =sfunc1] [ , SFUNC2 =sfunc1_return_type, STYPE2 =sfunc2] [ , FINALFUNC =sfunc2_return_type] [ , INITCOND1 =ffunc] [ , INITCOND2 =initial_condition1] )initial_condition2

*name*The name of an aggregate function to create.

*data_type*The fundamental data type on which this aggregate function operates.

*sfunc1*The state transition function to be called for every non-NULL field from the source column. It takes a variable of type

as the first argument and that field as the second argument.*sfunc1_return_type**sfunc1_return_type*The return type of the first transition function.

*sfunc2*The state transition function to be called for every non-NULL field from the source column. It takes a variable of type

as the only argument and returns a variable of the same type.*sfunc2_return_type**sfunc2_return_type*The return type of the second transition function.

*ffunc*The final function called after traversing all input fields. This function must take two arguments of types

and*sfunc1_return_type*.*sfunc2_return_type**initial_condition1*The initial value for the first transition function argument.

*initial_condition2*The initial value for the second transition function argument.

**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 can require up to three functions, two
state transition functions,
` sfunc1`
and

and a final calculation function,( internal-state1, next-data_item ) ---> next-internal-state1sfunc1( internal-state2 ) ---> next-internal-state2sfunc2

(internal-state1, internal-state2) ---> aggregate-valueffunc

Postgres creates up to two temporary variables
(referred to here as ` temp1`
and

These transition functions are required to have the following properties:

The arguments to

must be*sfunc1*of type*temp1*and*sfunc1_return_type*of type*column_value*. The return value must be of type*data_type*and will be used as the first argument in the next call to*sfunc1_return_type*.*sfunc1*The argument and return value of

must be*sfunc2*of type*temp2*.*sfunc2_return_type*The arguments to the final-calculation-function must be

and*temp1*and its return value must be a Postgres base type (not necessarily*temp2*which had been specified for BASETYPE).*data_type*FINALFUNC should be specified if and only if both state-transition functions are specified.

An aggregate function may also require one or two initial conditions, one for each transition function. These are specified and stored in the database as fields of type text.

Use **DROP AGGREGATE**
to drop aggregate functions.

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 FINALFUNC,
whereas the `sum` aggregate requires SFUNC1 (an addition
function) but not SFUNC2 or FINALFUNC and the `avg`
aggregate requires
both of the above state functions as
well as a FINALFUNC (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*
on aggregate functions for
complete examples of usage.