CREATE AGGREGATE

Description

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.

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.

Examples

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)