Complex Data Types

Home | Previous Page | Next Page Data Types > Extended Data Types (IDS) >

Complex Data Types

A complex data type can store one or more values of other built-in and extended data types. Figure 5 shows the complex types that Dynamic Server supports.

Figure 5. Complex Data Types of Dynamic Server

Begin figure description. Labelled rectangles depict the logical hierarchy of Complex data types. The COLLECTION types include LIST, MULTISET, and SET data types. The ROW types include Named ROW and Unnamed ROW data types. End figure description

The following table summarizes the structure of the complex data types.

Data Type		Description
Collection types:		Complex data types that are made up of elements, each of which is of the same data type.
	LIST	A group of ordered elements, each of which need not be unique within the group.
	MULTISET	A group of elements, each of which need not be unique. The order of the elements is ignored.
	SET	A group of elements, each of which is unique. The order of the elements is ignored.
ROW types:		Complex data types that are made up of fields.
	Named ROW type	Row types that are identified by their name.
	Unnamed ROW type	Row types that are identified by their structure.

Complex data types can be nested. For example, you can construct a ROW type whose fields include one or more sets, multisets, ROW types, and lists. Likewise, a collection type can have elements whose data type is a ROW type or a collection type.

Complex types that include opaque types inherit the following support functions.

input	export	LO_handles
output	import_binary	hash
send	export_binary	lessthan
recv	assign	equal
import	destroy	lessthan (ROW only)

Sections that follow summarize the complex data types. For more information, see the IBM Informix: Database Design and Implementation Guide.

Collection Data Types

A collection data type is a complex type that is made up of one or more elements, all of the same data type. A collection element can be of any data type (including other complex types) except BYTE, TEXT, SERIAL, or SERIAL8.

Important:

An element cannot have a NULL value. You must specify the NOT NULL constraint for collection elements. No other constraints are valid for collections.

Dynamic Server supports three kinds of built-in collection types: LIST, SET, and MULTISET. The keywords used to declare these collections are the names of the type constructors or just constructors. For the syntax of collection types, see the IBM Informix: Guide to SQL Syntax. No more than 97 columns of the same table can be declared as collection data types.

When you specify element values for a collection, list the element values after the constructor and between braces ( { } ). For example, suppose you have a collection column with the following MULTISET data type:

CREATE TABLE table1
(
   mset_col MULTISET(INTEGER NOT NULL)
)

The next INSERT statement adds one group of element values to this column. (The word MULTISET in these two examples is the MULTISET constructor.)

INSERT INTO table1 VALUES (MULTISET{5, 9, 7, 5})

You can leave the braces empty to indicate an empty set:

INSERT INTO table1 VALUE (MULTISET{})

An empty collection is not equivalent to a NULL value for the column.

Accessing Collection Data

To access the elements of a collection column, you must fetch the collection into a collection variable and modify the contents of the collection variable. Collection variables can be either of the following types:

Variables in an SPL routine
For more information, see the IBM Informix: Guide to SQL Tutorial.
Host variables in an IBM Informix ESQL/C program
For more information, see the IBM Informix: ESQL/C Programmer's Manual.

You can also use nested dot notation to access collection data. For more about accessing elements of a collection, see the IBM Informix: Guide to SQL Tutorial.

Important:

Collection data types are not valid as arguments to functions that are used for functional indexes.

ROW Data Types

A ROW data type is an ordered collection of one or more elements, called fields. Each field has a name and a data type. The fields of a ROW are comparable to the columns of a table, but with important differences:

A field has no default clause.
You cannot define constraints on a field.
You can only use fields with row types, not with tables.

Two kinds of ROW data types exist:

Named ROW data types are identified by their names.
Unnamed ROW data types are identified by their structure.

The structure of an unnamed ROW data type is the number (and the order of data types) of its fields.

No more than 195 columns of the same table can be declared as ROW data types. For more information about ROW data types, see ROW, Named (IDS) and ROW, Unnamed (IDS).

You can cast between named and unnamed ROW data types; this is described in the IBM Informix: Database Design and Implementation Guide.