17.10 - Using TD_ANYTYPE Parameters as an Alternative to Overloading Function Names - Advanced SQL Engine - Teradata Database

Teradata Vantageā„¢ - SQL External Routine Programming

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

You can use TD_ANYTYPE parameters to reduce the number of overloaded functions needed to support routines that have constant parameter counts but differing parameter data types. TD_ANYTYPE parameters can accept any system-defined data type or user-defined type (UDT); therefore, you can create a single function that can support a multitude of data types.

Consider the following function, which returns the ASCII numeric value of the first character of the input string:

CREATE FUNCTION ascii ( string_expr TD_ANYTYPE )
   RETURNS TD_ANYTYPE
   LANGUAGE C
   NO SQL
   SPECIFIC ascii
   EXTERNAL NAME 'CS!ascii!ascii.c'
   PARAMETER STYLE SQL;
The UDF writer can write this single function so that it accepts the following as valid argument types:
  • CHARACTER
  • VARCHAR
  • CLOB
  • UDT with a CHARACTER, VARCHAR, or CLOB attribute
  • ARRAY with an element type of CHARACTER or VARCHAR

Similarly, the UDF writer can write the function so that the return type is BYTEINT, SMALLINT, or INTEGER. The function accepts the character set associated with the input string. If no character set is explicitly specified, the default character set is used.

A UDF writer can use TD_ANYTYPE to represent a character data type of any chosen character set, or a decimal data type or interval type with any desired precision. By defining this function, you no longer have to create separate routines to perform the same basic function, such as ascii_char_latin(), ascii_char_unicode(), ascii_varchar_latin(), ascii_varchar_unicode(), ascii_clob_latin(), ascii_clob_unicode(), and so forth.

For more information about TD_ANYTYPE parameters, see Defining Functions that Use the TD_ANYTYPE Type.

For the corresponding C code example for this function, see C Scalar Function Using TD_ANYTYPE Parameters.