Aggregate functions in PostgreSQL are expressed as state values and state transition functions. That is, an aggregate can be defined in terms of state that is modified whenever an input item is processed. To define a new aggregate function, one selects a data type for the state value, an initial value for the state, and a state transition function. The state transition function is just an ordinary function that could also be used outside the context of the aggregate. A final function can also be specified, in case the desired output of the aggregate is different from the data that needs to be kept in the running state value.
Thus, in addition to the input and result data types seen by a user of the aggregate, there is an internal state-value data type that may be different from both the input and result types.
If we define an aggregate that does not use a final function,
we have an aggregate that computes a running function of the
column values from each row. Sum is
an example of this kind of aggregate. Sum starts at zero and always adds the current
row's value to its running total. For example, if we want to make
a sum aggregate to work on a data
type for complex numbers, we only need the addition function for
that data type. The aggregate definition is:
CREATE AGGREGATE complex_sum (
sfunc = complex_add,
basetype = complex,
stype = complex,
initcond = '(0,0)'
);
SELECT complex_sum(a) FROM test_complex; complex_sum ------------- (34,53.9)
(In practice, we'd just name the aggregate sum, and rely on PostgreSQL to figure out which kind of sum
to apply to a column of type complex.)
The above definition of sum will
return zero (the initial state condition) if there are no
non-null input values. Perhaps we want to return NULL in that
case instead --- the SQL standard expects sum to behave that way. We can do this simply
by omitting the initcond phrase, so that
the initial state condition is NULL. Ordinarily this would mean
that the sfunc would need to check for a
NULL state-condition input, but for sum and some other simple aggregates like
max and min, it's sufficient to insert the first
non-null input value into the state variable and then start
applying the transition function at the second non-null input
value. PostgreSQL will do that
automatically if the initial condition is NULL and the transition
function is marked "strict" (i.e., not
to be called for NULL inputs).
Another bit of default behavior for a "strict" transition function is that the previous state value is retained unchanged whenever a NULL input value is encountered. Thus, null values are ignored. If you need some other behavior for NULL inputs, just define your transition function as non-strict, and code it to test for NULL inputs and do whatever is needed.
Avg (average) is a more complex
example of an aggregate. It requires two pieces of running state:
the sum of the inputs and the count of the number of inputs. The
final result is obtained by dividing these quantities. Average is
typically implemented by using a two-element array as the
transition state value. For example, the built-in implementation
of avg(float8) looks like:
CREATE AGGREGATE avg (
sfunc = float8_accum,
basetype = float8,
stype = float8[],
finalfunc = float8_avg,
initcond = '{0,0}'
);
For further details see the description of the CREATE AGGREGATE command in the Reference Manual.