doc/src/sgml/xaggr.sgml

   1 <!-- $PostgreSQL$ -->
   2
   3  <sect1 id="xaggr">
   4   <title>User-Defined Aggregates</title>
   5
   6   <indexterm zone="xaggr">
   7    <primary>aggregate function</primary>
   8    <secondary>user-defined</secondary>
   9   </indexterm>
  10
  11   <para>
  12    Aggregate functions  in <productname>PostgreSQL</productname>
  13    are expressed in terms of <firstterm>state values</firstterm>
  14    and <firstterm>state transition functions</firstterm>.
  15    That is, an aggregate operates using a state value that is updated
  16    as each successive input row is processed.
  17    To define a new aggregate
  18    function, one selects a data type for the state value,
  19    an initial value for the state, and a state transition
  20    function.  The state transition function is just an
  21    ordinary function that could also be used outside the
  22    context of the aggregate.  A <firstterm>final function</firstterm>
  23    can also be specified, in case the desired result of the aggregate
  24    is different from the data that needs to be kept in the running
  25    state value.
  26   </para>
  27
  28   <para>
  29    Thus, in addition to the argument and result data types seen by a user
  30    of the aggregate, there is an internal state-value data type that
  31    might be different from both the argument and result types.
  32   </para>
  33
  34   <para>
  35    If we define an aggregate that does not use a final function,
  36    we have an aggregate that computes a running function of
  37    the column values from each row.  <function>sum</>  is  an
  38    example  of  this  kind  of aggregate.  <function>sum</> starts at
  39    zero and always adds the current  row's  value  to
  40    its  running  total.  For example, if we want to make a <function>sum</>
  41    aggregate to work on a data type for complex numbers,
  42    we only need the addition function for that data type.
  43    The aggregate definition would be:
  44
  45 <screen>
  46 CREATE AGGREGATE sum (complex)
  47 (
  48     sfunc = complex_add,
  49     stype = complex,
  50     initcond = '(0,0)'
  51 );
  52
  53 SELECT sum(a) FROM test_complex;
  54
  55    sum
  56 -----------
  57  (34,53.9)
  58 </screen>
  59
  60    (Notice that we are relying on function overloading: there is more than
  61     one aggregate named <function>sum</>, but
  62    <productname>PostgreSQL</productname> can figure out which kind
  63    of sum applies to a column of type <type>complex</type>.)
  64   </para>
  65
  66   <para>
  67    The above definition of <function>sum</function> will return zero (the initial
  68    state condition) if there are no nonnull input values.
  69    Perhaps we want to return null in that case instead &mdash; the SQL standard
  70    expects <function>sum</function> to behave that way.  We can do this simply by
  71    omitting the <literal>initcond</literal> phrase, so that the initial state
  72    condition is null.  Ordinarily this would mean that the <literal>sfunc</literal>
  73    would need to check for a null state-condition input, but for
  74    <function>sum</function> and some other simple aggregates like
  75    <function>max</> and <function>min</>,
  76    it is sufficient to insert the first nonnull input value into
  77    the state variable and then start applying the transition function
  78    at the second nonnull input value.  <productname>PostgreSQL</productname>
  79    will do that automatically if the initial condition is null and
  80    the transition function is marked <quote>strict</> (i.e., not to be called
  81    for null inputs).
  82   </para>
  83
  84   <para>
  85    Another bit of default behavior for a <quote>strict</> transition function
  86    is that the previous state value is retained unchanged whenever a
  87    null input value is encountered.  Thus, null values are ignored.  If you
  88    need some other behavior for null inputs, do not declare your
  89    transition function as strict; instead code it to test for null inputs and
  90    do whatever is needed.
  91   </para>
  92
  93   <para>
  94    <function>avg</> (average) is a more complex example of an aggregate.
  95    It requires
  96    two pieces of running state: the sum of the inputs and the count
  97    of the number of inputs.  The final result is obtained by dividing
  98    these quantities.  Average is typically implemented by using a
  99    two-element array as the state value.  For example,
 100    the built-in implementation of <function>avg(float8)</function>
 101    looks like:
 102
 103 <programlisting>
 104 CREATE AGGREGATE avg (float8)
 105 (
 106     sfunc = float8_accum,
 107     stype = float8[],
 108     finalfunc = float8_avg,
 109     initcond = '{0,0}'
 110 );
 111 </programlisting>
 112   </para>
 113
 114   <para>
 115    Aggregate functions can use polymorphic
 116    state transition functions or final functions, so that the same functions
 117    can be used to implement multiple aggregates.
 118    See <xref linkend="extend-types-polymorphic">
 119    for an explanation of polymorphic functions.
 120    Going a step further, the aggregate function itself can be specified
 121    with polymorphic input type(s) and state type, allowing a single
 122    aggregate definition to serve for multiple input data types.
 123    Here is an example of a polymorphic aggregate:
 124
 125 <programlisting>
 126 CREATE AGGREGATE array_accum (anyelement)
 127 (
 128     sfunc = array_append,
 129     stype = anyarray,
 130     initcond = '{}'
 131 );
 132 </programlisting>
 133
 134    Here, the actual state type for any aggregate call is the array type
 135    having the actual input type as elements.  The behavior of the aggregate
 136    is to concatenate all the inputs into an array of that type.
 137    (Note: the built-in aggregate <function>array_agg</> provides similar
 138    functionality, with better performance than this definition would have.)
 139   </para>
 140
 141   <para>
 142    Here's the output using two different actual data types as arguments:
 143
 144 <programlisting>
 145 SELECT attrelid::regclass, array_accum(attname)
 146     FROM pg_attribute
 147     WHERE attnum &gt; 0 AND attrelid = 'pg_tablespace'::regclass
 148     GROUP BY attrelid;
 149
 150    attrelid    |              array_accum
 151 ---------------+---------------------------------------
 152  pg_tablespace | {spcname,spcowner,spclocation,spcacl}
 153 (1 row)
 154
 155 SELECT attrelid::regclass, array_accum(atttypid::regtype)
 156     FROM pg_attribute
 157     WHERE attnum &gt; 0 AND attrelid = 'pg_tablespace'::regclass
 158     GROUP BY attrelid;
 159
 160    attrelid    |        array_accum
 161 ---------------+---------------------------
 162  pg_tablespace | {name,oid,text,aclitem[]}
 163 (1 row)
 164 </programlisting>
 165   </para>
 166
 167   <para>
 168    A function written in C can detect that it is being called as an
 169    aggregate transition or final function by seeing if it was passed
 170    an <structname>AggState</> or <structname>WindowAggState</> node
 171    as the function call <quote>context</>,
 172    for example by:
 173 <programlisting>
 174         if (fcinfo-&gt;context &amp;&amp;
 175             (IsA(fcinfo-&gt;context, AggState) ||
 176              IsA(fcinfo-&gt;context, WindowAggState)))
 177 </programlisting>
 178    One reason for checking this is that when it is true for a transition
 179    function, the first input
 180    must be a temporary transition value and can therefore safely be modified
 181    in-place rather than allocating a new copy.  (This is the <emphasis>only</>
 182    case where it is safe for a function to modify a pass-by-reference input.
 183    In particular, aggregate final functions should not modify their inputs in
 184    any case, because in some cases they will be re-executed on the same
 185    final transition value.)
 186    See <literal>int8inc()</> for an example.
 187   </para>
 188
 189   <para>
 190    For further details see the
 191    <xref linkend="sql-createaggregate" endterm="sql-createaggregate-title">
 192    command.
 193   </para>
 194  </sect1>