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

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;