ate 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 for the state value. This must be a string constant in the form accepted for the data type state_data_type. If not specified, the state value starts out NULL.
- The associated sort operator for a MIN- or MAX-like aggregate function. This is just an operator name (possibly schema-qualified). The operator is assumed to have the same input data types as the aggregate function (which must be a single-argument aggregate function).
The ordinary functions used to define a new aggregate function must be defined first. Note that in this release of Greenplum Database, it is required that the sfunc, ffunc, and prefunc functions used to create the aggregate are defined as IMMUTABLE.
If a user-defined aggregate is used in a window expression, a prefunc function must be defined for the aggregate.
If the value of the Greenplum Database server configuration parameter gp_enable_multiphase_agg is off, only single-level aggregation is performed.
Any compiled code (shared library files) for custom functions must be placed in the same location on every host in your Greenplum Database array (master and all segments). This location must also be in the LD_LIBRARY_PATH so that the server can locate the files.
The following simple example creates an aggregate function that computes the sum of two columns.
Before creating the aggregate function, create two functions that are used as the SFUNC and PREFUNC functions of the aggregate function.
This function is specified as the SFUNC function in the aggregate function.
CREATE FUNCTION mysfunc_accum(numeric, numeric, numeric) RETURNS numeric AS 'select $1 + $2 + $3' LANGUAGE SQL IMMUTABLE RETURNS NULL ON NULL INPUT;
This function is specified as the PREFUNC function in the aggregate function.
CREATE FUNCTION mypre_accum(numeric, numeric ) RETURNS numeric AS 'select $1 + $2' LANGUAGE SQL IMMUTABLE RETURNS NULL ON NULL INPUT;
This CREATE AGGREGATE command creates the aggregate function that adds two columns.
CREATE AGGREGATE agg_prefunc(numeric, numeric) ( SFUNC = mysfunc_accum, STYPE = numeric, PR