14th February 2019: PostgreSQL 11.2, 10.7, 9.6.12, 9.5.16, and 9.4.21 Released!

Development Versions:
devel

PostgreSQL 8.0.26 Documentation | ||||
---|---|---|---|---|

Prev | Fast Backward | Chapter 31. Extending SQL | Fast Forward | Next |

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 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 may 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:

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 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, just do not define your transition function as 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 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}' );

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

CREATE AGGREGATE array_accum ( sfunc = array_append, basetype = anyelement, stype = anyarray, initcond = '{}' );

Here, the actual state type for any aggregate call is the array type having the actual input type as elements.

Here's the output using two different actual data types as arguments:

SELECT attrelid::regclass, array_accum(attname) FROM pg_attribute WHERE attnum > 0 AND attrelid = 'pg_user'::regclass GROUP BY attrelid; attrelid | array_accum ----------+----------------------------------------------------------------------------- pg_user | {usename,usesysid,usecreatedb,usesuper,usecatupd,passwd,valuntil,useconfig} (1 row) SELECT attrelid::regclass, array_accum(atttypid) FROM pg_attribute WHERE attnum > 0 AND attrelid = 'pg_user'::regclass GROUP BY attrelid; attrelid | array_accum ----------+------------------------------ pg_user | {19,23,16,16,16,25,702,1009} (1 row)

For further details see the CREATE AGGREGATE command.