Defines a new aggregate function.
CREATE [ORDERED] AGGREGATE name (input_data_type [ , ... ]) ( SFUNC = sfunc, STYPE = state_data_type [, PREFUNC = prefunc] [, FINALFUNC = ffunc] [, INITCOND = initial_condition] [, SORTOP = sort_operator] )
CREATE AGGREGATE defines a new aggregate function. Some basic and commonly-used aggregate functions such as count, min, max, sum, avg and so on are already provided in Greenplum Database. If one defines new types or needs an aggregate function not already provided, then CREATE AGGREGATE can be used to provide the desired features.
An aggregate function is identified by its name and input data types. Two aggregate functions in the same schema can have the same name if they operate on different input types. The name and input data types of an aggregate function must also be distinct from the name and input data types of every ordinary function in the same schema.
An aggregate function is made from one, two or three ordinary functions (all of which must be IMMUTABLE functions):
- A state transition function sfunc
- An optional preliminary segment-level calculation function prefunc
- An optional final calculation function ffunc
These functions are used as follows:
sfunc( internal-state, next-data-values ) ---> next-internal-state prefunc( internal-state, internal-state ) ---> next-internal-state ffunc( internal-state ) ---> aggregate-value
You can specify PREFUNC as method for optimizing aggregate execution. By specifying PREFUNC, the aggregate can be executed in parallel on segments first and then on the master. When a two-level execution is performed, SFUNC is executed on the segments to generate partial aggregate results, and PREFUNC is executed on the master to aggregate the partial results from segments. If single-level aggregation is performed, all the rows are sent to the master and sfunc is applied to the rows.
Single-level aggregation and two-level aggregation are equivalent execution strategies. Either type of aggregation can be implemented in a query plan. When you implement 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