15.10 - data type - Teradata Database

Teradata Database SQL Data Definition Language Syntax and Examples

prodname
Teradata Database
vrm_release
15.10
created_date
December 2015
category
Programming Reference
featnum
B035-1144-151K

A parenthetical comma-separated list of data types, including UDTs the VARIANT_TYPE UDT type, the TD_ANYTYPE UDT type, and optional parameter names for the variables to be passed to the function parameter. The data types are required to differentiate between overloaded functions with the same name.

For information about naming database objects, see SQL Fundamentals, B035-1141.

parameter_name data type
Parameter names must be unique within a table UDF definition.
The maximum number of parameters a UDF accepts is 128.
You must specify opening and closing parentheses even if no parameters are to be passed to the function.
Teradata Database supports the system-defined parameter type VARIANT_TYPE for input parameters in a table function.
Note the following rules and restrictions for specifying the system-defined parameter type VARIANT_TYPE for an input parameter in a table function.
  • You can specify VARIANT_TYPE as the data type for a parameter whose external routine is written in C or C++.
  • You cannot specify VARIANT_TYPE as the data type for a parameter whose external routine is written in Java.
Note that while UDFs support a maximum of 128 parameters, each VARIANT_TYPE input parameter supports a maximum of another 128 parameters. Because you can declare a maximum of 8 UDF input parameters to have the VARIANT_TYPE data type, the actual number of UDF input parameters Teradata Database supports when you specify the maximum number of VARIANT_TYPE parameters is 1,144.
You can specify TD_ANYTYPE as the data type for any parameter of a table function. The system-defined data type TD_ANYTYPE can assume any system-defined data type. Its attributes are determined when the function is executed.
See SQL Data Types and Literals, B035-1143 for more information about the TD_ANYTYPE parameter data type and see SQL External Routine Programming, B035-1147 for more information about how to code C and C++ routines to take advantage of TD_ANYTYPE.
The following rules and restrictions apply to specifying the system-defined parameter type TD_ANYTYPE for an input or result parameter.
  • You can specify TD_ANYTYPE as the data type for an input parameter in a scalar, aggregate, or table function written in C, C++, or Java.
  • You can specify TD_ANYTYPE as the data type for a result parameter in a scalar or aggregate function written in C, C++, or Java.
  • You cannot specify TD_ANYTYPE as the data type for a result parameter in a table function.
You cannot specify a character server data set of KANJI1. Otherwise, Teradata Database returns an error to the requestor.
BLOB and CLOB parameter data types must be represented by a locator, in contrast with RETURN TABLE clause LOB column data types, which must not be represented by locators. For a description of locators, see “USING Request Modifier” in SQL Data Manipulation Language, B035-1146.
Teradata Database does not support in-memory LOB parameters: an AS LOCATOR phrase must be specified for each LOB parameter.
Note, however, that whenever a LOB that requires data type conversion is passed to a table UDF, the LOB must be materialized for the conversion to take place.
If you specify one parameter name, then you must specify names for all the parameters passed to the function.
If you do not specify parameter names, the system assigns unique names to them in the form P1, P2, …, Pn. These names are used in the COMMENT statement, displayed in the report produced by the HELP FUNCTION statement, and appear in the text of error messages. See COMMENT (Comment Placing Form) and HELP ONLINE.
The data type associated with each parameter is the type of the parameter or returned value. All Teradata Database data types are valid. Character data can also specify a CHARACTER SET clause.
For data types that take a length or size specification, like BYTE, CHARACTER, DECIMAL, VARCHAR, and so on, the size of the parameter indicates the largest number of bytes that can be passed.

Example: Variable Reference Table Function Called from a Derived Table

This example shows a variable reference table function (see “Variable Mode Table Function Body” in SQL External Routine Programming, B035-1147) equijoined to a derived table.

The derived table dt1 is created first as a subset of tbl1, then a column dt1.c1 for each row is passed to the table function. The resultant rows of the table function are combined with the selected rows of the derived table by means of the WHERE condition.

The relevant function definition is as follows:

    CREATE FUNCTION tudf1 (FLOAT, INTEGER, INTEGER)
      RETURN TABLE (
       c1 DECIMAL,
       c2 INTEGER,
       c3 INTEGER) 
      … ;

The SELECT request that uses this table function is as follows.

    SELECT dt1.c1, tf2.c2, tf2.c3 
    FROM (SELECT c1, c2 
          FROM tbl1 
          WHERE c1 < 500) AS dt1,
           TABLE (tudf1(45.6, 193, dt1.c1)) AS tf2 (nc1, nc2, nc3)
           WHERE dt1.c1 = tf2.nc1); 

Example: Table Function With a UDT Parameter that Returns a Column With a UDT Data Type

This example creates a table UDF with a UDT parameter that returns a distinct UDT column named udtc4.

First create a new distinct data type named TABLEINT:

     CREATE TYPE TABLEINT AS INTEGER FINAL;

Now create a new table function named fnc_tbf001udt that declares the input parameter p2 with a data type of TABLEINT:

     CREATE  FUNCTION fnc_tbf001udt(
       p1 INTEGER, 
       p2 TABLEINT)
     RETURNS TABLE (c1    INTEGER,  
                    c2    INTEGER,  
                    c3    VARCHAR(3),  
                    udtc4 TABLEINT)
     LANGUAGE C  
     NO SQL  
     PARAMETER STYLE SQL  
     EXTERNAL NAME 'CS!fnc_tbf001udt!fnc_tbf001udt.c'; 

Now use the table function in a SELECT request to return results in the form of a table:

     SELECT * 
     FROM TABLE(fnc_tbf001udt(1, 1)) AS t1 
     WHERE t1.c2 IN (0,1);

See SQL External Routine Programming, B035-1147 for information about how to code external functions similar to fnc_tbf001udt.