 must deal with NULL inputs and NULL transition values for itself. This allows the aggregate author to have full control over the aggregate 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 returned automatically. (This is the normal behavior of STRICT functions.) In any case the final function has the option of returning a NULL value. For example, the final function for avg returns NULL when it sees there were zero input rows.
Single argument aggregate functions, such as min or max, can sometimes be optimized by looking into an index instead of scanning every input row. If this aggregate can be so optimized, indicate it by specifying a sort operator. The basic requirement is that the aggregate must yield the first element in the sort ordering induced by the operator; in other words:
SELECT agg(col) FROM tab;
must be equivalent to:
SELECT col FROM tab ORDER BY col USING sortop LIMIT 1;
Further assumptions are that the aggregate function ignores NULL inputs, and that it delivers a NULL result if and only if there were no non-null inputs. Ordinarily, a data type's < operator is the proper sort operator for MIN, and > is the proper sort operator for MAX. Note that the optimization will never actually take effect unless the specified operator is the "less than" or "greater than" strategy member of a B-tree index operator class.
If the optional qualification ORDERED appears, the created aggregate function is an ordered aggregate. In this case, the preliminary aggregation function, prefunc cannot be specified.
An ordered aggregate is called with the following syntax.
name ( arg [ , ... ] [ORDER BY sortspec [ , ...]] )
If the optional ORDER BY is omitted, a system-defined ordering is used. The transition function sfunc of an ordered aggregate function is called on its input arguments in the specified order and on a single segment. There is a new column aggordered in the pg_aggregate table to indicate the aggregate function is defined as an ordered aggregate.
- The name (optionally schema-qualified) of the aggregate function to create.
- An input data type on which this aggregate function operates. To create a zero-argument aggregate function, write * in place of the list of input data types. An example of such an aggregate is count(*).
- The name of the state transition function to be called for each input row. For an N-argument aggregate function, the sfunc must take N+1 arguments, the first being of type state_data_type and the rest matching the declared input data types of the aggregate. The function must return a value of type state_data_type. This function takes the current state value and the current input data values, and returns the next state value.
- The data type for the aggregate state value.
- The name of a preliminary aggregation function. This is a function of two arguments, both of type state_data_type. It must return a value of state_data_type. A preliminary function takes two transition state values and returns a new transition state value representing the combined aggregation. In Greenplum Database, if the result of the aggregate function is computed in a segmented fashion, the preliminary aggregation function is invoked on the individual internal states in order to combine them into an ending internal state.
- Note that this function is also called in hash aggregate mode within a segment. Therefore, if you call this aggregate function without a preliminary function, hash aggregate is never chosen. Since hash aggregate is efficient, consider defining preliminary function whenever possible.
- The name of the final function called to compute the aggregate result after all input rows have been traversed. The function must take a single argument of type state_data_type. The return 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 result, and the return type is state_data_type.
- The initial setting fo