This text was automatically converted from troff me macros to HTML. Information may have been lost, added, or changed in the process. Lars Aronsson and Lysator do not guarantee the correctness of this HTML document.
define aggregate *- define a new aggregate
define aggregate agg-name [as] (sfunc1 = state-transition-function1, sfunc2 = state-transition-func2, finalfunc = final-function, initcond1 = initial-condition1, initcond2 = initial-condition2 )
An aggregate requires 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 three properties: The return type of each state-transition-function and the arguments of the final-calculation-function must be the same type (t). The return type of the final-calculation-function must be a POSTGRES base type. The first argument to state-transition-function1 must be of type t, while the second argument must match the data type of the object being aggregated.
Aggregates also require two initial conditions, one for each transition function.
The average aggregate would consist of two state transition functions, a summer and an incrementer. These would hold the internal state of the aggregate through a running sum and and the number of values seen so far. It might accept a new employee salary, increment the count, and add the new salary to produce the next state. The state transition functions must be passed correct initialization values. The final calculation then divides the sum by the count to produce the final answer.
/* Define an aggregate for int4average */
define aggregate avg (sfunc1 = int4add, sfunc2 = int4inc
finalfunc = int4div, initcond1 = "0", initcond2 = "0")