2 $Header: /cvsroot/pgsql/doc/src/sgml/xaggr.sgml,v 1.15 2001/11/21 06:09:45 thomas Exp $
6 <title>Extending <acronym>SQL</acronym>: Aggregates</title>
8 <indexterm zone="xaggr">
9 <primary>aggregate functions</primary>
10 <secondary>extending</secondary>
14 Aggregate functions in <productname>PostgreSQL</productname>
15 are expressed as <firstterm>state values</firstterm>
16 and <firstterm>state transition functions</firstterm>.
17 That is, an aggregate can be
18 defined in terms of state that is modified whenever an
19 input item is processed. To define a new aggregate
20 function, one selects a data type for the state value,
21 an initial value for the state, and a state transition
22 function. The state transition function is just an
23 ordinary function that could also be used outside the
24 context of the aggregate. A <firstterm>final function</firstterm>
25 can also be specified, in case the desired output of the aggregate
26 is different from the data that needs to be kept in the running
31 Thus, in addition to the input and result data types seen by a user
32 of the aggregate, there is an internal state-value data type that
33 may be different from both the input and result types.
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. <function>Sum</> is an
40 example of this kind of aggregate. <function>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 aggregate to work on a data type for complex numbers,
44 we only need the addition function for that data type.
45 The aggregate definition is:
48 CREATE AGGREGATE complex_sum (
55 SELECT complex_sum(a) FROM test_complex;
64 (In practice, we'd just name the aggregate <function>sum</function>, and rely on
65 <productname>PostgreSQL</productname> to figure out which kind
66 of sum to apply to a complex column.)
70 The above definition of <function>Sum</function> will return zero (the initial
71 state condition) if there are no non-null input values.
72 Perhaps we want to return NULL in that case instead --- SQL92
73 expects <function>Sum</function> to behave that way. We can do this simply by
74 omitting the <literal>initcond</literal> phrase, so that the initial state
75 condition is NULL. Ordinarily this would mean that the <literal>sfunc</literal>
76 would need to check for a NULL state-condition input, but for
77 <function>Sum</function> and some other simple aggregates like <function>Max</> and <function>Min</>,
78 it's sufficient to insert the first non-null input value into
79 the state variable and then start applying the transition function
80 at the second non-null input value. <productname>PostgreSQL</productname>
81 will do that automatically if the initial condition is NULL and
82 the transition function is marked <quote>strict</> (i.e., not to be called
87 Another bit of default behavior for a <quote>strict</> transition function
88 is that the previous state value is retained unchanged whenever a
89 NULL input value is encountered. Thus, NULLs are ignored. If you
90 need some other behavior for NULL inputs, just define your transition
91 function as non-strict, and code it to test for NULL inputs and do
96 <function>Average</> is a more complex example of an aggregate. It requires
97 two pieces of running state: the sum of the inputs and the count
98 of the number of inputs. The final result is obtained by dividing
99 these quantities. Average is typically implemented by using a
100 two-element array as the transition state value. For example,
101 the built-in implementation of <function>avg(float8)</function>
105 CREATE AGGREGATE avg (
106 sfunc = float8_accum,
109 finalfunc = float8_avg,
116 For further details see
118 Not available in the Programmer's Guide
119 <xref endterm="sql-createaggregate-title"
120 linkend="sql-createaggregate-title">.
122 <command>CREATE AGGREGATE</command> in
123 <citetitle>The PostgreSQL User's Guide</citetitle>.
127 <!-- Keep this comment at the end of the file
132 sgml-minimize-attributes:nil
133 sgml-always-quote-attributes:t
136 sgml-parent-document:nil
137 sgml-default-dtd-file:"./reference.ced"
138 sgml-exposed-tags:nil
139 sgml-local-catalogs:("/usr/lib/sgml/catalog")
140 sgml-local-ecat-files:nil