CREATE AGGREGATE name ( BASETYPE = input_type [ , SFUNC = sfunc, STYPE = state_type ] [ , FINALFUNC = ffunc ] [ , INITCOND = initial_condition ] )
The name of the aggregate function you are creating.
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.
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.
The data type for the state value of the aggregate.
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.
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.
The message returned when an aggregate is created successfully.
The error returned if the specified state function sfunc, accepting arguments of types state_type and input_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 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)