granicus.if.org Git - postgresql/blob - doc/src/sgml/xaggr.sgml

   1 <!-- $PostgreSQL: pgsql/doc/src/sgml/xaggr.sgml,v 1.35 2007/02/01 00:28:18 momjian Exp $ -->
   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.
 136   </para>
 137
 138   <para>
 139    Here's the output using two different actual data types as arguments:
 140
 141 <programlisting>
 142 SELECT attrelid::regclass, array_accum(attname)
 143     FROM pg_attribute
 144     WHERE attnum &gt; 0 AND attrelid = 'pg_tablespace'::regclass
 145     GROUP BY attrelid;
 146
 147    attrelid    |              array_accum
 148 ---------------+---------------------------------------
 149  pg_tablespace | {spcname,spcowner,spclocation,spcacl}
 150 (1 row)
 151
 152 SELECT attrelid::regclass, array_accum(atttypid)
 153     FROM pg_attribute
 154     WHERE attnum &gt; 0 AND attrelid = 'pg_tablespace'::regclass
 155     GROUP BY attrelid;
 156
 157    attrelid    |   array_accum
 158 ---------------+-----------------
 159  pg_tablespace | {19,26,25,1034}
 160 (1 row)
 161 </programlisting>
 162   </para>
 163
 164   <para>
 165    A function written in C can detect that it is being called as an
 166    aggregate transition or final function by seeing if it was passed
 167    an <structname>AggState</> node as the function call <quote>context</>,
 168    for example by:
 169 <programlisting>
 170         if (fcinfo->context &amp;&amp; IsA(fcinfo->context, AggState))
 171 </programlisting>
 172    One reason for checking this is that when it is true, the first input
 173    must be a temporary transition value and can therefore safely be modified
 174    in-place rather than allocating a new copy.  (This is the <emphasis>only</>
 175    case where it is safe for a function to modify a pass-by-reference input.)
 176    See <literal>int8inc()</> for an example.
 177   </para>
 178
 179   <para>
 180    For further details see the
 181    <xref linkend="sql-createaggregate" endterm="sql-createaggregate-title">
 182    command.
 183   </para>
 184  </sect1>