r 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, PREFUNC = mypre_accum, INITCOND = 0 );
The following commands create a table, adds some rows, and runs the aggregate function.
create table t1 (a int, b int) DISTRIBUTED BY (a); insert into t1 values (10, 1), (20, 2), (30, 3); select agg_prefunc(a, b) from t1;
This EXPLAIN command shows two phase aggregation.
explain select agg_prefunc(a, b) from t1; QUERY PLAN -------------------------------------------------------------------------- Aggregate (cost=1.10..1.11 rows=1 width=32) -> Gather Motion 2:1 (slice1; segments: 2) (cost=1.04..1.08 rows=1 width=32) -> Aggregate (cost=1.04..1.05 rows=1 width=32) -> Seq Scan on t1 (cost=0.00..1.03 rows=2 width=8) (4 rows)
CREATE AGGREGATE is a Greenplum Database language extension. The SQL standard does not provide for user-defined aggregate functions.