16.20 - SQL Procedure Support For UDTs - Teradata Database - Teradata Vantage NewSQL Engine

Teradata Vantage™ SQL Data Definition Language Detailed Topics

Product
Teradata Database
Teradata Vantage NewSQL Engine
Release Number
16.20
Release Date
March 2019
Content Type
Programming Reference
Publication ID
B035-1184-162K
Language
English (United States)

For correct operation of a UDT within a procedure, the UDT must have its mandatory ordering and transform functionality defined. Additionally, the tosql and fromsql transform routines must be duplicated by an equivalent set of predefined data type-to-UDT and UDT-to-predefined data type implicit cast definitions. You can do this by referencing the same routines in both the CREATE TRANSFORM and CREATE CAST statements.

For distinct UDTs, if you plan to use the system-generated functionality, no additional work is required because the transform and implicit casting functionality have already been defined. For structured UDTs, you must explicitly define the transform and cast functionality, as shown by the following examples.

/* Transform Functionality */
    CREATE TRANSFORM FOR address ClientIO (
      TO SQL WITH SPECIFIC FUNCTION SYSUDTLIB.stringToAddress,
      FROM SQL WITH SPECIFIC METHOD toString);

/* Implicit Cast To Back Up The ToSql Functionality */
    CREATE CAST ( Varchar(100) AS address )
      WITH SPECIFIC FUNCTION SYSUDTLIB.stringToAddress
      AS ASSIGNMENT;

/* Implicit Cast To Back Up The FromSql Functionality */
    CREATE CAST (address AS Varchar(100))
      WITH SPECIFIC METHOD  ToString
      AS ASSIGNMENT;

You can declare an input parameter to have the VARIANT_TYPE UDT data type only within the body of the procedure definition, which means that the system passes the dynamic UDT data type to this parameter position during runtime using the NEW VARIANT_TYPE expression (see “NEW_VARIANT TYPE” in Teradata Vantage™ SQL Operators and User-Defined Functions, B035-1210). You cannot declare an IN parameter data type for the procedure itself to have the VARIANT_TYPE data type.

You cannot declare the data type for any parameter to be TD_ANYTYPE.

Details about SQL procedure support for the following DML statements are documented in Teradata Vantage™ SQL Stored Procedures and Embedded SQL , B035-1148 .
  • SELECT INTO a UDT local variable.

    You can SELECT INTO a UDT local variable. No expressions are allowed in the INTO list.

  • FETCH INTO a UDT local variable.

    You can FETCH INTO a UDT local variable. No expressions are allowed in the INTO list.

    You cannot perform a FETCH INTO operation in which you attempt to fetch into a predefined type nor can you perform a fetch into operation that involves UDT types that are not 100 percent identical. In other words, no implicit casts are applied for these operations.

  • INSERT INTO a UDT local variable.

    You can INSERT INTO a UDT local variable in a table, but you cannot invoke methods on such a local variable if those methods are invoked within the INTO clause.