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 Informix® 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 Informix® Guide to SQL: Tutorial.