The AMP vprocs that participate are those containing rows pertaining to the input data provided to the function from the correlated table name specified prior to the table function in the SELECT statement. The assumption is that the input argument determines what to process. Each copy of the function will be called with the same input data repeatedly until the function returns with the no more data condition.
Although it might be possible to read additional data from outside the database in this mode, it probably will not work. This is because the participating AMP vprocs are determined based on where the selected database rows reside. It might not be on all AMP vprocs.
Use the following code excerpt as a guideline to implement your function using FNC_GetPhase:
typedef struct {
scratch_XML xml_ctx;
} local_ctx;
FNC_Phase Phase;
local_ctx *state_info;
if ( FNC_GetPhase(& Phase) != TBL_MODE_VARY)
{
/* Set sqlstate to an error and return. */
strcpy(sqlstate, "U0005");
return;
}
/* depending on the phase decide what to do */
switch(Phase)
{
case TBL_PRE_INIT:
{
/* Allocate scratchpad to retain data between iterations. */
state_info = FNC_TblAllocCtx(sizeof(local_ctx));
break;
}
case TBL_INIT:
{
/* Get scratchpad. */
state_info = FNC_TblGetCtx;
/* Preprocess data here. */
...
break;
}
case TBL_BUILD:
{
/* Get scratchpad and build the result row here. */
state_info = FNC_TblGetCtx;
...
/* Or, if no more rows to build, set sqlstate to "02000". */
strcpy(sqlstate, "02000");
...
break;
}
case TBL_FINI:
{
/* Reset for the next set of data. */
state_info = FNC_TblGetCtx;
...
break;
}
case TBL_END:
{
/* Everyone done. */
...
break;
}
}
If your table function does not build a result row until after it receives all of the input rows for the AMP on which it runs, you can use FNC_GetPhaseEx with the TBL_LASTROW option instead of FNC_GetPhase. This will build the row during the TBL_BUILD_EOF phase. Use the following code excerpt as a guideline to implement your function using FNC_GetPhaseEx.
typedef struct {
scratch_XML xml_ctx;
} local_ctx;
FNC_Phase Phase;
local_ctx *state_info;
if ( FNC_GetPhaseEx(& Phase, TBL_LASTROW) != TBL_MODE_VARY)
{
/* Set sqlstate to an error and return. */
strcpy(sqlstate, "U0005");
return;
}
/* depending on the phase decide what to do */
switch(Phase)
{
case TBL_PRE_INIT:
{
/* Allocate scratchpad to retain data between iterations. */
state_info = FNC_TblAllocCtx(sizeof(local_ctx));
break;
}
case TBL_INIT:
{
/* Get scratchpad. */
state_info = FNC_TblGetCtx;
/* Preprocess data here. */
...
break;
}
case TBL_BUILD:
{
/* Get scratchpad and add data here. */
state_info = FNC_TblGetCtx;
...
/* Set sqlstate to "02000" to indicate not to output rwsult rows here. */
strcpy(sqlstate, "02000");
...
break;
}
case TBL_BUILD_EOF:
{
/* Get scratchpad and build result row here. */
state_info = FNC_TblGetCtx;
...
/* Or, if no more rows to build, set sqlstate to "02000". */
strcpy(sqlstate, "02000");
...
break;
}
case TBL_FINI:
{
/* Reset for the next set of data. */
state_info = FNC_TblGetCtx;
...
break;
}
case TBL_END:
{
/* Everyone done. */
...
break;
}
}
If you want to get a new row on each table function invocation, use FNC_GetPhaseEx with the TBL_NEWROW option. The following code excerpt shows an example of this usage.
{
FNC_Phase Phase;
/* Make sure the function is called in the supported context. */
/* Only ask for the phase on each row. */
switch (FNC_GetPhaseEx(&Phase, TBL_NOOPTIONS))
{
case TBL_MODE_CONST:
strcpy(sqlstate, "U0005");
strcpy((char *) errormsg, "Table function being called in unsupported mode.");
return;
case TBL_MODE_VARY:
switch(Phase)
{
case TBL_PRE_INIT:
/* Ask for a new row on each call to build */
FNC_GetPhaseEx(&Phase,TBL_NEWROW);
break;
case TBL_INIT:
break;
case TBL_BUILD:
*out1 = *in1;
strcpy((char*)out2,(char*)in2);
break;
case TBL_FINI:
break;
case TBL_END:
break;
}
break;
}
}
Note that the above sample code is simply returning the values of the input arguments as output columns, so there is no need for a context object to retain status information between iterations of the table UDF. If you need to use a context object to retain status information between iterations of a table UDF, you must include code for defining, allocating, setting and getting the context object in the appropriate phases accordingly.