Implementation Guidelines | Variable Mode Table Function | Teradata Vantage - Implementation Guidelines - Advanced SQL Engine - Teradata Database

Here are the basic steps you take to define a table function that is invoked using the columns from a derived table as input arguments:

1. Define the SQL_TEXT constant.

   For more information, see SQL_TEXT Definition.

2. Include the sqltypes_td.h header file.

   For more information, see Header Files.

3. Include other header files that define macros and variables that the function uses.
4. Define the function parameter list in the order that the CREATE FUNCTION statement specifies the parameters.

   For more information, see Table Function Parameter List.

5. If the table function is defined with dynamic result row specification, call the FNC_TblGetColDef library function to get the actual number and data types of the result row arguments that the table function must return.
6. Call the FNC_GetPhase or FNC_GetPhaseEx library function and verify that the mode is TBL_MODE_VARY, indicating that the table function was invoked using the columns from a derived table as input arguments.
7. Use the value returned by the FNC_GetPhase or FNC_GetPhaseEx library function for the FNC_Phase argument to determine the phase in which the function was called and what action it should perform.

   IF the value is …	THEN the table function …
   TBL_PRE_INIT	is being called for the first time for all the rows that it will be called for. The input arguments to the function contain the first set of data. During this phase, the function has an opportunity to establish overall global context, but should not build any result row. The function continues to the TBL_INIT phase.
   TBL_INIT	should open any connections to external objects, such as files, if there is a need to do so. The input arguments to the function contain the first set of data. During this phase, the function should not build any result row. The function continues to the TBL_BUILD phase.
   TBL_BUILD	should take one of the following actions: If the function has a row to build, then build an output row by filling out each result argument whose corresponding indicator_result argument has an input value of 0 (not NULL). Set the indicator_result arguments for the result values as follows: If the result argument is NULL, then set the corresponding indicator_result argument to -1. If the result argument is not NULL, then set the corresponding indicator_result argument to 0. The function remains in the TBL_BUILD phase. If the function has no row to build, then set the sqlstate argument to "02000" to indicate no data. The function continues to the TBL_FINI phase. If using FNC_GetPhaseEx, the following actions are done depending on the option specified: If the TBL_NEWROW option is set, then call the table function with a new row with a phase of TBL_BUILD. If the TBL_NEWROWEOF option and EOF are set, then call the table function with a new row with a phase of TBL_BUILD. If the TBL_LASTROW option is set, the function remains in the TBL_BUILD phase until it is passed in the last set of data, where it continues to the TBL_BUILD_EOF phase.
   TBL_BUILD_EOF (this value is only returned by FNC_GetPhaseEx)	is being called after the last input row on the AMP was passed in. The function has an opportunity to output a summary row of what it has collected in memory during previous calls when the phase was TBL_BUILD. The function should take one of the following actions. If the function has a row to build, then build an output row by filling out each result argument whose corresponding indicator_result argument has an input value of 0 (not NULL). Set the indicator_result arguments for the result values as follows: If the result argument is NULL, then set the corresponding indicator_result argument to -1. If the result argument is not NULL, then set the corresponding indicator_result argument to 0. The function remains in the TBL_BUILD_EOF phase. If the function has no row to build, then set the sqlstate argument to "02000" to indicate no data. The function continues to the TBL_END phase.
   TBL_FINI	should close any connections, such as file handles, that were opened during the TBL_INIT phase. If there is more variable input data, the function returns to the TBL_INIT phase. Otherwise, the function continues to the TBL_END phase.
   TBL_END	should close all external connections and release any scratch memory it might have allocated. The table function is not called again after this phase.
   TBL_ABORT	is being aborted and should close all external connections and release any previously-allocated memory. A function can be called at any time with this phase, which is only entered when one of the table functions calls the library function FNC_TblAbort. It is not entered when the function is aborted for an external reason, such as a user abort.

8. If the function detects an error, set the:
   • sqlstate argument to an SQLSTATE exception or warning condition before the function exits.

     For more information, see Returning SQLSTATE Values.

   • error_message string to the error message text. The characters must be inside the LATIN character range. The string is initialized to a null-terminated string on input.