15.00 - Writing a UDF That Uses VARIANT_TYPE UDT Parameters - Teradata Database

Teradata Database SQL Data Definition Language Detailed Topics

Product
Teradata Database
Release Number
15.00
Content Type
Programming Reference
Publication ID
B035-1184-015K
Language
English (United States)

Writing a UDF That Uses VARIANT_TYPE UDT Parameters

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 SQL External Routine Programming 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 SQL External Routine Programming for details on how to write C and C++ routines to support UDFs that use dynamic UDTs. See “Function Overloading” on page 255 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.