1 <!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN">
5 >CREATE AGGREGATE</TITLE
8 CONTENT="Modular DocBook HTML Stylesheet Version 1.7"><LINK
10 HREF="mailto:pgsql-docs@postgresql.org"><LINK
12 TITLE="PostgreSQL 7.4.1 Documentation"
13 HREF="index.html"><LINK
16 HREF="sql-commands.html"><LINK
19 HREF="sql-copy.html"><LINK
22 HREF="sql-createcast.html"><LINK
25 HREF="stylesheet.css"><META
27 CONTENT="2003-12-22T03:48:47"></HEAD
33 SUMMARY="Header navigation table"
43 >PostgreSQL 7.4.1 Documentation</TH
73 HREF="sql-createcast.html"
81 HREF="sql-createcast.html"
92 NAME="SQL-CREATEAGGREGATE"
102 >CREATE AGGREGATE -- define a new aggregate function</DIV
107 CLASS="REFSYNOPSISDIV"
115 >CREATE AGGREGATE <VAR
121 >input_data_type</VAR
129 >state_data_type</VAR
137 >initial_condition</VAR
151 >CREATE AGGREGATE</TT
152 > defines a new aggregate function. Some aggregate functions
153 for base types such as <CODE
159 >avg(double precision)</CODE
160 > are already provided in the standard
161 distribution. If one defines new types or needs an aggregate function not
162 already provided, then <TT
164 >CREATE AGGREGATE</TT
166 can be used to provide the desired features.
169 > If a schema name is given (for example, <TT
172 myschema.myagg ...</TT
173 >) then the aggregate function is created in the
174 specified schema. Otherwise it is created in the current schema.
177 > An aggregate function is identified by its name and input data type.
178 Two aggregates in the same schema can have the same name if they operate on
179 different input types. The
180 name and input data type of an aggregate must also be distinct from
181 the name and input data type(s) of every ordinary function in the same
185 > An aggregate function is made from one or two ordinary
187 a state transition function
192 and an optional final calculation function
197 These are used as follows:
199 CLASS="PROGRAMLISTING"
203 >( internal-state, next-data-item ) ---> next-internal-state
207 >( internal-state ) ---> aggregate-value</PRE
214 > creates a temporary variable
219 to hold the current internal state of the aggregate. At each input
221 the state transition function is invoked to calculate a new
222 internal state value. After all the data has been processed,
223 the final function is invoked once to calculate the aggregate's return
224 value. If there is no final function then the ending state value
228 > An aggregate function may provide an initial condition,
229 that is, an initial value for the internal state value.
230 This is specified and stored in the database as a column of type
234 >, but it must be a valid external representation
235 of a constant of the state value data type. If it is not supplied
236 then the state value starts out null.
239 > If the state transition function is declared <SPAN
243 then it cannot be called with null inputs. With such a transition
244 function, aggregate execution behaves as follows. Null input values
245 are ignored (the function is not called and the previous state value
246 is retained). If the initial state value is null, then the first
247 nonnull input value replaces the state value, and the transition
248 function is invoked beginning with the second nonnull input value.
249 This is handy for implementing aggregates like <CODE
253 Note that this behavior is only available when
256 >state_data_type</VAR
261 >input_data_type</VAR
263 When these types are different, you must supply a nonnull initial
264 condition or use a nonstrict transition function.
267 > If the state transition function is not strict, then it will be called
268 unconditionally at each input value, and must deal with null inputs
269 and null transition values for itself. This allows the aggregate
270 author to have full control over the aggregate's handling of null values.
273 > If the final function is declared <SPAN
277 be called when the ending state value is null; instead a null result
278 will be returned automatically. (Of course this is just the normal
279 behavior of strict functions.) In any case the final function has
280 the option of returning a null value. For example, the final function for
284 > returns null when it sees there were zero
307 > The name (optionally schema-qualified) of the aggregate function
314 >input_data_type</VAR
318 > The input data type on which this aggregate function operates.
319 This can be specified as <TT
322 > for an aggregate that
323 does not examine its input values (an example is
337 > The name of the state transition function to be called for each
338 input data value. This is normally a function of two arguments,
339 the first being of type <VAR
341 >state_data_type</VAR
345 >input_data_type</VAR
347 for an aggregate that does not examine its input values, the
348 function takes just one argument of type <VAR
350 >state_data_type</VAR
352 the function must return a value of type <VAR
354 >state_data_type</VAR
356 takes the current state value and the current input data item,
357 and returns the next state value.
363 >state_data_type</VAR
367 > The data type for the aggregate's state value.
377 > The name of the final function called to compute the aggregate's
378 result after all input data has been traversed. The function
379 must take a single argument of type <VAR
381 >state_data_type</VAR
383 data type of the aggregate is defined as the return type of this
388 is not specified, then the ending state value is used as the
389 aggregate's result, and the return type is <VAR
391 >state_data_type</VAR
398 >initial_condition</VAR
402 > The initial setting for the state value. This must be a string
403 constant in the form accepted for the data type <VAR
405 >state_data_type</VAR
407 specified, the state value starts out null.
413 > The parameters of <TT
415 >CREATE AGGREGATE</TT
417 written in any order, not just the order illustrated above.
444 >CREATE AGGREGATE</TT
449 > language extension. The SQL
450 standard does not provide for user-defined aggregate function.
461 HREF="sql-alteraggregate.html"
466 HREF="sql-dropaggregate.html"
476 SUMMARY="Footer navigation table"
505 HREF="sql-createcast.html"
521 HREF="sql-commands.html"