Writing a UDF That Uses TYPE UDT Parameters | CREATE/REPLACE FUNCTION | Vantage - 17.10 - Writing a UDF That Uses VARIANT_TYPE UDT Parameters - Advanced SQL Engine - Teradata Database

Teradata Vantage™ - SQL Data Definition Language Detailed Topics

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

A dynamic UDT is a structured UDT with a type name of VARIANT_TYPE or TD_ANYTYPE. You can write scalar and aggregate UDFs for external routines written in C or C++, but not for external routines written in Java. All FNC library functions that are valid for structured UDTs are also valid for dynamic UDTs. For those FNC library functions that require an attribute name, you must specify the supplied alias name or supplied column name as the corresponding attribute name. See Teradata Vantage™ - SQL External Routine Programming, B035-1147 for details. The maximum number of VARIANT_TYPE input parameters you can declare in a UDF is 8.

Writing a UDF with a dynamic result row specification is the same as writing the same routine with a structured UDT because dynamic UDT parameters are structured type parameters and are passed into the function as a UDT_HANDLE the same way as structured types.

An illustrative example CREATE FUNCTION request for the function definition might look like this.

CREATE FUNCTION udfwithstructured (…)
RETURNS INTEGER 
LANGUAGE C
NO SQL
PARAMETER STYLE TD_GENERAL
EXTERNAL NAME 'CS!UdfWithStructured!td_udf/udfwithstructured.c';

See Teradata Vantage™ - SQL External Routine Programming, B035-1147 for details on how to write C and C++ routines to support UDFs that use dynamic UDTs. See Function Overloading for an example of how dynamic UDTs can be used to reduce or eliminate the need to create multiple UDFs to handle function name overloading.