Informix Guide to SQL: Tutorial

Informix Guide to SQL: Tutorial
Chapter 10: Understanding Complex Data Types

Home Contents Index Master Index New Book

What Are Complex Data Types?

A complex data type is a user-defined data type that can contain multiple data types of any kind and in any combination. An important characteristic of a complex data type is that you can easily access each of its component data types. In contrast, built-in types and opaque types are self-contained (encapsulated) data types. Consequently, the only way to access the component values of an opaque data type is through functions that you define on the opaque type. (For more information on opaque data types, see Chapter 3, "Environment Variables," in the Informix Guide to SQL: Reference.)

Figure 10-1 shows the complex types that Universal Server supports and the syntax that you use to create the complex types.

Figure 10-1
Complex Types

The complex types illustrated in Figure 10-1 provide the following extended data type support:

Collection types. You can use a collection type whenever you need to store and manipulate collections of data within a table cell. You can assign collection types to columns.
Row types. You can assign a row type to a column or a table. A column that is a named row type contains multiple fields (subcolumns). When you assign a named row type to a table, the type defines the structure of the entire table.

You can use complex types in the same way that you use built-in or opaque data types. For example, you can use complex types as:

column types.
routine argument types and return types.
field types in other complex types.

For complete information about how to perform SELECT, INSERT, UPDATE, and DELETE operations on the complex data types described in this chapter, see Chapter 12, "Accessing Complex Data Types."

Named Row Types

A named row type is a group of fields that are defined under a single name. A field refers to a component of a row type and should not be confused with a column, which is associated with tables only. The fields of a named row type are analogous to the fields of a C-language structure or members of a class in object-oriented programming. Once you create a named row type, the name that you assign to the row type represents a unique type within the database. To create a named row type, you specify a name for the row type and the names and data types of its constituent fields. The following example shows how you might create a named row type called person_t:

CREATE ROW TYPE person_t
(
	name		VARCHAR(30) NOT NULL,
	address		VARCHAR(20),
	city		VARCHAR(20),
	state		CHAR(2),
	zip		VARCHAR(9),
	bdate 		DATE
);

The person_t row type contains six fields: name, address, city, state, zip, and bdate. You can use any data type to define the fields of a row type, except the TEXT, BYTE, SERIAL, or SERIAL8 data type. When you create a named row type, you can use it just as you would any other data type. For example, person_t can occur anywhere that you might use any other data type.

For the syntax you use to create a named row type, see the CREATE ROW TYPE statement in the Informix Guide to SQL: Syntax. For information about how to cast row type values, see Chapter 13 in this manual.

CREATE ROW TYPE person_t
(
	name		VARCHAR(30),
	address		VARCHAR(20),
	city		VARCHAR(20),
	state		CHAR(2),
	zip		INTEGER,
	bdate 		DATE
);


CREATE TABLE person OF TYPE person_t;

The first statement creates the person_t type. The second statement creates the person table, which contains instances of the person_t type. More specifically, each row in a typed table contains an instance of the named row type that is assigned to the table. In the preceding example, the fields of the person_t type define the columns of the person table.

Inserting data into a typed table is no different than inserting data into an untyped table. When you insert data into a typed table, the operation creates an instance of the row type and inserts it into the table. The following example shows how to insert a row into the person table:

INSERT INTO person 
VALUES ('Brown, James', '13 First St.', 'San Carlos', 'CA', 
94070, '01/04/1940')

The INSERT statement creates an instance of the person_t type and inserts it into the table. For information about how to insert, update, and delete columns that are defined on named row types, see "Modifying Columns That Contain Row Type Data".

You can use a single named row type to create multiple typed tables. In this case, each table has a unique name, but all tables share the same type.

Important: You cannot create a typed table that is a temporary table.

For information on the advantages of choosing to implement your data model using typed tables, see "Type Inheritance".

Converting an Untyped Table into a Typed Table

The primary advantage of typed tables over untyped tables is that typed tables can be used in an inheritance hierarchy. In general, inheritance allows a table to acquire the representation and behavior of another table. For more information about inheritance, see "What Is Inheritance?".

If you want to convert an existing untyped table into a typed table, you can use the ALTER TABLE statement. For example, consider the following untyped table:

CREATE TABLE manager
(
	name 			VARCHAR(30),
	department 			VARCHAR(20),
	salary 			INTEGER
);

To convert an untyped table to a typed table, both the field names and the field types of the named row type must match the column names and column types of the existing table. For example, to make the manager table a typed table, you must first create a named row type that matches the column definitions of the table. The following statement creates the manager_t type, which contains field names and field types that match the columns of the manager table:

CREATE ROW TYPE manager_t
(
name 			VARCHAR(30),
department 			VARCHAR(30),
salary 			INTEGER
);

Once you create the named row type that you want to assign to the existing untyped table, use the ALTER TABLE statement to assign the type to the table. The following statement alters the manager table and makes it a typed table of type manager_t:

ALTER TABLE manager ADD TYPE manager_t

