Implementing Sample Table UDF udf_rg

The sample table UDF called udf_rg_1 illustrates how a v4 Table UDF can generate n rows of data. The implementation of the table UDF is in the samples directory in udf_rg_1.cxx.

Determine the input and output for the table UDF.
This example produces n rows of data based on the value of an input parameter. The input is a single integer parameter and the output is rows that consist of a single column of type integer.

The CREATE PROCEDURE statement required to define this procedure is:
```
CREATE OR REPLACE PROCEDURE udf_rg_1( IN num INT )
RESULT( c1 INT )
EXTERNAL NAME ‘udf_rg_1@libv4apiex’
```
Declare the library as a v4 library.
In this example, udf_rg_1.cxx includes the extfnapiv4.h header file:
```
#include “extfnapiv4.h”
```
To inform the server that this library contains v4 table UDFs, this function export is defined in udf_main.cxx:
```
a_sql_uint32 SQL_CALLBACK extfn_use_new_api( void )
/*************************************************/
{
    return EXTFN_V4_API;
}
```

Define the a_v4_extfn_proc descriptor.

This declares the necessary descriptor in udf_rg_1.cxx:

static a_v4_extfn_proc udf_rg_descriptor = 
{
    NULL,            // _start_extfn
    NULL,            // _finish_extfn
    udf_rg_evaluate, // _evaluate_extfn
    udf_rg_describe, // _describe_extfn
    NULL,            // _leave_state_extfn
    NULL,            // _enter_state_extfn
    NULL,            // Reserved: must be NULL
    NULL             // Reserved: must be NULL
};

Define a library entry point function.
This callback function declares the main entry point function. It simply returns a pointer to the a_v4_proc_descriptor variable udf_rg_descriptor.
```
extern "C"
a_v4_extfn_proc * SQL_CALLBACK udf_rg_1_proc()
/******************************************/    
{
    return &udf_rg_descriptor;
}
```
Define how the server gets row information from the table UDF.
This declares the a_v4_extfn_table_func descriptor that is used to tell the server how to retrieve row data from the table UDF:
```
static a_v4_extfn_table_func udf_rg_table_funcs = 
{
    udf_rg_open,       // _open_extfn
    udf_rg_fetch_into, // _fetch_into_extfn
    NULL,              // _fetch_block_extfn
    NULL,              // _rewind_extfn
    udf_rg_close,      // _close_extfn
    NULL,              // Reserved: must be NULL
    NULL               // Reserved: must be NULL
};
```
In this example, the _fetch_into_extfn function transfers row data to the server. This is the easiest data transfer method to understand and implement. This document refers to data transfer methods as rowblock data exchange. There are two rowblock data exchange functions: _fetch_into_extfn and _fetch_block_extfn.

At runtime, when the _evaluate_extfn function is called, the UDF publishes the table functions descriptor by setting the result set parameter. To do this, the UDF must create an instance of a_v4_extfn_table:
```
static a_v4_extfn_table udf_rg_table = {
    &udf_rg_table_funcs,	// Table function descriptor
    1                    // number_of_columns
};
```
This structure contains a pointer to the udf_rg_table_funcs structure and the number of columns in the result set. This table UDF produces a single column in its result set.
Implement the a_v4_extfn_proc structure functions.
In this example, the required function _describe_extfn function does not do anything. Other examples demonstrate how a table UDF can use the describe function:
```
static void UDF_CALLBACK udf_rg_describe(
       a_v4_extfn_proc_context *ctx )
/*****************************************************************/
{
    // This required function is not needed in this simple example.
}
```
The _evaluate_extfn method sends the server information about getting the result set from the UDF. This is done by calling the a_v4_extfn_proc_context method set_value on argument 0. Argument 0 represents the return value, which for a table UDF is a DT_EXTFN_TABLE. This method constructs an_extfn_value structure, setting the data type to DT_EXTFN_TABLE and setting the value pointer of this to point to the a_v4_extfn_table object created in step 5. For table UDFs, the type must always be DT_EXTFN_TABLE.
```
static void UDF_CALLBACK udf_rg_evaluate( 
	      a_v4_extfn_proc_context *ctx,
        void *args_handle ) 
/***********************************/
{
    an_extfn_value	result_table = { &udf_rg_table,
					 sizeof( udf_rg_table ),
					 sizeof( udf_rg_table ),
					 DT_EXTFN_TABLE };
    
    // Tell the server what functions table functions are being
    // implemented and how many columns are in our result set.
    ctx->set_value( args_handle, 0, &result_table );
}
```

Implement the a_v4_extfn_table_func structure functions.

In this example, the table UDF needs to read in the parameter passed in that contains the number of rows to generate, and cache this information to be used later. Because the _open_extfn method is called for each new value that the parameter has, this is an appropriate place to get this information.

