Extending Informix Dynamic Server 2000 Creating an Opaque Data Type

An SQL-invoked function is a user-defined function that an end user can explicitly call in an SQL statement. You might write SQL-invoked functions to extend the functionality of an opaque data type in the following ways:

• Writing new versions of arithmetic or built-in functions to provide arithmetic operations and built-in functions on the opaque data type
• Writing new versions of relational-operator functions to provide comparison operations on the opaque data type
• Writing new end-user routines to provide additional functionality for the opaque data type
• Writing new cast functions to provide additional data conversions to and from the opaque data type

The SQL functions that the database server defines handle the built-in data types. For a user-defined data type to use any of these functions, you can write a version of the function that handles the UDT. (For more information on the details of writing user-defined functions, see Chapter 4, Developing a User-Defined Routine.)

The database server supports the following types of SQL-invoked functions that allow you to operate on data in expressions of SQL statements:

• Arithmetic and text operator functions
• Built-in functions
• Aggregate functions

The database server provides the following types of operators for expressions in SQL statements:

• Arithmetic operators usually operate on numeric values.
• Text operators operate on character strings.

Tip: The database server also provides relational operators. For more information on the relational operators and their operator functions, see Comparing Data.

The database server provides operator functions for the arithmetic operators (see Figure 6-1 on page 6-5) and text operators (see Figure 6-2 on page 6-5). The versions of the operator functions that database server provides handle the built-in data types. You can write a new version of one of these operator function to provide the associated operation on your new opaque data type.

If you write a new version of an operator function, make sure you follow these rules:

1. The name of the operator function must match the name that Figure 6-1 on page 6-5 or Figure 6-2 on page 6-5 lists. The name is not case sensitive; the plus() function is the same as the Plus() function.
2. The operator function must handle the correct number of parameters.
3. The operator function must return the correct data type, where appropriate.

The database server provides special SQL-invoked functions, called built-in functions, that provide some basic mathematical operations. Figure 6-4 on page 6-8 shows the built-in functions that the database server defines. The versions of the built-in functions that database server provides handle the built-in data types. You can write a new version of a built-in function to provide the associated operation on your new opaque data type. If you write a new version of a built-in function, follow these rules:

1. The name of the built-in function must match the name that Figure 6-4 on page 6-8 lists. However, the name is not case sensitive; the abs() function is the same as the Abs() function.
2. The built-in function must be one that can be overridden.
3. The built-in function must handle the correct number of parameters, and these parameters must be of the correct data type. Figure 6-4 on page 6-8 lists the number and data types of the parameters.
4. The built-in function must return the correct data type, where appropriate.

For more information on built-in functions, see Built-In Functions.

The database server supports the following types of functions that allow you to compare data in expressions of SQL statements:

• SQL operators in a conditional clause
• Relational operator functions

The database server supports the following relational operators on an opaque data type in the conditional clause of SQL statements:

• The IS and IS NOT operators
• The IN operator if the equal() function has been defined
• The BETWEEN operator if the compare() function has been defined

Tip: The database server also uses the compare() function as the support function for the default B-tree operator class. For more information, see Extensions of the btree_ops Operator Class.

For more information on the conditional clause, see the Condition segment in the Informix Guide to SQL: Syntax. For more information on the compare() function, see Comparison Function for Opaque Data Types.

The database server provides operator functions for the relational operators that Figure 6-3 on page 6-6 shows. The versions of the relational-operator functions that the database server provides handle the built-in data types. You can write a new version of a relational-operator function to provide the associated operation on your new opaque data type.

If you write a new version of a relational-operator function, make sure you follow these rules:

1. The name of the relational-operator function must match the name that Figure 6-3 on page 6-6 lists. However, the name is not case sensitive; the equal() function is the same as the Equal() function.
2. The relational-operator function must take two parameters, both of the opaque data type.
3. The relational-operator function must be a Boolean function; that is, it must return a BOOLEAN value.

You must define an equal() function to handle your opaque data type if you want to allow columns of this data type to be:

• constrained as UNIQUE or PRIMARY KEY.

For more information on constraints, see the CREATE TABLE statement in the Informix Guide to SQL: Syntax.

• compared with the equal (=) operator in an expression.
• used with the IN operator in a condition.

The equal() function can compare only bit-hashable data types; that is, a bit-wise compare can determine equality. This comparison means that two values are equal if they have the same internal representation. The database server uses a built-in hash function to perform this comparison.

If your opaque data type is not bit hashable, the database server cannot use its built-in hash function for the equality comparison. Therefore, you cannot use the opaque data type in the following cases:

• In the GROUP BY clause of a SELECT statement
• In hash joins
• With the IN operator in a WHERE clause

For opaque types that are not bit hashable, specify the CANNOTHASH modifier in the CREATE OPAQUE TYPE statement.

Bit-hashable data types have the following property: if A = B, then hash(A) = hash(B), which means that A and B have identical bit representations. CANNOTHASH specifies that data type equality cannot be determined by a bit-wise compare.

The following requirements apply to support functions on nonhashable data types:

• The equal() function should be hashable (no CANNOTHASH modifier) to indicate that two values have same internal representation or you must define a hash() function. You must have an equal() function if a column of the data requires the UNIQUE constraint.

Data types whose equal() functions require the support of a hash() function are those in which a bitwise comparison does not guarantee equality (not the same internal representation).

• The hash() function must have the following property: if equal(x,y) is true, then equal(hash(x), hash(y)) is also true. The hash() function takes a single argument and returns an integer. GROUP BY, IN, and hash joins and merges use the hash() function.

The compare() function is an SQL-invoked function that sorts the target data type. The database server uses the compare() function to execute the following clauses and keywords of the SELECT statement:

• The ORDER BY clause
• The UNIQUE and DISTINCT keywords
• The UNION keyword

The database server also uses the compare() function to evaluate the BETWEEN operator in the condition of an SQL statement. For more information on conditional clauses, see the Condition segment in the Informix Guide to SQL: Syntax.

The database server provides versions of the compare() function that handle the built-in data types. For the database server be able to sort an opaque data type, you must define a compare() function that handles this opaque data type.

If you write a new version of a compare() function, make sure you follow these rules:

1. The name of the function must be compare(). The name is not case sensitive; the compare() function is the same as the Compare() function.
2. The function must accept two arguments, each of the data types to be compared.
3. The function must return an integer value to indicate the result of the comparison, as follows:
• <0 to indicate that the first argument is less than (<) the second argument
• 0 to indicate that the two arguments are equal (=)
• >0 to indicate that the first argument is greater than (>) the second argument

If your opaque data type is not bit hashable, the compare() function should generate an error so that the database server does not use the default compare() function.

The compare() function is also the support function for the default operator class of the B-tree secondary access method. For more information, see Generic B-Tree Index.