The new manager table contains the same columns and data types as the old table but now provides the advantages of a typed table.

Using a Named Row Type to Create a Column

Both typed and untyped tables can contain columns that are defined on named row types. A column that is defined on a named row type behaves in the same way whether the column occurs in a typed table or untyped table. In the following example, the first statement creates a named row type address_t; the second statement assigns the address_t type to the address column in the employee table:

CREATE ROW TYPE address_t
(
	street		VARCHAR(20),
	city		VARCHAR(20),
	state		CHAR(2),
	zip		VARCHAR(9)
);


CREATE TABLE employee
(
	name		VARCHAR(30),
	address		address_t,
	salary		INTEGER
);

In the preceding CREATE TABLE statement, the address column has the street, city, state, and zip fields of the address_t type. Consequently, the employee table, which has only three columns, contains values for name, street, city, state, zip, and salary. You use dot notation to access the individual fields of a column that is defined on a row type. For information about using dot notation to access fields of a column, see "Field Projections".

When you insert data into a column that is assigned a row type, you need to use the ROW constructor to specify row literal values for the row type. The following example shows how to use the INSERT statement to insert a row into the employee table:

INSERT INTO employee
VALUES ('John Bryant', 

ROW('10 Bay Street', 'Madera', 'CA', 95400)::address_t, 

55000);

Strong typing is not enforced for an insert or update on a named row type. To ensure that the row values are of the named row type, you must explicitly cast to the named row type to generate values of a named row type, as shown in the previous example. The INSERT statement inserts three values, one of which is a row type value that contains four values. More specifically, the operation inserts unitary values for the name and salary columns, but it creates an instance of the address_t type and inserts it into the address column.

For more information about how to insert, update, and delete columns that are defined on row types, see "Modifying Columns That Contain Row Type Data".

Using a Named Row Type Within Another Named Row Type

You can use a row type as the data type of a field within another row type. In the following example, the first statement creates the address_t type, which is also used in the second statement to define the type of the address field of the employee_t type:

CREATE ROW TYPE address_t
(
	street		VARCHAR (20),
	city		VARCHAR(20),
	state		CHAR(2),
	zip		VARCHAR(9)
);


CREATE ROW TYPE employee_t
(
	name		VARCHAR(30) NOT NULL,
	address		address_t,
	salary		INTEGER
);

Important: A row type cannot be used recursively. If type_t is a row type, then type_t cannot be used as the data type of a field contained in type_t.

Dropping Named Row Types

To drop a named row type, use the DROP ROW TYPE statement. You can drop a type only if it has no dependencies. You cannot drop a named row type if any of the following conditions are true:

The type is currently assigned to a table.
The type is currently assigned to a column in a table.
The type is currently assigned to a field within another row type.

The following example shows how to drop the person_t type:

DROP ROW TYPE person_t restrict;

For information about dropping a named row type from a type hierarchy, see "Dropping Named Row Types from a Type Hierarchy".

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. In general, it is easier to cast between unnamed row types than named row types because type checking on unnamed row types is by structural equivalence only.

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, and 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 "Modifying Columns That Contain Row Type Data".

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 information on the syntax for unnamed row types, see the Data Type segment of the Informix Guide to SQL: Syntax. For information about how to cast row type values, see Chapter 13 in this manual.

Restrictions on Data Types Allowed in Unnamed Row Types

You cannot use the following data types in the field definition of an unnamed row type:

SERIAL
SERIAL8
BYTE
TEXT

Collection Data Types

Collection data types enable you to store and manipulate collections of data within a single row of a table. A collection 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 Data Types Allowed in 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 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)

Once you define a column as a collection 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 of the Informix Guide to SQL: Syntax. For information about how to cast between collection data types, see Chapter 13 in this manual.

Important: The contents of a collection, including spaces and tabs, must not exceed 32 kilobytes.

Null Values in Collections

A collection cannot contain null elements. When you insert elements into a collection that is a row type, you must specify a value for at least one field of the row type for each element in the collection. For example, to insert data into col_2, you must provide, at minimum, a value for either the name or salary field. If you attempt to insert null values for both the name and salary fields, the database server returns an error.

Important: When you define a collection type, you must include the not null constraint as part of the type definition. No other column constraints are allowed on a collection type.

Using a Set

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 a Multiset

A multiset is a collection of elements in which 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 a List

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 generated by a salesperson 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 "Modifying Collections".

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
	flowers			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 Informix Guide to SQL: Syntax.

Important: You cannot use the ALTER TABLE statement to add a column to a typed table because the named row type that is assigned to the table specifies the structure of the table.

Restrictions on Data Types Allowed in Collections

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

SERIAL
SERIAL8

Informix Guide to SQL: TutorialChapter 10: Understanding Complex Data Types Home Contents Index Master Index New Book

What Are Complex Data Types?

Dropping Named Row Types

Collection Data Types

Nesting Collection Types

Informix Guide to SQL: Tutorial
Chapter 10: Understanding Complex Data Types

Home Contents Index Master Index New Book