16.20 - Example: Table UDF With Fixed Result Row Specification - Advanced SQL Engine - Teradata Database

Teradata Vantage™ - SQL External Routine Programming

Product
Advanced SQL Engine
Teradata Database
Release Number
16.20
Release Date
April 2020
Content Type
Programming Reference
Publication ID
B035-1147-162K
Language
English (United States)

Here is a code excerpt that shows the method signature for a method that implements a table UDF:

public class UDFExample {

   ...

   public static void getStoreData( int   storedata,  /* input arg */
                                    int[] storeno,    /* output arg */
                                    int[] itemno,     /* output arg */
                                    int[] quantsold)  /* 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
   (StoreNo   INTEGER
   ,ItemNo    INTEGER
   ,QuantSold INTEGER)
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)) AS tf;