2 $PostgreSQL: pgsql/doc/src/sgml/ref/create_aggregate.sgml,v 1.31 2005/01/04 00:39:53 tgl Exp $
3 PostgreSQL documentation
6 <refentry id="SQL-CREATEAGGREGATE">
8 <refentrytitle id="sql-createaggregate-title">CREATE AGGREGATE</refentrytitle>
9 <refmiscinfo>SQL - Language Statements</refmiscinfo>
13 <refname>CREATE AGGREGATE</refname>
14 <refpurpose>define a new aggregate function</refpurpose>
17 <indexterm zone="sql-createaggregate">
18 <primary>CREATE AGGREGATE</primary>
23 CREATE AGGREGATE <replaceable class="PARAMETER">name</replaceable> (
24 BASETYPE = <replaceable class="PARAMETER">input_data_type</replaceable>,
25 SFUNC = <replaceable class="PARAMETER">sfunc</replaceable>,
26 STYPE = <replaceable class="PARAMETER">state_data_type</replaceable>
27 [ , FINALFUNC = <replaceable class="PARAMETER">ffunc</replaceable> ]
28 [ , INITCOND = <replaceable class="PARAMETER">initial_condition</replaceable> ]
34 <title>Description</title>
37 <command>CREATE AGGREGATE</command> defines a new aggregate
38 function. Some basic and commonly-used aggregate functions are
39 included with the distribution; they are documented in <xref
40 linkend="functions-aggregate">. If one defines new types or needs
41 an aggregate function not already provided, then <command>CREATE
42 AGGREGATE</command> can be used to provide the desired features.
46 If a schema name is given (for example, <literal>CREATE AGGREGATE
47 myschema.myagg ...</>) then the aggregate function is created in the
48 specified schema. Otherwise it is created in the current schema.
52 An aggregate function is identified by its name and input data type.
53 Two aggregates in the same schema can have the same name if they operate on
54 different input types. The
55 name and input data type of an aggregate must also be distinct from
56 the name and input data type(s) of every ordinary function in the same
61 An aggregate function is made from one or two ordinary
63 a state transition function
64 <replaceable class="PARAMETER">sfunc</replaceable>,
65 and an optional final calculation function
66 <replaceable class="PARAMETER">ffunc</replaceable>.
67 These are used as follows:
69 <replaceable class="PARAMETER">sfunc</replaceable>( internal-state, next-data-item ) ---> next-internal-state
70 <replaceable class="PARAMETER">ffunc</replaceable>( internal-state ) ---> aggregate-value
75 <productname>PostgreSQL</productname> creates a temporary variable
76 of data type <replaceable class="PARAMETER">stype</replaceable>
77 to hold the current internal state of the aggregate. At each input
79 the state transition function is invoked to calculate a new
80 internal state value. After all the data has been processed,
81 the final function is invoked once to calculate the aggregate's return
82 value. If there is no final function then the ending state value
87 An aggregate function may provide an initial condition,
88 that is, an initial value for the internal state value.
89 This is specified and stored in the database as a column of type
90 <type>text</type>, but it must be a valid external representation
91 of a constant of the state value data type. If it is not supplied
92 then the state value starts out null.
96 If the state transition function is declared <quote>strict</quote>,
97 then it cannot be called with null inputs. With such a transition
98 function, aggregate execution behaves as follows. Null input values
99 are ignored (the function is not called and the previous state value
100 is retained). If the initial state value is null, then the first
101 nonnull input value replaces the state value, and the transition
102 function is invoked beginning with the second nonnull input value.
103 This is handy for implementing aggregates like <function>max</function>.
104 Note that this behavior is only available when
105 <replaceable class="PARAMETER">state_data_type</replaceable>
107 <replaceable class="PARAMETER">input_data_type</replaceable>.
108 When these types are different, you must supply a nonnull initial
109 condition or use a nonstrict transition function.
113 If the state transition function is not strict, then it will be called
114 unconditionally at each input value, and must deal with null inputs
115 and null transition values for itself. This allows the aggregate
116 author to have full control over the aggregate's handling of null values.
120 If the final function is declared <quote>strict</quote>, then it will not
121 be called when the ending state value is null; instead a null result
122 will be returned automatically. (Of course this is just the normal
123 behavior of strict functions.) In any case the final function has
124 the option of returning a null value. For example, the final function for
125 <function>avg</function> returns null when it sees there were zero
131 <title>Parameters</title>
135 <term><replaceable class="PARAMETER">name</replaceable></term>
138 The name (optionally schema-qualified) of the aggregate function
145 <term><replaceable class="PARAMETER">input_data_type</replaceable></term>
148 The input data type on which this aggregate function operates.
149 This can be specified as <literal>"ANY"</> for an aggregate that
150 does not examine its input values (an example is
151 <function>count(*)</function>).
157 <term><replaceable class="PARAMETER">sfunc</replaceable></term>
160 The name of the state transition function to be called for each
161 input data value. This is normally a function of two arguments,
162 the first being of type <replaceable
163 class="PARAMETER">state_data_type</replaceable> and the second
165 class="PARAMETER">input_data_type</replaceable>. Alternatively,
166 for an aggregate that does not examine its input values, the
167 function takes just one argument of type <replaceable
168 class="PARAMETER">state_data_type</replaceable>. In either case
169 the function must return a value of type <replaceable
170 class="PARAMETER">state_data_type</replaceable>. This function
171 takes the current state value and the current input data item,
172 and returns the next state value.
178 <term><replaceable class="PARAMETER">state_data_type</replaceable></term>
181 The data type for the aggregate's state value.
187 <term><replaceable class="PARAMETER">ffunc</replaceable></term>
190 The name of the final function called to compute the aggregate's
191 result after all input data has been traversed. The function
192 must take a single argument of type <replaceable
193 class="PARAMETER">state_data_type</replaceable>. The return
194 data type of the aggregate is defined as the return type of this
195 function. If <replaceable class="PARAMETER">ffunc</replaceable>
196 is not specified, then the ending state value is used as the
197 aggregate's result, and the return type is <replaceable
198 class="PARAMETER">state_data_type</replaceable>.
204 <term><replaceable class="PARAMETER">initial_condition</replaceable></term>
207 The initial setting for the state value. This must be a string
208 constant in the form accepted for the data type <replaceable
209 class="PARAMETER">state_data_type</replaceable>. If not
210 specified, the state value starts out null.
217 The parameters of <command>CREATE AGGREGATE</command> can be
218 written in any order, not just the order illustrated above.
223 <title>Examples</title>
226 See <xref linkend="xaggr">.
231 <title>Compatibility</title>
234 <command>CREATE AGGREGATE</command> is a
235 <productname>PostgreSQL</productname> language extension. The SQL
236 standard does not provide for user-defined aggregate functions.
241 <title>See Also</title>
243 <simplelist type="inline">
244 <member><xref linkend="sql-alteraggregate" endterm="sql-alteraggregate-title"></member>
245 <member><xref linkend="sql-dropaggregate" endterm="sql-dropaggregate-title"></member>
250 <!-- Keep this comment at the end of the file
255 sgml-minimize-attributes:nil
256 sgml-always-quote-attributes:t
259 sgml-parent-document:nil
260 sgml-default-dtd-file:"../reference.ced"
261 sgml-exposed-tags:nil
262 sgml-local-catalogs:"/usr/lib/sgml/catalog"
263 sgml-local-ecat-files:nil