Collection data types

A collection data type is a group of values of a single data type in a column. Each value is referred to as an element.

A collection data type is defined by using a type constructor and an element data type. Type constructors determine whether the database server checks for duplicate elements or orders the elements. The following table describes the collection type constructors.

Type constructor	Duplicates allowed?	Ordered?
SET	No	No
MULTISET	Yes	No
LIST	Yes	Yes

For a SET, the database server prevents insertion of duplicate elements. For a MULTISET, the database server takes no special actions. For a LIST, the database server orders the elements.

Elements can be almost any data type, including other extended data types and built-in data types such as smart large objects. You can access any element in a collection individually through SQL statements.

The number of elements in a collection is not mandated. You can change the number of elements in a collection without reinserting it into a table, and different rows can have different numbers of elements in their collections.

The following diagram illustrates a collection data type by using a SET constructor and the LVARCHAR data type in a column called Dependents.

Figure 1. Sample collection data type

begin figure description - This figure is described in the surrounding text. - end figure description

Instead of putting information about dependents in a separate table, all the information is contained in one row with collection data type. You can add or remove elements without altering the columns of the table.

You can use collection data types to reconfigure a table with awkwardly long rows by grouping data into a single column. Use a collection if you have data of the same data type that can be naturally grouped into a single column. You can group data even further by creating a collection of row types or other collections.

Collections are also useful as returned values: for example, a group of values from many rows in a column or fields in a row type. For example, if you want to obtain a list of every city in which your employees live from the sample collection data type in Figure 1, you could create a collection on the Location column to return a set of values.

The following function types can return collections:

A user-defined function that returns a collection
An iterator function that returns a single value at a time but is called repeatedly to assemble a collection

For a description of collection data types, see the HCL Informix® Guide to SQL: Tutorial.