ement the functions prefunc and sfunc, you must ensure that the invocation of sfunc on the segment instances followed by prefunc on the master produce the same result as single-level aggregation that sends all the rows to the master and then applies only the sfunc to the rows.
Greenplum Database creates a temporary variable of data type stype to hold the current internal state of the aggregate function. At each input row, the aggregate argument values are calculated and the state transition function is invoked with the current state value and the new argument values to calculate a new internal state value. After all the rows have been processed, the final function is invoked once to calculate the aggregate return value. If there is no final function then the ending state value is returned as-is.
An aggregate function can provide an optional initial condition, an initial value for the internal state value. This is specified and stored in the database as a value 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, then it cannot be called with NULL inputs. With such a transition function, aggregate execution behaves as follows. Rows with any 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 at the first row with all non-null input values, the first argument value replaces the state value, and the transition function is invoked at subsequent rows with all non-null input values. This is useful for implementing aggregates like max. Note that this behavior is only available when state_data_type is the same as the first input_data_type. When these types are different, you must supply a non-null initial condition or use a nonstrict transition function.
If the state transition function is not declared STRICT, then it will be called unconditionally at each input row, and 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 aggreg