xaggr.sgml 4.86 KB
Newer Older
1
<!--
2
$Header: /cvsroot/pgsql/doc/src/sgml/xaggr.sgml,v 1.13 2001/09/10 21:58:47 petere Exp $
3 4
-->

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

8 9 10 11 12
  <indexterm zone="xaggr">
   <primary>aggregate functions</primary>
   <secondary>extending</secondary>
  </indexterm>

13 14 15 16 17 18 19
  <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
20
   function, one selects a data type for the state value,
21 22 23
   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
24 25 26 27
   context of the aggregate.  A <firstterm>final function</firstterm>
   can also be specified, in case the desired output of the aggregate
   is different from the data that needs to be kept in the running
   state value.
28
  </para>
29

30
  <para>
31 32
   Thus, in addition to the input and result data types seen by a user
   of the aggregate, there is an internal state-value data type that
33
   may be different from both the input and result types.
34
  </para>
35

36
  <para>
37
   If we define an aggregate that does not use a final function,
38
   we have an aggregate that computes a running function of
39
   the column values from each row.  "Sum"  is  an
40
   example  of  this  kind  of aggregate.  "Sum" starts at
41
   zero and always adds the current  row's  value  to
42
   its  running  total.  For example, if we want to make a Sum
43 44
   aggregate to work on a data type for complex numbers,
   we only need the addition function for that data type.
45 46 47
   The aggregate definition is:
   
   <programlisting>
48
CREATE AGGREGATE complex_sum (
49
    sfunc = complex_add,
50
    basetype = complex,
51 52
    stype = complex,
    initcond = '(0,0)'
53
);
54

55
SELECT complex_sum(a) FROM test_complex;
56 57 58 59 60 61

         +------------+
         |complex_sum |
         +------------+
         |(34,53.9)   |
         +------------+
62
   </programlisting>
63

64 65 66 67
   (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>
68

69
  <para>
70 71 72 73
   The above definition of "Sum" will return zero (the initial
   state condition) if there are no non-null input values.
   Perhaps we want to return NULL in that case instead --- SQL92
   expects "Sum" to behave that way.  We can do this simply by
74 75
   omitting the <literal>initcond</literal> phrase, so that the initial state
   condition is NULL.  Ordinarily this would mean that the <literal>sfunc</literal>
76 77 78 79 80 81
   would need to check for a NULL state-condition input, but for
   "Sum" and some other simple aggregates like "Max" and "Min",
   it's sufficient to insert the first non-null input value into
   the state variable and then start applying the transition function
   at the second non-null input value.  <productname>Postgres</productname>
   will do that automatically if the initial condition is NULL and
Peter Eisentraut's avatar
Peter Eisentraut committed
82
   the transition function is marked "strict" (i.e., not to be called
83
   for NULL inputs).
84 85 86
  </para>
  
  <para>
87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103
   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, NULLs are ignored.  If you
   need some other behavior for NULL inputs, just define your transition
   function as non-strict, and code it to test for NULL inputs and do
   whatever is needed.
  </para>
  
  <para>
   "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 transition state value.  For example,
   the built-in implementation of <function>avg(float8)</function>
   looks like:

104
   <programlisting>
105 106 107
CREATE AGGREGATE avg (
    sfunc = float8_accum,
    basetype = float8,
108
    stype = float8[],
109 110
    finalfunc = float8_avg,
    initcond = '{0,0}'
111
);
112 113
   </programlisting>
  </para>
114

115 116 117 118 119 120 121 122 123 124 125 126 127 128 129 130 131 132 133 134 135 136 137 138
  <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
139
sgml-local-catalogs:("/usr/lib/sgml/catalog")
140 141 142
sgml-local-ecat-files:nil
End:
-->