Using TD_ANYTYPE Parameters as an Alternative to Overloading Function Names - Analytics Database - Teradata Vantage

SQL External Routine Programming

Deployment
VantageCloud
VantageCore
Edition
Enterprise
IntelliFlex
VMware
Product
Analytics Database
Teradata Vantage
Release Number
17.20
Published
June 2022
Language
English (United States)
Last Update
2023-07-11
dita:mapPath
iiv1628111441820.ditamap
dita:ditavalPath
qkf1628213546010.ditaval
dita:id
B035-1147
lifecycle
latest
Product Category
Teradata Vantageā„¢

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 C/C++ Functions that Use the TD_ANYTYPE Type.

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