Unnamed row types

An unnamed row type is a group of typed fields that you create with the ROW constructor. An important distinction between named and unnamed row types is that you cannot assign an unnamed row type to a table. You use an unnamed row type to define the type of a column or field only. In addition, an unnamed row type is identified by its structure alone, whereas a named row type is identified by its name. The structure of a row type consists of the number and data types of its fields.

The following statement assigns two unnamed row types to columns of the student table: 
CREATE TABLE student
(
   s_name  ROW(f_name VARCHAR(20), m_init CHAR(1), 
               l_name VARCHAR(20) NOT NULL),
   s_address  ROW(street VARCHAR(20), city VARCHAR(20), 
                  state CHAR(2), zip VARCHAR(9))
   );
The s_name and s_address columns of the student table each contain multiple fields. Each field of an unnamed row type can have a different data type. Although the student table has only two columns, the unnamed row types define a total of seven fields:
  • f_name
  • m_init
  • l_name
  • street
  • city
  • state
  • zip
The following example shows how to use the INSERT statement to insert data into the student table: 
INSERT INTO student
VALUES (ROW('Jim', 'K', 'Johnson'), ROW('10 Grove St.',
'Eldorado', 'CA', 94108))

For more information about how to modify columns that are defined on row types, see the HCL OneDB™ Guide to SQL: Tutorial.

The database server does not distinguish between two unnamed row types that contain the same number of fields and that have corresponding fields of the same type. Field names are irrelevant in type checking of unnamed row types. For example, the database server does not distinguish between the following unnamed row types: 
ROW(a INTEGER, b CHAR(4));
ROW(x INTEGER, y CHAR(4));

For the syntax of unnamed row types, see the HCL OneDB Guide to SQL: Syntax. For information about how to cast row type values, see Create and use user-defined casts.

The following data types cannot be field types in an unnamed row type:
  • BIGSERIAL
  • SERIAL
  • SERIAL8
  • BYTE
  • TEXT

The database server returns an error when any of the preceding types are specified in the field definition of an unnamed row type.