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

SQL External Routine Programming

Product
Advanced SQL Engine
Teradata Database
Release Number
17.10
Published
July 2021
Language
English (United States)
Last Update
2021-07-27
dita:mapPath
rin1593638965306.ditamap
dita:ditavalPath
rin1593638965306.ditaval
dita:id
B035-1147
lifecycle
previous
Product Category
Teradata Vantage™

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;