24th September 2020: PostgreSQL 13 Released!

Development Versions:
devel

This documentation is for an unsupported version of PostgreSQL.

You may want to view the same page for the current version, or one of the other supported versions listed above instead.

You may want to view the same page for the current version, or one of the other supported versions listed above instead.

Aggregate functions in Postgres 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
datatype 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 datatypes seen by a user of the aggregate, there is an internal state-value datatype 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 datatype for complex numbers, we only need the addition function for that datatype. 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 Postgres to figure out which kind of sum to apply to a complex column.)

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 --- SQL92 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. Postgres 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, NULLs 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.

"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 **CREATE
AGGREGATE** in *The PostgreSQL User's
Guide*.