The ITER function

The ITER aggregate support function performs the sequential aggregation or iteration for the user-defined aggregation. It merges a single aggregate argument into the partial result, which the aggregate state contains.

The ITER function is a required aggregate support function; that is, you must define an ITER function for every user-defined aggregate. If you do not define an ITER function for your user-defined aggregate, the database server generates an error.

Tip: If the UDA does not have an INIT support function, the ITER support function can initialize the aggregate state. For more information, see Handling a simple state.

If the UDA was registered with the HANDLESNULLS modifier in the CREATE AGGREGATE statement, the database server calls the ITER support function once for each aggregate argument that passed to the user-defined aggregate. Each aggregate argument is one column value. If you omit the HANDLESNULLS modifier from CREATE AGGREGATE, the database server does not call ITER for any NULL-valued aggregate arguments. Therefore, NULL-valued aggregate arguments do not contribute to the aggregate result.

For example, suppose you execute the SQSUM1 user-defined aggregate (which Table 1 describes) in the following query:

SELECT SQSUM1(col3) FROM tab1;

The tab1 table (which Figure 1 defines) contains 6 rows. Therefore, the preceding query (which contains no WHERE clause) causes six invocations of the SQSUM1 ITER function. Each invocation of this ITER function processes one value from the col3 column.

To declare an ITER function as a C function, use the following syntax:

agg_state iter_func(current_state, agg_argument)
   agg_state current_state;
   agg_arg_type agg_argument;

agg_state: The data type of the current and updated aggregate state. After the ITER function merges the agg_argument into the current_state state, it returns a pointer to the updated state.
agg_arg_type: The data type of the agg_argument, which is the data type that the aggregate support function handles for the user-defined aggregate.
agg_argument: A single aggregate argument, usually a column value, which the ITER function merges into the partial result in the current_state aggregate state.
current_state: The current aggregate state, which previous calls to the ITER function and to the INIT function have generated.
iter_func: The name of the ITER aggregate support function.
Important: Make sure that the ITER support function obtains all state information that it needs from its current_state argument. The INIT function cannot maintain additional state information as user data in its MI_FPARAM structure because MI_FPARAM is not shared among the other aggregate support functions. However, the ITER function can store user data in MI_FPARAM that is not part of the aggregate result.

The following figure shows the ITER aggregate support function that handles an INTEGER argument for the SQSUM1 user-defined aggregate (which Table 1 describes).

Figure 1. The ITER aggregate support function for SQSUM1 on INTEGER

/* SQSUM1 ITER support function on INTEGER */
mi_integer iter_sqsum1(state, value)
   mi_integer state;
   mi_integer value;
{
   /* add 'state' and 'value' together */
      return (state + value);
}

For other aggregate support functions of SQSUM1, see Figure 1, Figure 1, and Figure 1.