Writing an Iterator Function

An iterator function is a user-defined function that returns to its calling SQL statement several times, each time returning a value. The database server gathers these returned values together in an active set. To access a value in the active set, you must obtain it from a database cursor. Therefore, an iterator function is a cursor function because it must be associated with a cursor when it is executed.

The database server might execute an iterator function many times. It groups these iterations into the iterator-status values and puts the iterator status for a given iteration in the MI_FPARAM structure. Within an iterator function, you examine the MI_FPARAM structure for an iterator status to determine which actions the iterator function must take.

To specify the different points at which the database server calls an iterator function, the iterator-status flag (of type MI_SETREQUEST) supports the constants in Table 102.

Table 102. Iterator-Status Constants for Calls to an Iterator Function
When Is the Iterator Function Called?	What Does the Iterator Function Do?	Iterator-Status Constant in MI_FPARAM
The first time that the iterator function is called	Initializes the iterations	SET_INIT
Once for each item in the active set	Returns one item of the active set	SET_RETONE
After the last item of the active set is returned	Releases iteration resources	SET_END

To implement an iterator function with a C user-defined function

Declare the iterator function so that its return value has a data type that is compatible with one of the items in the active set.
For example, to return an active set of integer values, declare the iterator function to return the mi_integer data type.
Include an MI_FPARAM structure as the last parameter of the C declaration of the iterator function.
The MI_FPARAM structure holds the iterator status, the iterator-completion flag, and the user-state pointer.
Within the iterator function, obtain the iterator status from the MI_FPARAM structure with the mi_fp_request( ) function.
This function returns the iterator-status constant (SET_INIT, SET_RETONE, or SET_END) that the database server has set for the distinct groups of iterations of the iterator function.
For each of the iterator-status values, take the appropriate actions within the iterator function.

Iterator-Status Value More Information

SET_INIT Initializing the Iterations

SET_RETONE Returning One Active-Set Item

SET_END Releasing Iteration Resources
Register the iterator function as a user-defined function with the ITERATOR routine modifier in the CREATE FUNCTION statement.
Omit the MI_FPARAM parameter from the parameter list when you register the iterator function. For more information, see Registering a C UDR.

Iterator-Status Value	More Information
SET_INIT	Initializing the Iterations
SET_RETONE	Returning One Active-Set Item
SET_END	Releasing Iteration Resources

The Fibonacci series is a list of numbers for which each value is the sum of the previous two. For example, the Fibonacci series up to a stop value of 20 is as follows:

Figure 89 is an implementation of an iterator function named fibGen( ). This function builds an active set that contains a Fibonacci series of numbers up to a specified stop value.

Figure 89. The fibGen( ) Iterator Function

typedef struct fibState1 /* function-state structure */
{
   mi_integer fib_prec1; /* second most recent number in series */
   mi_integer fib_prec2; /* most recent number in series */
   mi_integer fib_ncomputed; /* number computed */
   mi_integer fib_endval; /* stop value */
}fibState;

/* fibGen( ): an iterator function to return the Fibonacci series. 
 * This function takes a stop value as a parameter and returns the
 * Fibonacci series up to this stop value. 
 *
 * Three states of iterator status:
 *    SET_INIT : Allocate the defined user-state structure. 
 *    SET_RETONE : Compute the next number in the series. 
 *    SET_END : Free the user-allocated user-state structure.
 */
mi_integer fibgen(stop_val,fparam)
   mi_integer stop_val;
   MI_FPARAM  *fparam;
{
   mi_integer next;
   fibState *fibstate = NULL;

   switch( mi_fp_request(fparam) ) 
      {
      case SET_INIT:
         next = fibGen_init(stop_val, fparam);
         break;

      case SET_RETONE:
         next = fibGen_retone(fparam);
         fibstate = (fibState *)mi_fp_funcstate(fparam);
         if ( next > fibstate->fib_endval )
            {
            mi_fp_setisdone(fparam, 1);
            next = 0; /* return value ignored */
            }
         break;

      case SET_END:
         next = fibGen_end(fparam); 
         break;
      }
   return (next);
}

The database server calls this fibGen( ) iterator function at the following execution points:

When the iterator function reaches the last item, call the mi_fp_setisdone( ) function to set the iterator-completion flag of the MI_FPARAM structure to one (1). This flag indicates to the database server that it has reached the end condition for the iterator function. The database server no longer needs to continue calling the iterator function with the SET_RETONE iterator-status value. Instead, it calls the iterator function one more time, with the SET_END status value.

In Figure 89, the fibGen( ) iterator function determines if it has reached an end condition after it calls fibGen_retone( ). It makes this determination as follows: