VARIANT_TYPE Data Type | Data Types and Literals | Teradata Vantage - VARIANT_TYPE Data Type - Advanced SQL Engine - Teradata Database

SQL Data Types and Literals

Product
Advanced SQL Engine
Teradata Database
Release Number
17.05
17.00
Published
June 2020
Language
English (United States)
Last Update
2021-01-22
dita:mapPath
zsn1556242031050.ditamap
dita:ditavalPath
lze1555437562152.ditaval
dita:id
B035-1143
lifecycle
previous
Product Category
Teradata Vantage™

An input parameter data type that can be used to package and pass in a varying number of parameters of varying data types to a UDF as a single UDF input parameter.

Syntax

parameter_name VARIANT_TYPE
parameter_name
The name of an input parameter declared in a UDF.

ANSI Compliance

VARIANT_TYPE is a Teradata extension to the ANSI SQL standard.

Description

Teradata Database supports dynamic UDTs, which are structured UDTs with a pre-assigned UDT type name of VARIANT_TYPE, and whose structured type attribute composition is determined at run time.

The VARIANT_TYPE UDT can be used only as a UDF input parameter data type. When you declare a UDF input parameter to be of VARIANT_TYPE data type, you can use that parameter to pass in a varying number of parameters of varying types to the UDF.

The additional parameters are packaged and passed to the UDF as a single structured UDT. The number of parameters and the data types of the parameters are determined at runtime. Therefore, you can vary the parameter composition each time you invoke the routine.

To declare an instance of a VARIANT_TYPE UDT and define the runtime composition of the UDT, use the NEW VARIANT_TYPE expression. For more information, see “NEW VARIANT_TYPE” in Teradata Vantage™ - SQL Functions, Expressions, and Predicates, B035-1145. For information on declaring a UDF input parameter to be a VARIANT_TYPE data type, see “CREATE/REPLACE FUNCTION” in Teradata Vantage™ - SQL Data Definition Language Syntax and Examples, B035-1144.

VARIANT_TYPE UDT High-Level Features

UDT Classification Structured Type UDT
UDT Type Name Variant_Type
Associated Methods (UDMs) None
Ordering Functionality Implements full map ordering. The ordering routine will return the value of the first attribute of the VARIANT_TYPE UDT.
The first attribute of the VARIANT_TYPE UDT cannot be one of the following: LOB, UDT, or LOB-UDT.
Transform Functionality None

You cannot import or export the VARIANT_TYPE UDT. You cannot use this data type in SQL statements that result with the data type being returned to the caller as output. Otherwise, you will receive an SQL error stating that no such UDT is defined.

Cast Functionality None

Maximum Number of Supported Parameters

UDFs support a maximum of 128 parameters. In addition, each VARIANT_TYPE input parameter accommodates up to 128 parameters, and you can declare up to 8 UDF input parameters to be of VARIANT_TYPE data type. This increases the number of supported UDF input parameters from 128 to 120 + (8 X 128) = 1144 input parameters per UDF.

Restrictions

  • You can specify the VARIANT_TYPE UDT as a data type of input parameters to UDFs. You cannot use them as the data type for UDF result parameters or for any other purpose.
  • You can use the VARIANT_TYPE data type only with UDFs written in C or C++. You cannot use them with SQL UDFs or UDFs written in Java.
  • All restrictions that apply to structured UDTs also apply to the VARIANT_TYPE UDT.

Example: VARIANT_TYPE Data Type

This example shows a user-defined aggregate function with an input parameter named parameter_1 declared as a VARIANT_TYPE data type.

CREATE TYPE INTEGERUDT AS INTEGER FINAL;
CREATE FUNCTION udf_agch002002dynudt (parameter_1 VARIANT_TYPE)
RETURNS INTEGERUDT CLASS AGGREGATE (4)
LANGUAGE C
NO SQL
EXTERNAL NAME 'CS!udf_agch002002dynudt!udf_agch002002dynudt.c'
PARAMETER STYLE SQL;

Related Topics

FOR information on … SEE …
declaring VARIANT_TYPE input parameters in UDFs “CREATE/REPLACE FUNCTION” in Teradata Vantage™ - SQL Data Definition Language Syntax and Examples, B035-1144.
declaring an instance of a VARIANT_TYPE UDT and defining the runtime composition of the UDT “NEW VARIANT_TYPE” in Teradata Vantage™ - SQL Functions, Expressions, and Predicates, B035-1145.
writing UDFs that use VARIANT_TYPE input parameters Teradata Vantage™ - SQL External Routine Programming , B035-1147 .