CREATE AGGREGATE

Name

CREATE AGGREGATE — Defines a new aggregate function.
   CREATE AGGREGATE name [AS]
       ([  SFUNC1    = state_transition_function1
       , BASETYPE  = data_type
       , STYPE1    = sfunc1_return_type ]
       [, SFUNC2    = state_transition_function2
       , STYPE2    = sfunc2_return_type ]
       [, FINALFUNC = final_function ]
       [, INITCOND1 = initial_condition1 ]
       [, INITCOND2 = initial_condition2 ]
       )
  

Inputs

name

The name of an aggregate function to create.

state_transition_function1

data_type

sfunc1_return_type

state-transition_function2

sfunc2_return_type

final_function

initial_condition1

initial_condition2

Outputs

CREATE

Message returned if the command completes successfully.

Description

An aggregate function can use up to three functions, two state transition functions, X1 and X2: X1( internal-state1, next-data_item ) ---> next-internal-state1 X2( internal-state2 ) ---> next-internal-state2 and a final calculation function, F: F(internal-state1, internal-state2) ---> aggregate-value These functions are required to have the following properties:

Note that it is possible to specify aggregate functions that have varying combinations of state and final functions. For example, the "count" aggregate requires sfunc2 (an incrementing function) but not sfunc1 or finalfunc, whereas the "sum" aggregate requires sfunc1 (an addition function) but not sfunc2 or finalfunc and the "average" aggregate requires both of the above state functions as well as a finalfunc (a division function) to produce its answer. In any case, at least one state function must be defined, and any sfunc2 must have a corresponding initcond2.

Aggregates also require two initial conditions, one for each transition function. These are specified and stored in the database as fields of type text.

Notes

CREATE AGGREGATE function is a PostgreSQL language extension.

Refer to DROP AGGREGATE function to drop aggregate functions.

Usage

  

Compatibility

SQL92

There is no CREATE AGGREGATE function on SQL92.