17.10 - System-Generated Observer and Mutator Methods - Advanced SQL Engine - Teradata Database

Teradata Vantage™ - SQL Data Definition Language Detailed Topics

Product
Advanced SQL Engine
Teradata Database
Release Number
17.10
Release Date
July 2021
Content Type
Programming Reference
Publication ID
B035-1184-171K
Language
English (United States)

When you create a structured UDT, the system automatically generates an observer method and a mutator method for each of its attributes.

To query the value of an attribute, use its observer method, which has the same name as the attribute.

An observer method returns the current value of its associated attribute. Observer methods are invoked without parameters, for example:

     SELECT column_name.attribute_name()
     FROM udt_table;

The empty parentheses after the observer method name are required for use with Vantage. Although the current ANSI SQL standard defines empty parentheses as optional, empty parentheses are mandatory to preserve compatibility with existing applications.

To update the value of an attribute or to insert a row that contains a column defined with a structured UDT, use its mutator method, which has the same name as the attribute.

Mutator methods are invoked with one parameter, the new value of the attribute. The mutator method returns a new instance of the structured UDT that is identical to the input UDT except for the specified attribute, whose value is set to the value of the parameter.

The following example shows a the mutator invocation in an UPDATE request:

     UPDATE udt_table
     SET column_name = column_name.attribute_name(new_attribute_value);

You can invoke multiple mutator methods together to initialize different attributes in one expression.

The following example illustrates this usage with an INSERT request:

     INSERT INTO table1
     VALUES (column_1_value,      udt_name().attr_1(attr_1_value).attr_2(attr_2_value));

The UDT expression in this INSERT request is executed the following order:

  1. The constructor function udt_name() is invoked.

    The result is a default UDT instance.

  2. The mutator method for attribute1 is invoked.

    The result is a UDT instance with its attr_1 set to attr_1_value.

  3. The mutator method attr_2 is invoked.

    The result is a UDT instance with its attr_2 set to attr_2_value.

  4. The final result is a UDT instance with its attr_1 set to attr_1_value and attr_2 set to attr_2_value.

Note that mutators are methods, with the same routine resolution rules as any other method or UDF.

UDFs are limited with respect their predefined data type parameter compatibility. For example, UDFs do not inherently support most of the implicit Teradata predefined data type-to-predefined data type conversions. The best practice for invoking a mutator is to explicitly cast its mutator parameter data type to be the same as the declared attribute type.