 <chapter id="xaggr">
  <title>Extending <acronym>SQL</acronym>: Aggregates</title>

  <para>
   Aggregate functions  in <productname>Postgres</productname> 
   are expressed as <firstterm>state values</firstterm>
   and <firstterm>state transition functions</firstterm>.
   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.
  </para>

  <para>
   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
   <firstterm>final function</firstterm> that computes the
   actual aggregate result from the ending state values.
  </para>

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

  <para>
   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 <productname>Postgres</productname>
   aggregate machinery defines <acronym>sfunc1</acronym> for
   an aggregate as a function that is passed both the old state
   value and the current input value, while <acronym>sfunc2</acronym>
   is a function that is passed only the old state value.
  </para>

  <para>
   If we define an aggregate that  uses  only <acronym>sfunc1</acronym>,
   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:
   
   <programlisting>
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)   |
         +------------+
   </programlisting>

   (In practice, we'd just name the aggregate "sum", and rely on
   <productname>Postgres</productname> to figure out which kind
   of sum to apply to a complex column.)
  </para>

  <para>
   If we define only <acronym>sfunc2</acronym>, 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 
   <acronym>int4inc</acronym> routine to do
   the work for us.  This routine increments (adds one to)
   its argument.
   
   <programlisting>
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         |
         +----------+
   </programlisting>
  </para>
  
  <para>
   "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 <acronym>int4pl</acronym> and
   <acronym>int4inc</acronym> routines we used
   before as well as the <productname>Postgres</productname>  integer  division  
   routine,  <acronym>int4div</acronym>,  to  compute the division of the sum by
   the count.
   
   <programlisting>
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        |
         +------------+
   </programlisting>
  </para>

  <para>
   For further details see
   <!--
   Not available in the Programmer's Guide
  <xref endterm="sql-createaggregate-title"
   linkend="sql-createaggregate-title">.
   -->
   <command>CREATE AGGREGATE</command> in
   <citetitle>The PostgreSQL User's Guide</citetitle>.
  </para>
 </chapter>

<!-- Keep this comment at the end of the file
Local variables:
mode:sgml
sgml-omittag:nil
sgml-shorttag:t
sgml-minimize-attributes:nil
sgml-always-quote-attributes:t
sgml-indent-step:1
sgml-indent-data:t
sgml-parent-document:nil
sgml-default-dtd-file:"./reference.ced"
sgml-exposed-tags:nil
sgml-local-catalogs:("/usr/lib/sgml/catalog")
sgml-local-ecat-files:nil
End:
-->
