17.10 - Example: Table UDF with Dynamic Result Row Specification - Advanced SQL Engine - Teradata Database

Teradata Vantage™ - SQL External Routine Programming

Product
Advanced SQL Engine
Teradata Database
Release Number
17.10
Release Date
July 2021
Content Type
Programming Reference
Publication ID
B035-1147-171K
Language
English (United States)

Here is a code excerpt that shows the method signature for a method that implements a table UDF with dynamic result row specification:

public class UDFExample {

   ...

   public static void getStoreData( int          store, /* input arg  */
                              java.lang.Object[] c1,    /* output arg */
                              java.lang.Object[] c2,    /* output arg */
                              java.lang.Object[] c3,    /* output arg */
                              java.lang.Object[] c4)    /* output arg */
   {
        ...
   }

   ...

}

If the JAR file for the UDFExample class is called UDFExample.jar, the following statement registers UDFExample.jar and the UDFExample class with the database, and creates an SQL identifier called JarUDF for the JAR file:

CALL SQLJ.INSTALL_JAR('CJ!udfsrc/UDFExample.jar','JarUDF',0);

The corresponding CREATE FUNCTION statement to define the UDF looks like this:

CREATE FUNCTION getStoreData
  (FileToRead INTEGER)
RETURNS TABLE VARYING COLUMNS (4)
LANGUAGE Java
NO SQL
EXTERNAL NAME 'JarUDF:UDFExample.getStoreData'
PARAMETER STYLE Java;

Here is an example of an INSERT ... SELECT statement that invokes the table function in the FROM clause:

INSERT INTO Sales_Table
SELECT *
FROM TABLE (getStoreData(9005)
   RETURNS (Store INTEGER, Item INTEGER, Quantity INTEGER)) AS tf;