<!-- doc/src/sgml/xaggr.sgml -->

 <sect1 id="xaggr">
  <title>User-Defined Aggregates</title>

  <indexterm zone="xaggr">
   <primary>aggregate function</primary>
   <secondary>user-defined</secondary>
  </indexterm>

  <para>
   Aggregate functions in <productname>PostgreSQL</productname>
   are defined in terms of <firstterm>state values</firstterm>
   and <firstterm>state transition functions</firstterm>.
   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 takes the previous state
   value and the aggregate's input value(s) for the current row, and
   returns a new state value.
   A <firstterm>final function</firstterm>
   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.  The final function takes the ending state value
   and returns whatever is wanted as the aggregate result.
   In principle, the transition and final functions are just ordinary
   functions that could also be used outside the context of the
   aggregate.  (In practice, it's often helpful for performance reasons
   to create specialized transition functions that can only work when
   called as part of an aggregate.)
  </para>

  <para>
   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.
  </para>

  <para>
   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.  <function>sum</function>  is  an
   example  of  this  kind  of aggregate.  <function>sum</function> starts at
   zero and always adds the current  row's  value  to
   its  running  total.  For example, if we want to make a <function>sum</function>
   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:

<programlisting>
CREATE AGGREGATE sum (complex)
(
    sfunc = complex_add,
    stype = complex,
    initcond = '(0,0)'
);
</programlisting>

   which we might use like this:

<programlisting>
SELECT sum(a) FROM test_complex;

   sum
-----------
 (34,53.9)
</programlisting>

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

  <para>
   The above definition of <function>sum</function> will return zero
   (the initial state value) if there are no nonnull input values.
   Perhaps we want to return null in that case instead &mdash; the SQL standard
   expects <function>sum</function> to behave that way.  We can do this simply by
   omitting the <literal>initcond</literal> phrase, so that the initial state
   value is null.  Ordinarily this would mean that the <literal>sfunc</literal>
   would need to check for a null state-value input.  But for
   <function>sum</function> and some other simple aggregates like
   <function>max</function> and <function>min</function>,
   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.  <productname>PostgreSQL</productname>
   will do that automatically if the initial state value is null and
   the transition function is marked <quote>strict</quote> (i.e., not to be called
   for null inputs).
  </para>

  <para>
   Another bit of default behavior for a <quote>strict</quote> transition function
   is that the previous state value is retained unchanged