CREATE AGGREGATE SQL - Language Statements This entry needs a lot of work, especially some usefully complex examples. Since I don't yet understand it, I haven't done this. CREATE AGGREGATE Defines a new aggregate function 1998-04-15 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 ] ) 1998-04-15 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 1998-04-15 CREATE Message returned if the command completes successfully. 1998-04-15 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: The arguments to state-transition-function-1 must be (stype1,basetype), and its return value must be stype1. The argument and return value of state-transition- function-2 must be stype2. The arguments to the final-calculation-function must be (stype1,stype2), and its return value must be a POSTGRES base type (not necessarily the same as basetype. The final-calculation-function should be specified if and only if both state-transition functions are specified. 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. 1998-04-15 CREATE AGGREGATE function is a PostgreSQL language extension. Refer to DROP AGGREGATE function to drop aggregate functions. 1998-04-15 There is no CREATE AGGREGATE function on SQL92.