15.00 - NEW - Teradata Database

Teradata Database SQL Functions, Operators, Expressions, and Predicates

Product
Teradata Database
Release Number
15.00
Content Type
Programming Reference
Publication ID
B035-1145-015K
Language
English (United States)
Last Update
2018-09-24

NEW

Purpose  

Constructs a new instance of a structured type and initializes it using the specified constructor method or function.

Syntax  

where

 

Syntax element …

Specifies …

SYSUDTLIB.

the database in which the constructor exists.

Teradata Database only searches the SYSUDTLIB database for UDT constructors, regardless of whether the database name appears in the NEW expression.

constructor_name

the name of the constructor, which is the same as the name of the structured type.

argument

an argument to pass to the constructor.

Parentheses must appear even for constructors that take no arguments.

ANSI Compliance

NEW is partially ANSI SQL:2011 compliant.

The requirement that parentheses appear when the argument list is empty is a Teradata extension to preserve compatibility with existing applications.

Usage Notes  

You can also construct a new instance of a structured type by calling the constructor method or function. For an example, see “Example 1” on page 1347.

To construct a new instance of a dynamic UDT and define the run time composition of the UDT, you must use the NEW VARIANT_TYPE expression. For details, see “NEW VARIANT_TYPE” on page 1350.

Default Constructor

When a structured UDT is created, Teradata Database automatically generates a constructor function with an empty argument list that you can use to construct a new instance of the structured UDT and initialize the attributes to NULL.

Determining Which Constructor is Invoked

Teradata Database uses the rules in the following table to select a UDT constructor:

 

IF the NEW expression specifies a constructor with an argument list that is …

THEN …

empty

If a constructor method that takes no parameters and has the same name as the UDT:

  • Exists in the SYSUDTLIB database, Teradata Database selects that constructor method.
  • Does not exist in the SYSUDTLIB database, Teradata Database selects the constructor function that is automatically generated when the structured UDT is created.
  • not empty

    Teradata Database selects the constructor method in SYSUDTLIB with a parameter list that matches the arguments passed to the constructor in the NEW expression.

    Example  

    Consider the following statement that creates a structured UDT named address:

       CREATE TYPE address
       AS (street VARCHAR(20)
          ,zip CHAR(5))
       NOT FINAL;

    The following statement creates a table that defines an address column named location:

       CREATE TABLE european_sales
         (region INTEGER
         ,location address
         ,sales DECIMAL(8,2));

    The following statement uses NEW to insert an address value into the european_sales table:

       INSERT european_sales (1001, NEW address(), 0);

    Teradata Database selects the default constructor function that was automatically generated for the address UDT because the argument list is empty and the address UDT was created with no constructor method. The default address constructor function initializes the street and zip attributes to NULL.

    The following statement is equivalent to the preceding INSERT statement but calls the constructor function instead of using NEW:

       INSERT european_sales (1001, address(), 0);

    Example  

    To create XML type instances, see Teradata XML.

    Related Topics

     

    FOR more information on …

    SEE …

    creating constructor methods

    CREATE METHOD in SQL Data Definition Language.

    the constructor function that Teradata Database automatically generates when the structured type is created

    CREATE TYPE (Structured Form) in SQL Data Definition Language.

    constructing a new instance of a dynamic UDT and defining the run time composition of the UDT

    “NEW VARIANT_TYPE” on page 1350