CREATE AGGREGATE( BASETYPE =name[ , SFUNC1 =input_data_type, STYPE1 =sfunc1] [ , SFUNC2 =state1_type, STYPE2 =sfunc2] [ , FINALFUNC =state2_type] [ , INITCOND1 =ffunc] [ , INITCOND2 =initial_condition1] )initial_condition2

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

*input_data_type*The input data type on which this aggregate function operates.

*sfunc1*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

and the second of type*state1_type*. The function must return a value of type*input_data_type*. This function takes the current state value 1 and the current input data item, and returns the next state value 1.*state1_type**state1_type*The data type for the first state value of the aggregate.

*sfunc2*A state transition function to be called for every non-NULL input data value. This must be a function of one argument of type

, returning a value of the same type. This function takes the current state value 2 and returns the next state value 2.*state2_type**state2_type*The data type for the second state value of the aggregate.

*ffunc*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

and*state1_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.*state2_type**initial_condition1*The initial value for state value 1.

*initial_condition2*The initial value for state value 2.

`CREATE`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

( internal-state1, next-data-item ) ---> next-internal-state1sfunc1( internal-state2 ) ---> next-internal-state2sfunc2(internal-state1, internal-state2) ---> aggregate-valueffunc

Postgres creates one or two temporary variables
(of data types ` stype1` and/or

` ffunc` must be specified if
both transition functions are specified. If only one transition function
is used, then

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

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

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.