17.10 - 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.10
Release Date
July 2021
Content Type
Programming Reference
Publication ID
B035-1144-171K
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