Creating SQL-Invoked Functions

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:

• Overloading arithmetic or built-in functions to provide arithmetic operations and built-in functions on the opaque data type
• Overloading 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 UDT to use any of these functions, you can overload the function that handles the UDT. For more information on the details of writing user-defined functions, see Developing a User-Defined Routine. For information about overloading functions, refer to Overloading Routines.

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 also 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

Arithmetic and Text Operator Functions for Opaque Data Types

The database server provides operator functions for arithmetic operators (see Arithmetic Operators) and text operators (see Text Operators). The operator functions that the database server provides handle the built-in data types. You can overload an operator function to provide the associated operation on your new opaque data type.

If you overload an operator function, make sure you follow these rules:

1. The name of the operator function must match the name of one of the functions that the database server provides. 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.

Built-in Functions for Opaque Data Types

The database server provides special SQL-invoked functions, called built-in functions, that provide some basic mathematical operations. The built-in functions that the database server provides handle the built-in data types. You can overload a built-in function to provide the associated operation on your new opaque data type. If you overload a built-in function, follow these rules:

1. The name of the built-in function must match the name listed in Built-In Functions That You Can Overload. 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.
4. The built-in function must return the correct data type, where appropriate.

For more information on built-in functions, see the IBM Informix: Guide to SQL Syntax.

Aggregate Functions for Opaque Data Types

You can extend the built-in aggregate functions, such as SUM and AVG, to operate on your opaque data type. You can also create new aggregates. Creating User-Defined Aggregates, describes how to extend or create aggregates.

Conditional Operators for Opaque Data Types

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

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

Relational Operators for Opaque Data Types

The database server provides operator functions for the relational operators listed in Relational Operators. The relational-operator functions that the database server provides handle the built-in data types. You can overload a relational-operator function to provide the associated operation on your new opaque data type.

If you overload a relational-operator function, make sure you follow these rules:

1. The name of the relational-operator function must match a name listed in Relational Operators. 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 IBM Informix: Guide to SQL Syntax.

• Compared with the equal (=) operator in an expression
• Used with the IN operator in a condition

Hashable Data Types

The database server uses a built-in bit-hashing function to produce the hash value for a data type, which means that the built-in hash function can be used only for bit-hashable data types. If your opaque data type is not bit hashable, the database server cannot use its built-in hash function for the equality comparison. Therefore, if your data type is not bit-hashable, you cannot use it in the following cases:

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

Nonhashable Data Types

For opaque types that are not bit hashable using the built-in hashing function of the database server, specify the CANNOTHASH modifier in the CREATE OPAQUE TYPE statement.

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.

Multirepresentational data types are not bit hashable because they store large quantities of data in a smart large object and then store the large object handle in the user-defined type. It is the smart-large-object handle that makes the multirepresentational data type nonhashable. That is, the CREATE OPAQUE TYPE statement for a multirepresentational data type must include the CANNOTHASH modifier.

Comparison Function for Opaque Data Types

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

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

If you define a compare() function, you must also define the greaterthan(), lessthan(), equal or other functions that use the compare function.

If you overload the 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 precedes the second argument
   • 0 to indicate that the two arguments are the same
   • >0 to indicate that the first argument comes after the second argument

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.