16.20 - Type - Teradata Database - Teradata Vantage NewSQL Engine

Teradata Vantage™ SQL Data Definition Language Syntax and Examples

Product
Teradata Database
Teradata Vantage NewSQL Engine
Release Number
16.20
Published
March 2019
Content Type
Programming Reference
Publication ID
B035-1144-162K
Language
English (United States)
Last Update
2019-05-24
ARRAY_name
VARRAY_name
The name of the ARRAY or VARRAY type for which the most recent SQL create text is to be reported. The SQL create text is reported in standard Teradata syntax using the ARRAY keyword.

The ordering and transform functionality for the ARRAY type are not displayed because they are system-generated, so there is no create text to return.

Example: SHOW CAST

The following example shows an instance of the SHOW CAST report where two different casts have been defined for the distinct UDT named distinct_udt_1.

     SHOW CAST sysudtlib.DistinctUdt1;
     *** Text of DDL statement returned.
     *** Total elapsed time was 2 seconds.
     -----------------------------------------------------------
     CREATE CAST (distinct_udt_1 AS VARBYTE(9012)) 
      WITH SPECIFIC FUNCTION d2f;
     CREATE CAST (INTEGER AS distinct_udt_1) 
      WITH SPECIFIC FUNCTION i2d;

Example: SHOW TYPE

The following examples show two instances of the SHOW TYPE report for a UDT.

To report cast information for a UDT, you must use the SHOW CAST statement.
     SHOW TYPE UdtInt;
      *** Text of DDL statement returned.
      *** Total elapsed time was 2 seconds.
     -------------------------------------------------------------
     CREATE TYPE SYSUDTLIB.udt_int AS INTEGER FINAL
     INSTANCE  METHOD intordering ( )
     RETURNS INTEGER
     SPECIFIC udt_int_int_ordering_24a8_r
     LANGUAGE C
     NO SQL
     PARAMETER STYLE SQL
     DETERMINISTIC
     RETURNS NULL ON NULL INPUT,
     INSTANCE  METHOD int_fromsql ( )
     RETURNS INTEGER
     SPECIFIC udt_int_int_fromsql_24a9_r
     LANGUAGE C
     NO SQL
     PARAMETER STYLE SQL
     DETERMINISTIC
     RETURNS NULL ON NULL INPUT;
     SHOW TYPE udtint;
      *** Text of DDL statement returned.
      *** Total elapsed time was 5 seconds.
     ------------------------------------------------------
     CREATE TYPE SYSUDTLIB.udt_int AS INTEGER FINAL
     INSTANCE  METHOD int_ordering ( )
     RETURNS INTEGER
     SPECIFIC udt_int_int_ordering_24a8_r
     LANGUAGE C
     NO SQL
     PARAMETER STYLE SQL
     DETERMINISTIC
     RETURNS NULL ON NULL INPUT,
     INSTANCE  METHOD int_fromsql ( )
     RETURNS INTEGER
     SPECIFIC udt_int_int_fromsql_24a9_r
     LANGUAGE C
     NO SQL
     PARAMETER STYLE SQL
     DETERMINISTIC
     RETURNS NULL ON NULL INPUT;

Example: SHOW TYPE for ARRAY Types

Create the following ARRAY types.

     CREATE TYPE myarray_1 AS 
     VARRAY(5) OF CHARACTER(10);
     CREATE TYPE myarray_2 AS 
     CHARACTER(10) ARRAY[5];

A SHOW TYPE request for the ARRAY type myarray_1 returns the following information. Note that the SQL text returned for CREATE TYPE myarray_1 is reported using ARRAY format rather than the VARRAY format that was used to create the type. Because of this, the CREATE TYPE SQL text returned for myarray_1 is identical to that returned for myarray_2.

     SHOW TYPE myarray_1;
     CREATE TYPE myarray_1 AS CHARACTER(10) ARRAY[5];

A SHOW TYPE request for the ARRAY type myarray_2 returns the following information.

     SHOW TYPE myarray_2;
     CREATE TYPE myarray_2 AS CHARACTER(10) ARRAY[5];