Inserting Into Structured UDT Columns - Teradata Database

SQL Data Manipulation Language
Product
Teradata Database
Release Number
15.10
Language
English (United States)
Last Update
2018-10-06
dita:id
B035-1146
lifecycle
previous
Product Category
Teradata® Database

Inserting Into Structured UDT Columns

To insert into a structured type column, either of the following must be true:

  • The inserted value must be of the same structured type.
  • There must exist a cast that converts the type of the inserted value to the structured type of the column, and the cast was created with the AS ASSIGNMENT option. See the CREATE CAST statement in SQL Data Definition Language.

    • A structured type can be constructed from any of the following:

  • A NEW specification (constructor method invocation).
  • A constructor function invocation.
  • A UDF invocation that returns the same structured type.
  • Mutator method invocations.
  • An instance method invocation that returns the same structured type.
  • A cast expression where the target data type is the same structured type.
  • A column reference.
  • A null.

    • For example, suppose you have the following two table definitions:

         CREATE TABLE table_1 (
           column1 address, 
           column2 INTEGER)
         UNIQUE PRIMARY INDEX(column2);
     
     
         CREATE TABLE table_2 (
           column1 address, 
           column2 INTEGER)
         UNIQUE PRIMARY INDEX(column2);

    For this case, the information in the following table is true:

     

    Example

    Comment

    		 
    INSERT INTO table_1
    VALUES ('17095 Via Del Campo;92127', 1 );

    Valid if there is a cast with AS ASSIGNMENT that converts a string to the structured type address. 

    USING (addr VARCHAR(30), c2 INTEGER)
    INSERT INTO table_1 (:addr, :c2 );

    Valid if there is a cast with AS ASSIGNMENT that converts a string to the structured type address. 

    INSERT INTO table_1 
    VALUES (NEW address('17095 Via Del Campo', '92127'), 1 );

    Valid invocation of a constructor method.

    		 
    USING (street varchar(20), zip char(5))
    INSERT INTO TAB1 
    VALUES (NEW address(:street, :zip), 2 );

    Valid invocation of a constructor method with host variables.

    		 
    INSERT INTO TAB1 
    VALUES ( NEW address(), 3 );

    Valid invocation of a constructor function.

    		 
    INSERT INTO TAB1 
    VALUES ( NEW address().street('17095 Via Del Campo').zip('92127'), 4 );

    Valid invocations of mutator methods:

    1 The constructor function is invoked.

    The result is an address value whose attribute values are set to their defaults.

    2 The mutator for the street attribute is invoked.

    The result is an updated address value with its street attribute modified.

    3 The mutator for the zip attribute is invoked.

    The result is another updated address value with its zip attribute modified. This result also contains the previous change to the street attribute.

    4 The result address value is inserted into column1 of table_1. 

    INSERT INTO table_1
      SELECT empl.residence(), table_2.column2 
      FROM table_2, table_3;

    Valid if method empl.residence() returns the address data type. 

    INSERT INTO table_1 
      VALUES (SELECT CAST(intl_addr_col AS           address), c2)
    FROM table_x;

    Valid if the cast from intl_addr to the structured type address is valid. 

    INSERT INTO table_1
    SELECT column1, column2 
    FROM table_2;

    Valid column reference.

    		 
    INSERT INTO table_1 
    VALUES (NULL, 8);

    Valid insertion of a null into a structured type column.