16.20 - Writing a UDF That Uses VARIANT_TYPE UDT Parameters - Teradata Database - Teradata Vantage NewSQL Engine

Teradata Vantage™ SQL Data Definition Language Detailed Topics

Product
Teradata Database
Teradata Vantage NewSQL Engine
Release Number
16.20
Release Date
March 2019
Content Type
Programming Reference
Publication ID
B035-1184-162K
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.