- Define the SQL_TEXT constant.
For more information, see SQL_TEXT Definition.
- Include the sqltypes_td.h header file.
For more information, see Header Files.
- Include other header files that define macros and variables that the function uses.
- Define the function parameter list in the order that the CREATE FUNCTION statement specifies the parameters.
For more information, see Table Function Parameter List.
- 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.
- 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.
- 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.
Value Meaning TBL_PRE_INIT Table function must decide whether to 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 Table function must 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 FNC_TblOptOut returns, the table function is not called again.
All copies of the table function must complete this phase before any copy continues to the TBL_BUILD phase.
TBL_BUILD Table function must take one of the following actions: If the function has a row to build:- 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, set the sqlstate argument to "02000" to indicate no data. The function continues to the TBL_END phase.
TBL_END Table function must close external connections and release any allocated scratch memory. The table function is not called again after returning 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 System is ending table function. Table function must close external connections and release any allocated scratch memory. Table function can be called at any time of this phase, which is entered only when a table function calls library function FNC_TblAbort. - Implement the function and set the results to the appropriate value.
- 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.
- sqlstate argument to an SQLSTATE exception or warning condition before exiting.
Here are the basic steps you take to define a table function that is invoked with constant expression input arguments: