Collection Data Types

Collection data types enable you to store and manipulate collections of data within a single row of a table. A collection data type has two components: a type constructor, which determines whether the collection type is a SET, MULTISET, or LIST, and an element type, which specifies the type of data that the collection can contain. (The SET, MULTISET, and LIST collection types are described in detail in the following sections.)

The elements of a collection can be of most any data type. (For a list of exceptions, see Restrictions on Collections.) The elements of a collection are the values that the collection contains. In a collection that contains the values: {'blue', 'green', 'yellow', and 'red'}, 'blue' represents a single element in the collection. Every element in a collection must be of the same type. For example, a collection whose element type is INTEGER can contain only integer values.

The element type of a collection can represent a single data type (column) or multiple data types (row). In the following example, the col_1 column represents a SET of integers:

col_1 SET(INTEGER NOT NULL)

To define a collection data type that contains multiple data types, you can use a named row type or an unnamed row type. In the following example, the col_2 column represents a SET of rows that contain name and salary fields:

col_2 SET(ROW(name VARCHAR(20), salary INTEGER) NOT NULL)

After you define a column as a collection data type, you can perform the following operations on the collection:

• Select and modify individual elements of a collection (from ESQL/C programs only).
• Count the number of elements that a collection contains.
• Determine if certain values are in a collection.

For information on the syntax that you use to create collection data types, see the Data Type segment in the IBM Informix: Guide to SQL Syntax. For information about how to convert a value of one collection type to another collection type, see the IBM Informix: Guide to SQL Tutorial.

Null Values in Collections

A collection cannot contain null elements. However, when the collection is a row type, you can insert null values for any or all fields of a row type that a collection contains. Suppose you create the following table that has a collection column:

CREATE TABLE tab1 (col1 INT,
        col2 SET(ROW(a INT, b INT) NOT NULL));

The following statements are allowed because only the component fields of the row type specify null values:

INSERT INTO tab1 VALUES ( 25,"SET{ROW(NULL, NULL)}");

INSERT INTO tab1 VALUES ( 35,"SET{ROW(4, NULL)}");

INSERT INTO tab1 VALUES ( 45,"SET{ROW(14, NULL), ROW(NULL,5)}");

UPDATE tab1 SET col2 = "SET{ROW(NULL, NULL)}" WHERE col1 = 45;

However, each of the following statements returns an error message because the collection element specifies a null value:

INSERT INTO tab1 VALUES ( 45, "SET{NULL)}");

UPDATE tab1 SET col2 = "SET{NULL}" WHERE col1 = 55;

Using SET Collection Types

A SET is an unordered collection of elements in which each element is unique. You define a column as a SET collection type when you want to store collections whose elements have the following characteristics:

• The elements contain no duplicate values.
• The elements have no specific order associated with them.

To illustrate how you might use a SET, imagine that your human resources department needs information about the dependents of each employee in the company. You can use a collection type to define a column in an employee table that stores the names of an employee's dependents. The following statement creates a table in which the dependents column is defined as a SET:

CREATE TABLE employee
(
   name        CHAR(30),
   address     CHAR (40),
   salary      INTEGER,
   dependents  SET(VARCHAR(30) NOT NULL)
);

A query against the dependents column for any given row returns the names of all the dependents of the employee. In this case, SET is the appropriate collection type because the collection of dependents for each employee should not contain any duplicate values. A column that is defined as a SET ensures that each element in a collection is unique.

To illustrate how to define a collection type whose elements are a row type, suppose that you want the dependents column to include the name and birthdate of an employee's dependents. In the following example, the dependents column is defined as a SET whose element type is a row type:

CREATE TABLE employee
(
   name        CHAR(30),
   address     CHAR (40),
   salary      INTEGER,
   dependents  SET(ROW(name VARCHAR(30), bdate DATE) NOT NULL)
);

