TPF sample tpf_rg_1.cxx is similar to the table UDF sample udf_rg_2.cxx. It produces rows of data based on an input parameter.
The number of rows generated is the sum of the values of the rows in a single input table. The output is the same as udf_rg_2.cxx.
The _describe_extfn method accommodates the schema differences between udf_rg_2.cxx and this example. In particular, parameter 1 is a table with one integer column. This code snippet illustrates _describe_extfn:
static void UDF_CALLBACK tpf_rg_describe(
a_v4_extfn_proc_context *ctx )
/****************************************/
{
a_sql_int32 desc_rc;
// The following describes will ensure that the schema defined
// by the user matches the schema supported by this TPF
// This is achieved by telling the server what our schema is
// using describe_xxxx_set methods.
if( ctx->current_state == EXTFNAPIV4_STATE_ANNOTATION ) {
…
…
// Inform the server that the type of parameter 1 is a TABLE
type = DT_EXTFN_TABLE;
desc_rc = ctx->describe_parameter_set
( ctx,
1,
EXTFNAPIV4_DESCRIBE_PARM_TYPE,
&type,
sizeof( type ) );
UDF_CHECK_DESCRIBE( ctx, desc_rc );
// Inform the server that the input table should have a single
// column.
num_cols = 1;
desc_rc = ctx->describe_parameter_set
( ctx,
1,
EXTFNAPIV4_DESCRIBE_PARM_TABLE_NUM_COLUMNS,
&num_cols,
sizeof( num_cols ) );
UDF_CHECK_DESCRIBE( ctx, desc_rc );
// Inform the server that the input table column is an integer
type = DT_INT;
desc_rc = ctx->describe_column_set
( ctx,
1,
1,
EXTFNAPIV4_DESCRIBE_COL_TYPE,
&type,
sizeof( type ) );
UDF_CHECK_DESCRIBE( ctx, desc_rc );
…
…
}
}
In udf_rg_2.cxx, the number of rows generated by the UDF may be available during the describe phase if the value is a constant. A table argument can never be constant, so its value is unavailable until the Execution state. For this reason, no optimizer estimate for the number of rows being generated is provided during the describe phase.
Calls to the describe only during the Annotation state have an effect in this example. Such calls will do nothing in the other states.
The _open_extfn method reads in rows from the input table and sums their values. As in the udf_rg_2.cxx example, the value of the first input parameter is retrieved using get_value. The difference here is that the type of the parameter is a_v4_extfn_table pointer. This code snippet illustrates _open_extfn:
static short UDF_CALLBACK tpf_rg_open(
a_v4_extfn_table_context * tctx )
/***************************************/
{
an_extfn_value value;
tpf_rg_state * state = NULL;
a_v4_extfn_table_context * rs = NULL;
a_sql_uint32 num_to_generate = 0;
// 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;
}
// Open a result set for the input table.
if( !tctx->proc_context->open_result_set( tctx->proc_context,
( a_v4_extfn_table * )value.data,
&rs ) ) {
// Send an error to the client if we could not open the result
// set.
tctx->proc_context->set_error(
tctx->proc_context,
17001,
"Error: Could not open result set on input table." );
return 0;
}
a_v4_extfn_row_block * rbfb = NULL;
a_v4_extfn_row * rfb = NULL;
a_v4_extfn_column_data * cdfb = NULL;
// When using fetch block to read rows from an input table, the
// server will manage the row block allocation.
while( rs->fetch_block( rs, &rbfb ) ) {
// Each successful call to fetch will fill rows in the server
// allocated row block. The number of rows retrieved is
// indicated by the num_rows member.
for( unsigned int i = 0; i < rbfb->num_rows; i++ ) {
rfb = &(rbfb->row_data[i]);
cdfb = &(rfb->column_data[0]);
// Only consider non-null values. To determine null we
// have to use the following logic.
if( (*cdfb->is_null & cdfb->null_mask) != cdfb->null_value ) {
num_to_generate += *(a_sql_int32 *)cdfb->data;
}
}
}
if( !tctx->proc_context->close_result_set( tctx->proc_context, rs ) ) {
// Send an error to the client if we could not close the
// result set.
tctx->proc_context->set_error(
tctx->proc_context,
17001,
"Error: Could not close result set on input table." );
return 0;
}
// Allocate memory for the state using the a_v4_extfn_proc_context
// function alloc.
state = (tpf_rg_state *)
tctx->proc_context->alloc( tctx->proc_context,
sizeof( tpf_rg_state ) );
// Start generating at row zero.
state->next_row = 0;
// Save the value of parameter 1
state->max_row = num_to_generate;
// Save the state on the context
tctx->user_data = state;
return 1;
}
Once you retrieve the table object using get_value, call open_result_set to read in rows of data from the table.
To read rows from the input table, the UDF can use fetch_into or fetch_block. When a UDF is fetching rows from an input table, it becomes a consumer of data. If the consumer (the UDF in this case) wants to be responsible for managing the row block structure, then the consumer must allocate their own row block structure and use fetch_into to retrieve the data. Alternatively, if the consumer wants the producer (the server in this case) to manage the row block structure, then use fetch_block. tpf_rg_1 demonstrates the latter.
Using an open result set, tpf_rg_1 retrieves rows of data from the server by calling fetch_block repeatedly. Each successful call to fetch_block populates the server allocated row block structure with up to num_rows rows. In tpf_rg_1, the value of column 1 for each row is added to a total. As in the udf_rg_2.cxx example, this total is saved in the a_v4_extfn_proc_context state to be used later.