Use a named row type to create a column

Both typed and untyped tables can contain columns that are defined on named row types. A column that is defined on a named row type behaves in the same way whether the column occurs in a typed table or untyped table. In the following example, the first statement creates a named row type address_t; the second statement assigns the address_t type to the address column in the employee table:

CREATE ROW TYPE address_t
(
   street  VARCHAR(20),
   city    VARCHAR(20),
   state   CHAR(2),
   zip     VARCHAR(9)
);

CREATE TABLE employee
(
   name     VARCHAR(30),
   address  address_t,
   salary   INTEGER
);

In the preceding CREATE TABLE statement, the address column has the street, city, state, and zip fields of the address_t type. Consequently, the employee table, which has only three columns, contains values for name, street, city, state, zip, and salary. Use dot notation to access the individual fields of a column that are defined on a row type. For information about using dot notation to access fields of a column, see the HCL OneDB™ Guide to SQL: Tutorial.

When you insert data into a column that is assigned a row type, you must use the ROW constructor to specify row literal values for the row type. The following example shows how to use the INSERT statement to insert a row into the employee table:

INSERT INTO employee
VALUES ('John Bryant', 
  ROW('10 Bay Street', 'Madera', 'CA', 95400)::address_t, 55000);

Strong typing is not enforced for an insert or update on a named row type. To ensure that the row values are of the named row type, you must explicitly cast to the named row type to generate values of a named row type, as the previous example shows. The INSERT statement inserts three values, one of which is a row type value that contains four values. More specifically, the operation inserts unitary values for the name and salary columns but it creates an instance of the address_t type and inserts it into the address column.

For more information about how to insert, update, and delete columns that are defined on row types, see the HCL OneDB Guide to SQL: Tutorial.