15.10 - 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
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];