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.
