Using a single-valued state for a UDA

A single-valued state uses a built-in SQL data type to hold the aggregate state. Use a single-valued state for an aggregate state that can fit into a built-in data type but whose data type does not match that of the aggregate argument.

Built-in SQL data types are provided data types. For more information, see the section on data types in the HCL Informix® Guide to SQL: Reference.

To use a single-valued state for a UDA:

Write the appropriate aggregate support functions so that they handle a single-valued state.
Declare the state parameters and return values of the aggregate support functions to use the DataBlade API data type that corresponds to the built-in SQL data type that your state requires. For a list of these data type correspondences, see Table 1.
Register the aggregate support functions with the CREATE FUNCTION statement.
Specify the built-in SQL data type as the data type of the state parameters and return values in the function signatures of the aggregate support functions.

The database server can perform memory management for a single-valued state because it can determine the size of a built-in data type. Therefore, you do not need to allocate memory for a single-valued state in the INIT support function.

In the ITER function, you can initialize or update a single-valued state in either of the following ways:

In-place state update
To modify the state, change the value (or values) of the DataBlade API variable that the database server has passed into the ITER function as the state argument.
Allocate a new state
To modify the state, declare a local variable or allocate PER_ROUTINE memory for a new variable and put the new values into this variable.

For more information, see When to allocate and deallocate a state.

For a single-valued state, the FINAL support function does not need to perform any state-management tasks. However, it must convert to the data type of the final aggregate state to that of the aggregate result. For more information, see Return an aggregate result different from the aggregate state.