CREATE AGGREGASQLl- Language Statements (2002-CREATE AGGREGATE(l)
NAME
CREATE AGGREGATE - define a new aggregate function
SYNOPSIS
CREATE AGGREGATE name ( BASETYPE = input_data_type,
SFUNC = sfunc, STYPE = state_type
[ , FINALFUNC = ffunc ]
[ , INITCOND = initial_condition ] )
INPUTS
name The name (optionally schema-qualified) of an aggregate
function to create.
input_data_type
The input data type on which this aggregate function
operates. This can be specified as "ANY" for an
aggregate that does not examine its input values (an
example is count(*)).
sfunc
The name of the state transition function to be called
for each input data value. This is normally a function
of two arguments, the first being of type state_type
and the second of type input_data_type. Alternatively,
for an aggregate that does not examine its input
values, the function takes just one argument of type
state_type. In either case the function must return a
value of type state_type. This function takes the
current state value and the current input data item,
and returns the next state value.
state_type
The data type for the aggregate's state value.
ffunc
The name of the final function called to compute the
aggregate's result after all input data has been
traversed. The function must take a single argument of
type state_type. The output data type of the aggregate
is defined as the return type of this function. If
ffunc is not specified, then the ending state value is
used as the aggregate's result, and the output type is
state_type.
initial_condition
The initial setting for the state value. This must be a
literal constant in the form accepted for the data type
state_type. If not specified, the state value starts
out NULL.
Page 1 (printed 3/24/03)
CREATE AGGREGASQLl- Language Statements (2002-CREATE AGGREGATE(l)
OUTPUTS
CREATE AGGREGATE
Message returned if the command completes successfully.
DESCRIPTION
CREATE AGGREGATE allows a user or programmer to extend
PostgreSQL functionality by defining new aggregate
functions. Some aggregate functions for base types such as
min(integer) and avg(double precision) 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.
If a schema name is given (for example, CREATE AGGREGATE
myschema.myagg ...) then the aggregate function is created
in the specified schema. Otherwise it is created in the
current schema (the one at the front of the search path; see
CURRENT_SCHEMA()).
An aggregate function is identified by its name and input
data type. Two aggregates in the same schema can have the
same name if they operate on different input types. The name
and input data type of an aggregate must also be distinct
from the name and input data type(s) of every ordinary
function in the same schema.
An aggregate function is made from one or two ordinary
functions: a state transition function sfunc, and an
optional final calculation function ffunc. These are used
as follows:
sfunc( internal-state, next-data-item ) ---> next-internal-state
ffunc( internal-state ) ---> aggregate-value
PostgreSQL creates a temporary variable of data type stype
to hold the current internal state of the aggregate. At each
input data item, the state transition function is invoked to
calculate a new internal state value. After all the data has
been processed, the final function is invoked once to
calculate the aggregate's output value. If there is no final
function then the ending state value is returned as-is.
An aggregate function may provide an initial condition, that
is, an initial value for the internal state value. This is
specified and stored in the database as a field of type
text, but it must be a valid external representation of a
constant of the state value data type. If it is not supplied
then the state value starts out NULL.
If the state transition function is declared ``strict'',
Page 2 (printed 3/24/03)
CREATE AGGREGASQLl- Language Statements (2002-CREATE AGGREGATE(l)
then it cannot be called with NULL inputs. With such a
transition function, aggregate execution behaves as follows.
NULL input values are ignored (the function is not called
and the previous state value is retained). If the initial
state value is NULL, then the first non-NULL input value
replaces the state value, and the transition function is
invoked beginning with the second non-NULL input value.
This is handy for implementing aggregates like max. Note
that this behavior is only available when state_type is the
same as input_data_type. When these types are different,
you must supply a non-NULL initial condition or use a non-
strict transition function.
If the state transition function is not strict, then it will
be called unconditionally at each input value, and must deal
with NULL inputs and NULL transition values for itself. This
allows the aggregate author to have full control over the
aggregate's handling of null values.
If the final function is declared ``strict'', then it will
not be called when the ending state value is NULL; instead a
NULL result will be output automatically. (Of course this is
just the normal behavior of strict functions.) In any case
the final function has the option of returning NULL. For
example, the final function for avg returns NULL when it
sees there were zero input tuples.
NOTES
Use DROP AGGREGATE to drop aggregate functions.
The parameters of CREATE AGGREGATE can be written in any
order, not just the order illustrated above.
USAGE
Refer to the chapter on aggregate functions in the
PostgreSQL Programmer's Guide for complete examples of
usage.
COMPATIBILITY
SQL92
CREATE AGGREGATE is a PostgreSQL language extension. There
is no CREATE AGGREGATE in SQL92.
Page 3 (printed 3/24/03)
