Determine required aggregate support functions

The following figure shows the execution sequence of aggregate support functions for a user-defined aggregate that is not executed in a parallel query.

Figure 1. Execution of a nonparallel user-defined aggregate

begin figure description - This figure is described in the surrounding text. - end figure description

For information about how to execute a user-defined aggregate in parallel queries, see Execute a user-defined aggregate in parallel queries.

These aggregate support functions use the aggregate state to pass shared information between themselves.

As you design your aggregate algorithm, you must determine which of the support functions the algorithm requires. As a minimum, the user-defined aggregate must have an ITER function. It is the ITER function that performs a single iteration of the aggregate on one aggregate argument. Although Figure 1 shows the execution of both the INIT and FINAL support functions, these functions are optional for a user-defined aggregate. In addition, the COMBINE function, though required, often does not require separate code; it can simply call the ITER function.

Writing aggregate support functions that your user-defined aggregate does not require means unnecessary coding and execution time. Therefore, it is important to assess your aggregate for required functions. The following table shows the design decisions in the determination of required aggregate support functions.

Design decision	Aggregate support functions involved	More information
Does the algorithm require initialization or clean-up tasks?	INIT and FINAL	Aggregate support functions that the algorithm requires
Does the aggregate have a simple aggregate state?	INIT, COMBINE, and FINAL	Yes: Handling a simple state No: Handling a nonsimple state
Does the aggregate have a setup argument?	INIT	Implement a setup argument
Does the aggregate return a value whose data type is different from the aggregate state?	FINAL	Return an aggregate result different from the aggregate state
Does the aggregate have special needs to run in a parallel query?	COMBINE	Execute a user-defined aggregate in parallel queries

You can overload the aggregate support functions to provide support for different data types. Any overloaded version of the UDA, however, cannot omit any of the aggregate support functions that the CREATE AGGREGATE statement has listed or use any support function that CREATE AGGREGATE has not specified.

When the database server executes a UDA (regardless of the data type of the aggregate argument), the database expects to find all the aggregate support functions that the CREATE AGGREGATE statement has registered. Therefore, if you omit a support function for one of the reasons in the preceding table, all versions of the aggregate for all data types must be able to execute using only the aggregate support functions that CREATE AGGREGATE specifies.