ROW Data Types

A ROW data type is a complex data type that can store multiple data values within its ordered set of one or more fields. Informix supports two categories of ROW data types: named ROW types that the CREATE ROW TYPE statement registers in the system catalog, and unnamed ROW types that ROW constructor expressions define.

You can use this syntax when you assign a ROW data type to a column or to an SPL variable.

Named and unnamed ROW data types

Named ROW data type

Unnamed ROW data type


Element	Description	Restrictions	Syntax
data_type	Data type of field	Any built-in type except BYTE or TEXT, or a UDT that has a support function for bit-hashing	Data Type
field	Name of a field within row_type	Must be unique among field names of the same ROW type	Identifier
owner	Authorization identifier of the owner of this ROW type	In an ANSI-compliant database, the combination owner.row_type must be unique among data types in the database	Owner name
row_type	Named ROW type that CREATE ROW TYPE statement defined	ROW type must exist in the database	Identifier; Data Type

Named ROW types

You can assign a named ROW type to a table, to a column, or to an SPL variable. A named ROW type must already exist in the database before you can use it in any of the following contexts:

to create a typed table
to define a column
to define an SPL variable of a named ROW type.

A field in a named ROW type can be another existing named ROW type, as in this example:

CREATE ROW TYPE row_t ( w INT, y INT); 
CREATE ROW TYPE rowspace_t ( u INT, v row_t, z DATE);

Each row of the named ROW type rowspace_t can stores three INT values and one DATE value, but two of those INT values are in the field of ROW type row_t.

To specify a named ROW data type in an ANSI-compliant database, you must qualify the row_type with its owner name, if you are not the owner of row_type.

A named ROW type can be the child of a supertype within a data type hierarchy, from which it inherits the field data types of its parent ROW type, or it can have no parent supertype. For the DDL syntax to create a new named ROW data type, see CREATE ROW TYPE statement.

Unnamed ROW types

An unnamed ROW data type is identified by its structure, which specifies fields that you create with its ROW constructor. For the syntax of unnamed ROW constructors, see Constructor Expressions.

You can define a column or an SPL variable as an unnamed ROW data type.

If you omit the field list of the ROW constructor when the DEFINE statement of SPL declares an SPL variable as an unnamed ROW data type, the new variable has a generic ROW data type, to which the SPL routine can assign the field values of any ROW data type. For the syntax to declare SPL variables as ROW data types, see Subset of Complex Data Types.

Restrictions on ROW types

Columns in database tables cannot be generic ROW types.

An SPL variable declared as a generic ROW data type cannot return the result of an SQL routine. Before you can use a ROW type variable in an SPL routine, you must initialize the row variable with a LET statement or with a SELECT INTO statement.

Fields in a ROW data type cannot include TEXT or BYTE data types. Fields in ROW types also cannot be user-defined types (UDTs) that are not bit-hashable using the built-in hashing function of the database server.

Because of the maximum row size limit of 32,767 bytes, a single table cannot be created with more than approximately 195 ROW type columns.