Implementation Guidelines - Advanced SQL Engine - Teradata Database

Here are the basic steps you take to write a Java method that implements a table UDF that is invoked using the columns from a derived table as input arguments:

1. Define the parameter list in the order that the CREATE FUNCTION statement specifies the parameters.

   For more information, see Method Signature for Table UDFs.

2. If the table UDF is defined with dynamic result row specification, call Tbl.getColDef() to get the actual number and data types of the result row arguments that the method must return.
3. Call Tbl.getPhase() or Tbl.getPhaseEx() and verify that the return value is Tbl.TBL_MODE_VARY, indicating that the table UDF was invoked with the columns from a derived table as input arguments.
4. Use the value that Tbl.getPhase() or Tbl.getPhaseEx() returns in the phase argument to determine the phase in which Teradata Database invoked the method and what action to take.

   IF the value is …	THEN the method …
   Tbl.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 method contain the first set of data. During this phase, the method has an opportunity to establish overall global context, but should not build any result row. The method continues to the TBL_INIT phase.
   Tbl.TBL_INIT	can call Tbl.allocCtx() to initialize the function context to use as a scratchpad for data between local iterations of the method, if there is a need to do so. The input arguments to the method contain the first set of data. During this phase, the method should not build any result row. The method continues to the TBL_BUILD phase.
   Tbl.TBL_BUILD	should take one of the following actions. If the method has a row to build, then build an output row by filling out each output argument that has a non-null input value. The method remains in the TBL_BUILD phase. If the method has no row to build, then throw an SQLException, setting the SQLState field to "02000" to indicate no data. The method continues to the TBL_FINI phase. If using Tbl.getPhaseEx(), the following actions are done depending on the option specified: If the TBL_NEWROW option is set, then call the 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.TBL_BUILD_EOF (only getPhaseEx() can return this value)	is being called after the last input row on the AMP was passed in. The UDF 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 method should take one of the following actions. If the method has a row to build, then build an output row by filling out each output argument that has a non-null input value. The method remains in the Tbl.TBL_BUILD_EOF phase. If the method has no row to build, then throw an SQLException, setting the SQLState field to "02000" to indicate no data. The method continues to the Tbl.TBL_END phase.
   Tbl.TBL_FINI	can perform any initialization, such as clearing the scratchpad, in preparation for the possible next set of data. If there is more variable input data, the method returns to the Tbl.TBL_INIT phase. Otherwise, the method continues to the TBL_END phase.
   Tbl.TBL_END	can do any required notification to the external world that processing is complete. Teradata Database does not invoke the method again after it returns from this phase.
   Tbl.TBL_ABORT	is being aborted. A method can be called at any time with this phase, which is only entered when a copy of the table functions calls Tbl.abort(). This phase is not entered when the method aborts for an external reason, such as a user abort.

5. If the method detects an error, throw an SQLException. For more information, see Exception Handling.

In general, you should understand the following row states:

• When the first row is passed, during the TBL_PRE_INIT phase.
• When a row is being returned, as indicated by the value of the SQLState field in the TBL_BUILD phase.
• When you want a new row. You can define this using the TBL_NEWROW or TBL_NEWROWEOF options of Tbl.getPhaseEx().
• When the end of file has been encountered. You can determine this using the TBL_LASTROW option of Tbl.getPhaseEx().