The MI_FPARAM structure for holding private-state information

The MI_FPARAM structure is associated with the routine sequence. Therefore, for a UDR in a query that executes in parallel, each thread has its own routine sequence and therefore its own MI_FPARAM structure. The first invocation of the routine by each thread would have to perform any initialization. Only UDRs that are declared as parallelizable can be executed in parallel queries. The database server always executes an SQL statement that contains a non-parallelizable UDR serially.

Tip: By default, the CREATE FUNCTION statement registers a UDR as non-parallelizable. To declare a user-defined function as parallelizable, specify the PARALLELIZABLE routine modifier in the CREATE FUNCTION or CREATE PROCEDURE statement.

The MI_FPARAM structure has a memory duration of PER_COMMAND. The database server re-initializes the user-state information that mi_fp_funcstate() references to NULL at the end of the SQL command (for example, at the end of the subquery execution for each outer row from an outer query).

The following code example implements the rowcount() function. This function uses the MI_FPARAM structure to hold a count of the number of rows in a query.

/* The rowcount() function maintains the row count with a variable that
 * is stored as user-state information in the MI_FPARAM structure 
 */
mi_integer rowcount (fparam_ptr)
   MI_FPARAM *fparam_ptr;
{
   mi_integer *count = NULL;

   /* obtain the current user-state pointer from the MI_FPARAM structure */
   count = (mi_integer *)mi_fp_funcstate(fparam_ptr);

   /* if the user-state pointer is NULL, this is the first 
    * invocation of the function
    */
   if ( count == NULL )
      {
      /* allocate memory for the user-state information */
      count = (mi_integer *)mi_dalloc(sizeof(mi_integer), PER_COMMAND);

      /* save user-state pointer in the MI_FPARAM structure */
      mi_fp_setfuncstate(fparam_ptr, (void *)count);

      /* initialize the row counter */
      *count = 0;
      }

   /* increment the row counter */
   (*count)++;
   return (*count);
}

The rowcount() function uses the mi_fp_funcstate() function to obtain the user-state pointer from the MI_FPARAM structure. If this pointer is NULL, rowcount() allocates memory for the count variable and uses the mi_fp_setfuncstate() function to store this pointer as the user-state pointer in the MI_FPARAM structure. It uses the mi_dalloc() function to allocate this memory with a duration of PER_COMMAND so that the database server does not deallocate the memory after the first invocation of the function.

Tip: The rowcount() function in the preceding code example shows how to use the MI_FPARAM structure to hold private user-state information. This method removes the need for global or static variables, which can make a C UDR ill-behaved. The code fragment in Avoid modification of global and static variables shows the bad_rowcount() function, which incorrectly implements a row counter with a static variable.

For the rowcount() function to be used in an SQL statement, it must be registered in the database. The following CREATE FUNCTION statement registers the rowcount() function for use in SQL statements:

CREATE FUNCTION rowcount() RETURNS INTEGER
   EXTERNAL NAME '/usr/lib/db_funcs/count.so(rowcount)'
   LANGUAGE C;

The CREATE FUNCTION statement must omit the MI_FPARAM argument; therefore the registered rowcount() function has no arguments. Suppose that the following query uses the rowcount() function:

SELECT rowcount() from employee;

The query calls the rowcount() function for each row in the employee table. Because the rowcount() function uses the MI_FPARAM structure to hold its state information (the count variable), each query has its own private count variable. Separate queries do not interfere with one another as they might with static and global variables.

Tip: You can also implement the rowcount() function as a user-defined aggregate function. User-defined aggregates do not use the MI_FPARAM structure to hold state information.