Implementing an iterator function with a C user-defined function

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 Initialize the iterations

SET_RETONE Return one active-set item

SET_END Release 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 Register a C UDR.

Iterator-status value	More information
SET_INIT	Initialize the iterations
SET_RETONE	Return one active-set item
SET_END	Release 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:

0, 1, 1, 2, 3, 5, 8, 13

The following code fragment 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 1. 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:

Once, to initialize the calculation of the Fibonacci series of numbers.
At this point, the database server has set the iterator status to SET_INIT and fibGen() calls the fibGen_init() function (see Figure 1).
Repeatedly, to calculate each number in the series until a number exceeds the stop value.
As long as the number is less than the stop value, the database server sets the iterator status to SET_RETONE and fibGen() calls the fibGen_retone() function (see Figure 1).
Once, to deallocate resources that the iterator function uses.
At this point, the database server has set the iterator status to SET_END and fibGen() calls the fibGen_end() function (see Figure 1).

Tip: For end users to be able to use an iterator function within an SQL statement, you must register the iterator function with the ITERATOR routine modifier of the CREATE FUNCTION statement. For more information, see Call an iterator function from an SQL statement.

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. 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.

Important: Make sure that you include a call to the mi_fp_setisdone() function within your iterator function that sets the iterator-completion flag to one. Without this call, the database server never reaches an end condition for the iteration, which causes it to iterate the function in an infinite loop.

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

If this number is greater than the user-specified stop value (in the fib_endval field of the user-state information), the end condition was reached.
The fibGen() function calls the mi_fp_setisdone() function to set the iterator-completion flag to 1. The function then exits with a return value of zero. However, this last return value of 0 is not returned as part of the active set. The database server calls the next iteration of fibGen() with an iterator-status value of SET_END.
If the next Fibonacci number is less than or equal to the stop value, the end condition was not reached.
The function returns this next number in the Fibonacci series to the active set. The database server calls the next iteration of fibGen() with an iterator-status value of SET_RETONE.