15.00 - Method Invocation - 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)

Method Invocation

Purpose  

Invokes a method associated with a UDT.

Syntax  

where:

 

Syntax element …

Specifies …

database_name

an optional qualifier for the column_name.

table_name

an optional qualifier for the column_name.

column_name

the name of a distinct or structured UDT column.

udf_name

the name of a UDF that returns a distinct or structured UDT.

argument

an argument to the UDF.

CAST

a CAST expression that converts a source data type to a distinct or structured UDT.

Data type conversions involving UDTs require appropriate cast definitions for the UDTs. To define a cast for a UDT, use the CREATE CAST statement. For more information on CREATE CAST, see SQL Data Definition Language.

expression

an expression that results in a data type that is compatible as the source type of a cast definition for the target UDT.

udt_name

the name of a distinct or structured UDT.

NEW

an expression that constructs a new instance of a structured type and initializes it using the specified constructor method.

For details on NEW, see “NEW” on page 1346.

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 expression.

constructor_name

the name of a constructor method associated with a UDT.

Constructor methods have the same name as the UDT with which they are associated.

argument

an argument to pass to the constructor.

Parentheses must appear even though the argument list may be empty.

method_name

the name of an observer, mutator, or user-defined method (UDM).

You must precede each method name with a period.

argument

an argument to pass to the method.

Parentheses must appear even though the argument list may be empty.

ANSI Compliance

Invocation of UDT methods 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.

Additionally, when a statement specifies an ambiguous expression that can be interpreted as a UDF invocation or a method invocation, Teradata Database gives UDF invocation higher precedence over method invocation. ANSI SQL:2011 gives method invocation higher precedence over UDF invocation.

Observer and Mutator Methods

Teradata Database automatically generates observer and mutator methods for each attribute of a structured UDT. Observer and mutator methods have the same name as the attribute for which they are generated.

 

Method

Description

Invocation Example

Observer

Takes no arguments and returns the current value of the attribute.

“Example” on page 1356

Mutator

Takes one argument and returns a new UDT instance with the specified attribute set to the value of the argument.

“Example 4: Methods and Functions” on page 1345

Usage Notes  

When you invoke a UDM on a UDT, Teradata Database searches the SYSUDTLIB database for a UDM that has the UDT as its first parameter followed by the same number of parameters as the method invocation.

If several UDMs have the same name, Teradata Database must determine which UDM to invoke. For details on the steps that Teradata Database uses, see SQL External Routine Programming.

Restrictions

To use any of the following functions as the first argument of a method invocation, you must enclose the function in parentheses:

  • DATE
  • TIME
  • VARGRAPHIC
  • For example, consider a structured UDT called datetime_record that has a DATE type attribute called start_date. The following statement invokes the start_date mutator method, passing in the result of the DATE function:

       SELECT datetime_record_column.start_date((DATE)) FROM table1;

    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 invokes the zip observer method to retrieve the value of each zip attribute in the location column:

       SELECT location.zip() FROM european_sales;

    Related Topics

     

    FOR more information on …

    SEE …

    creating methods

    CREATE METHOD in SQL Data Definition Language.

    creating UDTs

    CREATE TYPE in SQL Data Definition Language.

    UDM programming

    SQL External Routine Programming.