17th September 2020: PostgreSQL 13 RC 1 Released!

This documentation is for an unsupported version of PostgreSQL.

You may want to view the same page for the current version, or one of the supported versions listed above instead.

You may want to view the same page for the current version, or one of the supported versions listed above instead.

CREATE AGGREGATEname( BASETYPE =input_data_type[ , SFUNC1 =sfunc1, STYPE1 =state1_type] [ , SFUNC2 =sfunc2, STYPE2 =state2_type] [ , FINALFUNC =ffunc] [ , INITCOND1 =initial_condition1] [ , INITCOND2 =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

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

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

`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. `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 `sfunc2`, and a final calculation function,
`ffunc`. These are used as
follows:

sfunc1( internal-state1, next-data-item ) ---> next-internal-state1sfunc2( internal-state2 ) ---> next-internal-state2ffunc(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.