Dynamic Server provides two ways to extend the functionality of aggregates. Use the CREATE AGGREGATE statement only for the second of the two cases.
A built-in aggregate is an aggregate that the database server provides, such as COUNT, SUM, or AVG. These only support built-in data types. To extend a built-in aggregate so that it supports a user-defined data type (UDT), you must create user-defined routines that overload the binary operators for that aggregate. For further information on extending built-in aggregates, see the IBM Informix User-Defined Routines and Data Types Developer's Guide.
A user-defined aggregate is an aggregate that you define to perform an aggregate computation that the database server does not provide. You can use user-defined aggregates with built-in data types, extended data types, or both. To create a user-defined aggregate, use the CREATE AGGREGATE statement. In this statement, you name the new aggregate and specify the support functions that compute the aggregate result. These support functions perform initialization, sequential aggregation, combination of results, and type conversion.
The following example defines a user-defined aggregate named average:
CREATE AGGREGATE average
WITH (
INIT = average_init,
ITER = average_iter,
COMBINE = average_combine,
FINAL = average_final
)
Before you use the average aggregate in a query, you must also use CREATE FUNCTION statements to create the support functions specified in the CREATE AGGREGATE statement.
The following table gives an example of the task that each support function might perform for average.
| Keyword | Support
Function |
Effect |
|---|---|---|
| INIT | average_init | Allocates and initializes an extended data type storing the current sum and the current row count |
| ITER | average_iter | For each row, adds the value of the expression to the current sum and increments the current row count by one |
| COMBINE | average_combine | Adds the current sum and the current row count of one partial result to the other and returns the updated result |
| FINAL | average_final | Returns the ratio of the current sum to the current row count and converts this ratio to the result type |