Name
CREATE AGGREGATE
-- Defines a new aggregate function within the database.
Synopsis
CREATE AGGREGATE name ( BASETYPE = input_data_type
[ , SFUNC = sfunc, STYPE = state_type ]</
[ , FINALFUNC = ffunc ]
[ , INITCOND = initial_condition ]
Parameters
- name
Use this parameter to supply the name of the aggregate function you are
creating.
- input_data_type
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.
- sfunc1
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.
- state_type
This parameter contains the data type for the state value of the
aggregate.
- ffunc
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.
 | Important |
|---|
| | 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.
|
- initial_condition
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.
Results
- CREATE
This is the message displayed if the command completes successfully.
Description
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.
| Limitations |
|---|
| | 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.
Examples
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)'
);