Aggregate functions in PostgreSQL are expressed in terms of
state values and state transition functions. That is, an aggregate
operates using a state value that is updated as each successive
input row 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 result of the aggregate is different from the data that
needs to be kept in the running state value.

Thus, in addition to the argument and result data types seen
by a user of the aggregate, there is an internal state-value data
type that might be different from both the argument 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 would be:

(Notice that we are relying on function overloading: there is
more than one aggregate named sum,
but PostgreSQL can figure out
which kind of sum applies to a column of type complex.)

The above definition of sum will
return zero (the initial state condition) if there are no nonnull
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
is sufficient to insert the first nonnull input value into the
state variable and then start applying the transition function at
the second nonnull 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, do not declare your transition
function as strict; instead 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 an array as the state value. For
example, the built-in implementation of avg(float8) looks like:

(float8_accum requires a
three-element array, not just two elements, because it
accumulates the sum of squares as well as the sum and count of
the inputs. This is so that it can be used for some other
aggregates besides avg.)

Aggregate functions can use polymorphic state transition
functions or final functions, so that the same functions can be
used to implement multiple aggregates. See Section
35.2.5 for an explanation of polymorphic functions. Going a
step further, the aggregate function itself can be specified with
polymorphic input type(s) and state type, allowing a single
aggregate definition to serve for multiple input data types. Here
is an example of a polymorphic aggregate:

Here, the actual state type for any aggregate call is the
array type having the actual input type as elements. The behavior
of the aggregate is to concatenate all the inputs into an array
of that type. (Note: the built-in aggregate array_agg provides similar functionality, with
better performance than this definition would have.)

A function written in C can detect that it is being called as
an aggregate transition or final function by calling AggCheckCallContext, for example:

if (AggCheckCallContext(fcinfo, NULL))

One reason for checking this is that when it is true for a
transition function, the first input must be a temporary
transition value and can therefore safely be modified in-place
rather than allocating a new copy. See int8inc() for an example. (This is the
only case where it is
safe for a function to modify a pass-by-reference input. In
particular, aggregate final functions should not modify their
inputs in any case, because in some cases they will be
re-executed on the same final transition value.)

Submit correction

If you see anything in the documentation that is not correct, does not match
your experience with the particular feature or requires further clarification,
please use
this form
to report a documentation issue.