25th June 2020: PostgreSQL 13 Beta 2 Released!

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 supported versions listed above instead.

You may want to view the same page for the current version, or one of the 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.

Actually, in order to make it easier to construct useful
aggregates from existing functions, an aggregate can have one or
two separate state values, one or two transition functions to
update those state values, and a *final
function* that computes the actual aggregate result from the
ending state values.

Thus there can be as many as four datatypes involved: the type of the input data items, the type of the aggregate's result, and the types of the two state values. Only the input and result datatypes are seen by a user of the aggregate.

Some state transition functions need to look at each successive input to compute the next state value, while others ignore the specific input value and simply update their internal state. (The most useful example of the second kind is a running count of the number of input items.) The Postgres aggregate machinery defines sfunc1 for an aggregate as a function that is passed both the old state value and the current input value, while sfunc2 is a function that is passed only the old state value.

If we define an aggregate that uses only sfunc1, we have an aggregate that computes a running function of the attribute values from each instance. "Sum" is an example of this kind of aggregate. "Sum" starts at zero and always adds the current instance'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 ( sfunc1 = complex_add, basetype = complex, stype1 = complex, initcond1 = '(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.)

If we define only sfunc2, we are specifying an aggregate that computes a running function that is independent of the attribute values from each instance. "Count" is the most common example of this kind of aggregate. "Count" starts at zero and adds one to its running total for each instance, ignoring the instance value. Here, we use the built-in int4inc routine to do the work for us. This routine increments (adds one to) its argument.

CREATE AGGREGATE my_count ( sfunc2 = int4inc, -- add one basetype = int4, stype2 = int4, initcond2 = '0' ); SELECT my_count(*) as emp_count from EMP; +----------+ |emp_count | +----------+ |5 | +----------+

"Average" is an example of an aggregate that requires both a function to compute the running sum and a function to compute the running count. When all of the instances have been processed, the final answer for the aggregate is the running sum divided by the running count. We use the int4pl and int4inc routines we used before as well as the Postgres integer division routine, int4div, to compute the division of the sum by the count.

CREATE AGGREGATE my_average ( sfunc1 = int4pl, -- sum basetype = int4, stype1 = int4, sfunc2 = int4inc, -- count stype2 = int4, finalfunc = int4div, -- division initcond1 = '0', initcond2 = '0' ); SELECT my_average(salary) as emp_average FROM EMP; +------------+ |emp_average | +------------+ |1640 | +------------+

For further details see **CREATE AGGREGATE**
in *The PostgreSQL User's Guide*.