In addition to the total number of rows to generate, the table UDF must also remember the next row to generate. When the server begins fetching rows from the table UDF, it may need to repeatedly call the _fetch_into_extfn method. This means that the table UDF must remember the last row that was generated.

This structure is created in udf_rg_1.cxx to contain the state information between calls:

struct udf_rg_state {
    a_sql_int32	next_row;  // The next row to produce
    a_sql_int32	max_row;   // The number of rows to generate.
};

The open method first reads in the value of argument 1 using the a_v4_proc_context method get_value. An instance of udf_rg_state is allocated using the a_v4_proc_context function alloc. table UDFs should use the memory management functions (alloc and free) on the a_v4_proc_context structure whenever possible to manage their memory. The state object is then saved in the user_data field of a_v4_proc_context. Memory stored in this field is available to the table UDF until execution finishes.

static short UDF_CALLBACK udf_rg_open(
	      a_v4_extfn_table_context * tctx )
/***************************************/    
{
    an_extfn_value 	value;
    udf_rg_state *	state = NULL;

    // Read in the value of the input parameter and store it away in a
    // state object.  Save the state object in the context.
    if( !tctx->proc_context->get_value( tctx->args_handle,
					1,
					&value ) ) {

	// Send an error to the client if we could not get the value.
	tctx->proc_context->set_error( 
	    tctx->proc_context,
	    17001,
	    "Error: Could not get the value of parameter 1" );

	return 0;
    }

    // Allocate memory for the state using the a_v4_extfn_proc_context
    // function alloc.
    state = (udf_rg_state *)
	tctx->proc_context->alloc( tctx->proc_context, 
				   sizeof( udf_rg_state ) );
    
    // Start generating at row zero.
    state->next_row = 0;
    
    // Save the value of parameter 1
    state->max_row = *(a_sql_int32 *)value.data;

    // Save the state on the context
    tctx->user_data = state;

    return 1;
}

The _fetch_info_extfn method returns row data to the server. This method is called repeatedly until it returns false. For this example, the table UDF retrieves the state information from the user_data field of the a_v4_extfn_proc_context object to determine the next row to generate and the total number of rows to generate. This method is free to generate up to the maximum number of rows indicated in the rowblock structure passed in.

For this example, the table UDF generates a single column of type INT. It copies the data for the next_row saved in the state into the data pointer of the first column. Each time through the loop, the table UDF copies a new value into the data pointer and stops when either the maximum number of rows to produce is reached or the row block is full.

static short UDF_CALLBACK udf_rg_fetch_into(
	      a_v4_extfn_table_context *tctx, 
	      a_v4_extfn_row_block *rb)
/*******************************************/
{
    udf_rg_state	*state = (udf_rg_state *)tctx->user_data;

    // Because we are implementing fetch_into, the server has provided
    // us with a row block. We need to inform the server how many rows
    // this call to _fetch_into has produced.
    rb->num_rows = 0;

    // The server provided row block structure contains a max_rows
    // field.  This field is the maximum number of rows that this row
    // block can handle.  We can not exceed this number.  We will also
    // stop producing rows when we have produced the number of rows
    // required as per the max_row in the state.
    while( rb->num_rows < rb->max_rows && state->next_row < state->max_row ) {

	       // Get the current row from the row block data.
	       a_v4_extfn_row  	&row = rb->row_data[ rb->num_rows ];

	       // Get the column data for the current row.
	       a_v4_extfn_column_data	&col0 = row.column_data[ 0 ];

	       // Copy the integer value for the next row to generate 
	       // into the column data for the current row.
	       memcpy( col0.data, &state->next_row, col0.max_piece_len );

	       state->next_row++;
	       rb->num_rows++;
    }

    // If we produced any rows, return true.
    return( rb->num_rows > 0 );
}

The table UDF calls the _close_extfn method once per new value for the parameters, after all the rows have been fetched. In other words, for each _open_extfn call, there is a subsequent _close_extfn call. In this example, the table UDF must free the memory allocated during the _open_extfn call which it does by retrieving the state from the user_data field of a_v4_extfn_proc_context object and calling the free method.

static short UDF_CALLBACK udf_rg_close(
	      a_v4_extfn_table_context *tctx)
/*************************************/    
{
    udf_rg_state *	state = NULL;

    // Retrieve the state that was saved in user_data
    state = (udf_rg_state *)tctx->user_data;

    // Free the memory for the state using the a_v4_extfn_proc_context
    // function free.
    tctx->proc_context->free( tctx->proc_context, state );
    tctx->user_data = NULL;

    return 1;
}

Implementing Sample Table UDF udf_rg_1