Implementation Guidelines | Constant 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 with constant expression 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 library function and verify that the mode is TBL_MODE_CONST, indicating that the table function was invoked with constant expression input arguments.
7. Use the value that the FNC_GetPhase library function returns for the FNC_Phase argument to determine the phase in which the function was called and what action to take.

   IF the value is …	THEN the table function …
   TBL_PRE_INIT	must decide whether it should be the controlling copy of all table functions running on other AMP vprocs. If the function wants to provide control context to all other copies of the table function, the function must call FNC_TblControl. If the function does not want to be the controlling copy of the table function, or if the function is designed without the need for a controlling function, the function can simply return and do nothing during this phase. All copies of the table function must complete this phase before any copy 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. Any copy of the function that does not want to participate further must call FNC_TblOptOut. After the function returns, it will not be called again. All copies of the table function must complete this phase before any copy 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. If the result argument is NULL, then set the corresponding indicator_resultargument 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_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 it returns from this phase. The controlling copy of the table function, if one exists, is called with this phase after all other copies of the table function have completed this phase, allowing the controlling function to do any final cleanup or notification to the external world.
   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. Implement the function and set the results to the appropriate value.
9. If the function detects an error, set the:
   • sqlstate argument to an SQLSTATE exception or warning condition before exiting.

     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.