Defines a new aggregate function within the database.
CREATE AGGREGATE name ( BASETYPE = input_type [ , SFUNC = sfunc, STYPE = state_type ] [ , FINALFUNC = ffunc ] [ , INITCOND = initial_condition ] )
name
The name of the aggregate function you are creating.
input_type
The input data type on which the new function will operate. If the aggregate function
ignores input values (as the count()
function does), you can use the
ANY
string constant as the data type.
sfunc
The name of the function you wish to be called to handle all non-NULL
input data values. These functions usually follow the same format, having
two arguments. The first argument is of the state_type
data type, and the second of the input_type
data type. If the aggregate does not examine input
values, it will take only one argument of type state_type
. Either way, the function must return a value of type
state_type
.
state_type
The data type for the state value of the aggregate.
ffunc
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
.
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 data type is determined by state_type
.
initial_condition
The initial value of the aggregate function’s state value. This is a literal constant
of the the same data type as state_type
. The state
value will be initialized to NULL
if initial_condition
is not specified.
CREATE
The message returned when an aggregate is created successfully.
ERROR: AggregateCreate: function "
sfunc(state_type, input)
" does not exist
The error returned if the specified state function sfunc
, accepting arguments of types state_type
and input
, does
not exist.
ERROR: AggregateCreate: function "
sfunc(state_type)
" does not exist
The error returned if the specified sfunc
,
accepting one argument of type state_type
, does
not exist. This error should only be displayed if the input_type
is set to ANY.
Use the CREATE AGGREGATE
command to define new aggregate functions in
PostgreSQL. Some commonly used aggregate functions are already included with PostgreSQL, such
as min()
, avg()
, and max()
. See Chapter 5, for more on PostgreSQL’s built-in functions.
Aggregate functions are characterized primarily by their input data type. It is possible for two or more aggregate functions to exist with the same name, as long as they accept different data types. This is called function overloading.
In order to avoid confusion, do not try to create normal functions with the same name and input type as an aggregate. If you do, aggregate functions will receive precedence.
An aggregate function is comprised of either one or two normal functions. The required
function is the state transition function (the sfunc
),
and the optional function is the finalization function (ffunc
).
PostgreSQL uses a temporary stype
variable that
is updated by the state transition function for every input row it receives. If you have
defined a finalization function for your aggregate, it will be called to calculate the output
value after all data has been processed; otherwise, the ending state value is returned without
further processing.
Aggregate functions can also set an initial value for the internal state value; this is
known as an initial condition, and is specified with the INITCOND
keyword. PostgreSQL stores this value in the database as a value of type
text
, but it must represent a constant of the same data type as the state
value defined by the STYPE
keyword. This value will be initialized to
NULL
if nothing is supplied.
If the state transition function was created with the isstrict
attribute (see the reference entry titled “CREATE FUNCTION”), it cannot be called with
NULL
parameters. Transition functions declared in this manner cause
aggregate execution to behave differently then normal; specifically, all NULL
input parameters are ignored, and the function is not called. The previous
state value is retained, and the aggregate function continues to process input values.
Furthermore, 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()
. Note that this behavior will only occur
when state_type
is the same as input_type
. If these types are different, you must either 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 for each input value. This causes it to handle NULL
values
and NULL
transition values by itself, which allows the aggregate author to
have more control over the aggregate’s handling of NULL
input.
If the finalization function of an aggregate is declared strict
, it
will not be called if the ending state value is a NULL
value; instead, it
will output a NULL
result automatically.
The following example defines an aggregate function named sum()
, for
use with the text
data type. This aggregate calls the textcat(text,text)
function built into PostgreSQL to return a concatenated “sum”
of all the text found in its input values:
booktown=# CREATE AGGREGATE sum ( BASETYPE = text, booktown(# SFUNC = textcat, booktown(# STYPE = text, booktown(# INITCOND = '' ); CREATE booktown=# SELECT sum(title || ' ') FROM books WHERE title ~ '^L'; sum ------------------------------- Little Women Learning Python (1 row)
18.219.134.198