Each element of a collection from the dependents column contains values for the name and bdate. Each row of the employee table contains information about the employee as well as a collection with the names and birthdates of the employee's dependents. For example, if an employee has no dependents, the collection for the dependents column is empty. If an employee has 10 dependents, the collection should contain 10 elements.

Using MULTISET Collection Types

A MULTISET is a collection of elements in which the elements can have duplicate values. For example, a MULTISET of integers might contain the collection {1,3,4,3,3}, which has duplicate elements. You can define a column as a MULTISET collection type when you want to store collections whose elements have the following characteristics:

• The elements might not be unique.
• The elements have no specific order associated with them.

To illustrate how you might use a MULTISET, suppose that your human resources department wants to keep track of the bonuses awarded to employees in the company. To track each employee's bonuses over time, you can use a MULTISET to define a column in a table that records all the bonuses that each employee receives. In the following example, the bonus column is a MULTISET:

CREATE TABLE employee
(
   name      CHAR(30),
   address   CHAR (40),
   salary    INTEGER,
   bonus     MULTISET(MONEY NOT NULL)
);

You can use the bonus column in this statement to store and access the collection of bonuses for each employee. A query against the bonus column for any given row returns the dollar amount for each bonus that the employee has received. Because an employee might receive multiple bonuses of the same amount (resulting in a collection whose elements are not all unique), the bonus column is defined as a MULTISET, which allows duplicate values.

Using LIST Collection Types

A LIST is an ordered collection of elements that allows duplicate values. A LIST differs from a MULTISET in that each element in a LIST has an ordinal position in the collection. The order of the elements in a list corresponds with the order in which values are inserted into the LIST. You can define a column as a LIST collection type when you want to store collections whose elements have the following characteristics:

• The elements have a specific order associated with them.
• The elements might not be unique.

To illustrate how you might use a LIST, suppose your sales department wants to keep a monthly record of the sales total for each salesperson. You can use a LIST to define a column in a table that contains the monthly sales totals for each salesperson. The following example creates a table in which the month_sales column is a LIST. The first entry (element) in the LIST, with an ordinal position of 1, might correspond to the month of January, the second element, with an ordinal position of 2, February, and so forth:

CREATE TABLE sales_person
(
   name         CHAR(30),
   month_sales  LIST(MONEY NOT NULL)
);

You can use the month_sales column in this statement to store and access the monthly sales totals for each salesperson. More specifically, you might perform queries on the month_sales column to find out:

• The total sales that a salesperson generated during a specified month
• The total sales for every salesperson during a specified month

Nesting Collection Types

A nested collection is a collection type that contains another collection type. You can nest any collection type within another collection type. There is no practical limit on how deeply you can nest a collection type. However, performing inserts or updates on a collection that has been nested more than one or two levels can be difficult. The following example shows several ways in which you might create columns that are defined on nested collection types:

col_1 SET(MULTISET(VARCHAR(20) NOT NULL) NOT NULL);

col_2 MULTISET(ROW(x CHAR(5), y SET(INTEGER NOT NULL))
NOT NULL);

col_3 LIST(MULTISET(ROW(a CHAR(2), b INTEGER) NOT NULL)
NOT NULL);

For information about how to access a nested collection, see the IBM Informix: Guide to SQL Tutorial.

Adding a Collection Type to an Existing Table

You can use the ALTER TABLE statement to add or drop a column that is a collection type (or any other data type). For example, the following statement adds the flowers column, which is defined as a SET, to the nursery table:

ALTER TABLE nursery ADD flower SET(VARCHAR(30) NOT NULL)

You cannot modify an existing column that is a collection type or convert a non-collection type column into a collection type.

For more information on adding and dropping collection-type columns, see the ALTER TABLE statement in the IBM Informix: Guide to SQL Syntax.

Restrictions on Collections

You cannot use any of the following data types as the element type of a collection:

• TEXT
• BYTE
• SERIAL
• SERIAL8

You cannot use a CREATE INDEX statement to create an index on collection, nor can you create a functional index for a collection column.