Properties of aggregates

Aggregate name

Use the new object prefix to begin the name of your new aggregate. The aggregate name cannot be the same as another user-defined routine or aggregate unless you are overloading an existing user-defined aggregate.

Iteration type

Initialization parameter

The initialization parameter is an argument in the initialization function to customize the aggregation computation. For example, if you defined an aggregate to return the top n values of a query, your initialization parameter can be 3 to select the top three.

State type

The state type holds the partial result information during the aggregation computation. The database server never accesses the state type, so it can be any data type or structure appropriate for the partial results. For example, if you have an aggregate that returns the three largest values from a query result set, your state type can be an array of three integers.

If you are overloading an existing aggregate, the state type must be different for each aggregate.

Select the POINTER data type from the data type list to indicate that your data type is not known to the database server.

Initialization function

The initialization function initializes the data structures required by the rest of the aggregation computation. For example, it can set up smart large objects or temporary files for storing intermediate results.

The initialization function can take an optional initialization parameter to customize the aggregate computation.

The initialization function is not required for simple binary operators that have a state type that is the same as the iteration type.

Iteration function

The iteration function merges a single value of the iteration type with the partial result of the state type and returns the updated partial result.

You can specify whether the iteration function handles null values. If it does not, any null values returned by the query are ignored. If it does handle null values, the iteration function includes them in its computations.

Combine function

The database server can break up the aggregation computation into several pieces and compute them in parallel. Each piece is computed sequentially; then the results from all pieces are combined into a single result by using the combine function. The parallel computation must give the same result as the sequential computation.

The combine function merges partial results of the state type and returns the updated partial result. It can also perform cleanup work by releasing resources acquired by the initialization function.

The combine function can be the same as the iterator function if the aggregate is derived from a simple binary operator whose result type is the same as the state type.

Final function

The final function converts a partial result of the state type into the result type. It can also release resources acquired by the initialization function to clean up the result type.

If you do not include a final function, the database server returns the final state type. The final function is not required for aggregates derived from simple binary operators whose result type is the same as the state type.