CREATE TYPE Examples | Teradata Vantage - 17.10 - Example: Creating a Structured UDT with 2 Attributes - Advanced SQL Engine - Teradata Database

Teradata Vantage™ - SQL Data Definition Language Syntax and Examples

Product
Advanced SQL Engine
Teradata Database
Release Number
17.10
Release Date
July 2021
Content Type
Programming Reference
Publication ID
B035-1144-171K
Language
English (United States)

The following example creates a structured UDT named address that has two attributes defined with predefined data types: street and zip. This type is also defined with one constructor method (address (VARCHAR(20), CHAR(5)) and three instance methods (city() , state() , and in_state()).

Because no SPECIFIC method names are specified for the instance methods city() and in_state(CHAR(2)) in this example, the system autogenerates and registers the following TVMNameI SPECIFIC names for them in the SYSUDTLIB database:

  • ADDRESS_CITY_R on behalf of the city() method.
  • ADDRESS_IN_STATE_R on behalf of the in_state(CHAR(2)) method.
        CREATE TYPE address AS (
           street VARCHAR(20), 
           zip    CHARACTER(5) ) 
          NOT FINAL
         CONSTRUCTOR METHOD address( VARCHAR(20), CHARACTER(5) ) 
          RETURNS address
          SPECIFIC address_constructor_1
          SELF AS RESULT
          LANGUAGE C
          PARAMETER STYLE SQL
          DETERMINISTIC
          NO SQL,
         INSTANCE METHOD city() 
          RETURNS VARCHAR(20)
          LANGUAGE C
          PARAMETER STYLE SQL
          DETERMINISTIC
          NO SQL,
         METHOD state() 
          RETURNS CHARACTER(2) 
          SPECIFIC address_state
          RETURNS NULL ON NULL INPUT
          LANGUAGE C
          PARAMETER STYLE SQL
          DETERMINISTIC
          NO SQL,
         METHOD in_state(CHARACTER(2)) 
          RETURNS CHARACTER(1)
          LANGUAGE C
          PARAMETER STYLE SQL
          DETERMINISTIC
          NO SQL;