Using a pointer-valued state for a UDA

A pointer-valued state uses the POINTER data type as the aggregate state.

The mi_pointer data type is the DataBlade API type that represents the SQL data type, POINTER. (For more information, see Pointer data types (Server).) Use a pointer-valued state when an aggregate state might contain more information than can fit into the maximum opaque-type size.

Important: The maximum size of an opaque type is system dependent. On many systems, this limit is 32 KB. Consult your machine notes for the limit on your system. If your aggregate state contains less data than the opaque-type limit, use an opaque-type state instead. For more information, see Manage an opaque-type state.

To use a pointer-valued state for a UDA

Write the appropriate aggregate support functions so that they handle a pointer-valued state.
Declare the state parameters and return values of the aggregate support functions to use the mi_pointer data type. Information on how to handle state management of a pointer-valued state follows.
Register the aggregate support functions with the CREATE FUNCTION statement.
Specify the POINTER data type for the state parameters and return values in the function signatures of the aggregate support functions.

The database server cannot perform state management for a pointer-valued state because it cannot determine the size of the state. The DataBlade API data type mi_pointer is a typedef for the following C data type:

void *

Because this data type is only a pointer, the database server cannot determine how large the aggregate state is. Therefore, it cannot allocate the PER_COMMAND system-allocated state buffer. In this case, the INIT and FINAL aggregate support functions are not optional. They must perform state management of the nonsimple aggregate state, as follows:

The INIT function can allocate and initialize the aggregate state.
The INIT function must also allocate any related resources that the aggregate state might need. Keep in mind that the database server does not interpret the contents of the pointer-valued state. It cannot manage any objects that the state type might reference. Therefore, use states with embedded pointers with caution.
The ITER function must perform an in-place update to initialize or modify a pointer-valued state.
After you allocate the pointer-valued state, the database server passes a pointer to this state to the other aggregate support functions. Initialize or update the pointer-valued state only with an in-place update. For more information, see When to allocate and deallocate a state.
The FINAL function can handle deallocation of resources that the INIT function has set up.
For a pointer-valued state, the FINAL function must always deallocate the aggregate state. If your INIT support function has allocated related resources that the aggregate state uses, make sure that the FINAL function deallocates these resources.

Important: Make sure that you use a memory duration that extends for the life of the user-defined aggregate. A PER_ROUTINE memory duration (the default) expires after one invocation of the ITER function completes. Therefore, you must use a memory duration of at least PER_COMMAND for memory associated with the state.