17.00 - Example: Java Table UDF - Advanced SQL Engine - Teradata Database

Teradata Vantage™ - SQL Data Definition Language Syntax and Examples

Product
Advanced SQL Engine
Teradata Database
Release Number
17.00
Release Date
September 2020
Content Type
Programming Reference
Publication ID
B035-1144-170K
Language
English (United States)

The following example shows two different ways to create a definition for a table function. The functions behave identically.

Note the void return type and usage of return parameter types for the Java method signature in this example, which is different from that of scalar and aggregate UDFs. The difference is necessary because of the possibility of multiple returned column values from the table UDF.

     CREATE FUNCTION mytableudf (
       p1 INTEGER )
     RETURNS TABLE (c1 INTEGER, 
                    c2 INTEGER)
     LANGUAGE JAVA
     NO SQL
     PARAMETER STYLE JAVA 
     EXTERNAL NAME 'UDF_JAR:UserDefinedFunctions.mytableudf';
     public static void mytableudf(int i, int[] ret1, 
                                   int[] ret2) throws SQLException
     CREATE FUNCTION mytableudf (
       p1 INTEGER )
     RETURNS TABLE (c1 INTEGER, 
                    c2 INTEGER)
     LANGUAGE JAVA
     NO SQL
     PARAMETER STYLE JAVA 
     EXTERNAL NAME 'UDF_JAR:UserDefinedFunctions.mytableudf(int,      int[],int[])';
     public static void mytableudf(int i, int[] ret1, 
                                   int[] ret2) throws SQLException