INFORMIX
Informix Guide to SQL: Reference
Chapter 2: Data Types
Home Contents Index Master Index New Book

Extended Data Types

 Universal Server allows you to characterize data that cannot be represented well by the built-in data types. It allows you to create the following kinds of extended data types:

The following sections provide an overview of each of these data types.

Complex Data Types

A complex data type is a data type that you build from other data types (built-in and extended). Figure 2-6 shows the different complex types that Universal Server supports and the following table briefly describes the structure of these data types.

Figure 2-6
Complex Data Types Supported by Universal Server

Data Type Description

Collection types

Complex data types that are made up of elements, each of which is the same data type.

LIST

A group of ordered elements, each of which need not be unique.

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.

Nesting in Complex Data Types

Complex types can be nested. For example, you can construct a row type whose fields include one or more sets, multisets, row types, and/or lists. Likewise, a collection type can have elements whose data type is a row type or a collection type. For a discussion of nested complex types, see Chapter 10, "Understanding Complex Data Types," in the Informix Guide to SQL: Tutorial.

Support Function Inheritance with Complex Data Types

All complex types inherit the following support functions:

input

assign

output

destroy

send

LOhandles

recv

hash

import

lessthan

export

equal

import binary

lessthan (ROW only)

export binary

This section summarizes the complex types. For more information on complex types, see Chapter 10, "Understanding Complex Data Types" in the Informix Guide to SQL: Tutorial.

Collection Data Types

A collection data type is a complex type made up of one or more elements. Every element in a collection has the same data type. A collection element can have any data type, including other complex types, except TEXT, BYTE, SERIAL, or SERIAL8.

Important: The value of an element cannot be the null value. You must specify the not null constraint for collection elements. No other constraints are valid for collections.
Universal Server supports three kinds of collection types: SET, MULTISET, and LIST. The keywords used to construct these collections are called type constructors or just constructors. For a description of each of these collection data types, see its entry in this chapter. For the syntax of collection types, see the Complex Data Type segment in the Informix Guide to SQL: Syntax.

Using Complex Data Types in Table Columns
When you specify element values for a collection, list the element values after the constructor and between curly brackets. For example, suppose you have a collection column with the following type:

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

Leave the brackets empty to indicate an empty set:

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:

You can also use nested dot notation to access collection data. To learn more about accessing the elements of a collection, see "Casting Collection Data Types" of the Informix Guide to SQL: Tutorial.

Row Data Types

A row type is a sequence 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 there are important differences: a field has no default clause, you cannot define constraints on a field, and you cannot use fields with tables, only with row types.

Two kinds of row types are: named row types and unnamed row types. Named row types are identified by their names. Unnamed row types are identified by their structure. The structure of an unnamed row type consists of the number and data types of its fields. For more information about the two row types, see pages 2-56 and 2-67.

You can cast between named and unnamed row types. For a discussion of casting, see Chapter 10, "Understanding Complex Data Types"of the Informix Guide to SQL: Tutorial.

Opaque Data Types

An opaque data type is a user-defined data type that is fully encapsulated, that is, whose internal structure is unknown to the database server. For more information, see the description given on page 2-58.

Distinct Data Types

A distinct data type has the same internal structure as some other source data type in the database. The source data type can be either a built-in type or an extended type. What distinguishes a distinct type from the source type are the functions defined on this type. For more information, see the description on page 2-46.



Informix Guide to SQL: Reference, version 9.1
Copyright © 1998, Informix Software, Inc. All rights reserved.