CREATE AGGREGATE - Defines 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 of an aggregate function to create.
input_data_type
The input data type on which this aggregate func
tion 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 func
tion 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.
OUTPUTS
CREATE Message returned if the command completes success
fully.
CREATE AGGREGATE allows a user or programmer to extend
Postgres functionality by defining new aggregate func
tions. Some aggregate functions for base types such as
min(integer) and avg(double precision) are already pro
vided 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 fea
tures.
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 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
Postgres 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" in
pg_proc, 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
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 con
trol over the aggregate's handling of NULLs.
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 Post
greSQL Programmer's Guide for complete examples of usage.
COMPATIBILITY
SQL92
CREATE AGGREGATE is a Postgres language extension. There
is no CREATE AGGREGATE in SQL92.
Man(1) output converted with
man2html