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: |
|||
|
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 |