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