Using Dot Notation with Row-Type Expressions

Besides specifying a column of a ROW data type, you can also use dot notation with any expression that evaluates to a ROW type. In an INSERT statement, for example, you can use dot notation in a subquery that returns a single row of values. Assume that you created a ROW type named row_t:

CREATE ROW TYPE row_t (part_id INT, amt INT);

Also assume that you created a typed table named tab1 that is based on the row_t ROW type:

CREATE TABLE tab1 OF TYPE row_t;

Assume also that you inserted the following values into table tab1:

INSERT INTO tab1 VALUES (ROW(1,7));
INSERT INTO tab1 VALUES (ROW(2,10));

Finally, assume that you created another table named tab2:

CREATE TABLE tab2 (colx INT);

Now you can use dot notation to insert the value from only the part_id column of table tab1 into the tab2 table:

INSERT INTO tab2
   VALUES ((SELECT t FROM tab1 t
      WHERE part_id = 1).part_id);

The asterisk form of dot notation is not necessary when you want to select all fields of a ROW-type column because you can specify the column name alone to select all of its fields. The asterisk form of dot notation can be quite helpful, however, when you use a subquery, as in the preceding example, or when you call a user-defined function to return ROW-type values.

Suppose that a user-defined function named new_row returns ROW-type values, and you want to call this function to insert the ROW-type values into a table. Asterisk notation makes it easy to specify that all the ROW-type values that the new_row( ) function returns are to be inserted into the table:

INSERT INTO mytab2 SELECT new_row (mycol).* FROM mytab1;

References to the fields of a ROW-type column or a ROW-type expression are not allowed in fragment expressions. A fragment expression is an expression that defines a table fragment or an index fragment in SQL statements like CREATE TABLE, CREATE INDEX, and ALTER FRAGMENT.