When you create UDTs, you also provide the following routines:
A cast performs a conversion between two data types. The database server allows you to write your own cast functions to perform casts. The following sections summarize how you can extend a cast function for built-in and UDTs. For more information on how to extend casts, refer to Creating User-Defined Casts.
The database server provides built-in casts that perform automatic conversions between certain built-in data types. For more information on these built-in casts, refer to the IBM Informix: Guide to SQL Reference.
You cannot create user-defined casts to allow conversions between two built-in data types for which a built-in cast does not currently exist. For more information on when you might want to write new cast functions, refer to Creating a User-Defined Cast.
You can create user-defined casts to perform conversions between most data types, including opaque types, distinct types, row types, and built-in types. You can write cast functions in SPL or in external languages. For example, you can define casts for any of the following UDTs:
You can create casts to convert a UDT to other data types in the database. Developers of opaque data types must also provide functions that serve as cast functions between the internal and external representations of the opaque type. For more information, see Creating an Opaque Data Type.
The database server cannot directly compare a distinct type to its source type. However, the database server automatically registers explicit casts from the distinct type to the source type and conversely. Although a distinct type inherits the casts and functions of its source type, the casts and functions that you define on a distinct type are not available to its source type.
You can create casts to convert a named row data type to another type. For information about how to cast between named row types and unnamed row types, see the IBM Informix: Guide to SQL Tutorial.
For more information on how to create and register casts on extended data types, refer to Creating User-Defined Casts.
An end-user routine is an SQL-invoked function that the SQL user can include in an SQL statement. Such routines provide special functionality that application users often need. An end-user routine might be as simple as "increase the price of every item from XYZ Corporation by 5 percent" or something far more complicated.
This section summarizes how you can extend an end-user routine that operates on the following data types:
The database server provides many functions that end users can use in SQL statements on built-in data types. These functions are called built-in functions to distinguish them from SQL-invoked functions that you define.
You cannot extend an existing built-in function on a built-in data type that it supports. However, you can perform the following extensions:
For more information about built-in functions, see Extending Operators and Built-In Functions.
You can write an end-user routine on any data type that is registered in the database.
For more information about end-user routines, see Creating an End-User Routine.
An aggregate function is an SQL-invoked function that takes values that depend on all the rows that the query selects and returns information about these rows. The database server supports aggregate functions that you write, called user-defined aggregates. You can write user-defined aggregates in SPL or in external languages.
You can extend an aggregate function for built-in and UDTs, as follows:
You cannot create a user-defined aggregate that has the same name as a built-in aggregate and that handles a built-in data type. However, you can define a new aggregate that operates on a built-in data type.
For more information about aggregate functions, see Creating User-Defined Aggregates. Aggregate functions use the support functions to compute the aggregate result. For information on support functions, see Writing Support Functions.
An operator function is an SQL-invoked function that has a corresponding operator symbol (such as '=' or '+'). These operator symbols are used within expressions in an SQL statement.
Operator binding is the implicit invocation of an operator function when an operator symbol is used in an SQL statement. The database server implicitly maps a built-in operator function name to a built-in operator. For example, you can compare two values for equality in either of the following ways.
The following sections summarize how you can extend an operator on built-in and UDTs. For more information on how to extend operators, refer to Extending Operators and Built-In Functions.
The database server provides operator functions that operate on most built-in data types. For a complete list of operator functions, see Extending Operators and Built-In Functions. You cannot extend an operator function that operates on a built-in data type.
You can extend an existing operator to operate on a UDT. When you define the appropriate operator function, operator binding enables SQL statements to use both the function name and its operator symbol on the UDT. You can write operator functions in SPL or an external language.
For example, suppose you create a data type, called Scottish, that represents Scottish names, and you want to order the data type in a different way than the U.S. English collating sequence. You might want the names McDonald and MacDonald to appear together on a phone list. The default relational operators (for example, =) for character strings do not achieve this ordering.
To cause Mc and Mac to appear together, you can create a compare() function that compares two Scottish-name values and treats Mc and Mac identically. The database server uses the compare (Scottish, Scottish) function when it compares two Scottish-name values. If you define a compare() function, you must also define the greaterthan(), lessthan(), equal() or other functions that use the compare function.
For more information, refer to Overloading Routines.
An operator class is the set of operators that the database server associates with a secondary-access method for query optimization and building the index. A secondary-access method (sometimes referred to as an index-access method) is a set of database server functions that build, access, and manipulate an index structure such as a B-tree, an R-tree, or an index structure that a DataBlade module provides.
The query optimizer uses an operator class to determine if an index can be considered in the cost analysis of query plans. The query optimizer can consider use of the index for the given query when the following conditions are true:
For more information on how to optimize queries with UDRs, refer to Optimizing a User-Defined Routine. For more information on how to extend operator classes, refer to Extending an Existing Operator Class.
The database server provides the default operator class for the built-in secondary-access method, the generic B-tree. These operator-class functions handle the built-in data types. You can write new operator-class functions that operate on built-in data types if you want to do the following:
The compare() function implements the ordering scheme for a B-tree index. The strategy functions (greaterthan(), lessthan(), and so on) let the query optimizer use the index for optimizing SQL statements. If you define a compare() function, you must also define the greaterthan(), lessthan(), or other functions that use the compare function.
Because of routine overloading, these functions can have the same name as the functions in the default operator class. For more information, refer to Overloading Routines.
You write operator-class functions with names that are different from any existing operating-class functions associated with the secondary-access method. You then register a new operator class that contains these new operators. The query optimizer can choose an index on this data type when the index uses this new operator class and the SQL statement contains one of the operators in this operator class.
When you create a opaque data type, you can write operator-class functions to do the following:
You write operator-class functions with the same names as those in the existing operator class. These functions extend the existing operator class by implementing its indexing scheme on the opaque data type. The query optimizer can choose an index on this data type when the index uses this operator class and the SQL statement contains one of the operators in this operator class.
Because of routine overloading, these functions can have the same name as the functions in the default operator class. For more information on routine overloading, refer to Overloading Routines.
You supply the support and strategy functions that the access method requires. These functions define the new operators that the query optimizer can recognize as associated with the secondary-access method. The requirements for the support and strategy functions vary from one access method to another. You must consult the documentation for the access method before defining a new operator class.
Optimization functions help the query optimizer determine the most efficient query plan for a particular SQL statement. These optimization functions are as follows.
The database server provides optimization functions for the built-in data types. You can write an optimization function on any UDT that is registered in the database. You cannot extend existing optimization for built-in types through optimization functions.
For more information about optimization functions, see Improving UDR Performance.
When you define a new opaque data type, you provide support functions that enable the database server to operate on the data type. The database server requires some support functions, and others are optional. The following list shows the standard functions that you define to support opaque data types:
For more information on support functions for opaque data types, refer to Writing Support Functions.
An access method is a set of functions that the database server uses to access and manipulate a table or an index. The two types of access methods are as follows:
A primary-access method is a set of routines that perform all the operations needed to make a table available to a database server, such as create, drop, insert, delete, update, and scan. The database server provides a built-in primary-access method.
A secondary-access method is a set of routines that perform all the operations needed to make an index available to a database server, such as create, drop, insert, delete, update, and scan. The database server provides the B-tree and R-tree secondary-access methods. For information about R-tree indexes, refer to the IBM Informix: R-Tree Index User's Guide.
DataBlade modules can provide other primary- and secondary-access methods. For more information, refer to the IBM Informix: Virtual-Table Interface Programmer's Guide and the IBM Informix: Virtual-Index Interface Programmer's Guide.
Home | [ Top of Page | Previous Page | Next Page | Contents | Index ]