Custom Aggregate Functions

If one defines new types or needs an aggregate function not already provided, then CREATE AGGREGATE can be used: CREATE AGGREGATE name ( BASETYPE = input_data_type, SFUNC = sfunc, STYPE = state_type [ , FINALFUNC = ffunc ] [ , INITCOND = initial_condition ] ) An aggregate function is made from one or two ordinary functions: a state transition function sfunc, and an optional final calculation function ffunc. These are used as follows: sfunc( internal-state, next-data-item ) ---> next-internal-state ffunc( internal-state ) ---> aggregate-value sfunc: The name of the state transition function to be called for each input data value. This is normally a function of two arguments, the first being of type state_type and the second of type input_data_type.

PostgreSQL creates a temporary variable of data type stype to hold the current internal state of the aggregate. At each input data item, the state transition function is invoked to calculate a new internal state value. After all the data has been processed, the final function is invoked once to calculate the aggregate's output value. If there is no final function then the ending state value is returned as-is.

DROP AGGREGATE will remove all references to an existing aggregate definition.

DROP AGGREGATE name ( type ) type: The input data type of an existing aggregate function, or * if the function accepts any input type.

Example:

CREATE AGGREGATE tsum ( BASETYPE = INT, SFUNC = msum, STYPE = INT, INITCOND = 0) ; SELECT tsum(semester) FROM stud ; tsum ------ 8 (1 row) DROP AGGREGATE tsum ( INT ) ;