Informix Guide to SQL: Syntax SQL Statements

Use the CREATE CAST statement to register a cast that converts data from one data type to another.

A cast is a mechanism that the database server uses to convert one data type to another. The database server uses casts to perform the following tasks:

• To compare two values in the WHERE clause of a SELECT, UPDATE, or DELETE statement
• To pass values as arguments to a user-defined routines
• To return values from user-defined routines

To create a cast, you must have the necessary privileges on both the source data type and the target data type. All users have permission to use the built-in data types. However, to create a cast to or from an opaque type, distinct type, or named-row type requires the Usage privilege on that type.

The CREATE CAST statement registers a cast in the syscasts system catalog table. For more information on syscasts, see the chapter on system catalog tables in the Informix Guide to SQL: Reference.

The CREATE CAST statement defines a cast that converts a source data type to a target data type. Both the source data type and target data type must exist in the database when you execute the CREATE CAST statement to register the cast. The source data type and the target data type have the following restrictions:

• Either the source data type or the target data type, but not both, can be a built-in type.
• Neither the source data type nor the target data type can be a distinct type of the other.
• Neither the source data type nor the target data type can be a collection data type.

To process queries with multiple data types often requires casts that convert data from one data type to another. You can use the CREATE CAST statement to create the following kinds of casts:

• Use the CREATE EXPLICIT CAST statement to define an explicit cast.
• Use the CREATE IMPLICIT CAST statement to define an implicit cast.

An explicit cast is a cast that you must specifically invoke, with either the CAST AS keywords or with the cast operator (::). The database server does not automatically invoke an explicit cast to resolve data type conversions. The EXPLICIT keyword is optional; by default, the CREATE CAST statement creates an explicit cast.

The following CREATE CAST statement defines an explicit cast from the rate_of_return opaque data type to the percent distinct data type:

CREATE EXPLICIT CAST (rate_of_return AS percent 

	WITH rate_to_prcnt)

The following SELECT statement explicitly invokes this explicit cast in its WHERE clause to compare the bond_rate column (of type rate_of_return) to the initial_APR column (of type percent):

SELECT bond_rate FROM bond

WHERE bond_rate::percent > initial_APR

The database server invokes built-in casts to convert from one built-in data type to another built-in type that is not directly substitutable. For example, the database server performs conversion of a character type such as CHAR to a numeric type such as INTEGER through a built-in cast.

An implicit cast is a cast that the database server can invoke automatically when it encounters data types that cannot be compared with built-in casts. This type of cast enables the database server to handle automatically conversions between other data types.

To define an implicit cast, specify the IMPLICIT keyword in the CREATE CAST statement. For example, the following CREATE CAST statement specifies that the database server should automatically use the prcnt_to_char() function when it needs to convert from the CHAR data type to a distinct data type, percent:

CREATE IMPLICIT CAST (CHAR AS percent WITH prcnt_to_char) 

This cast provides the database server with only the ability to automatically convert from the CHAR data type to percent. For the database server to convert from percent to CHAR, you need to define another implicit cast, as follows:

CREATE IMPLICIT CAST (percent AS CHAR WITH char_to_prcnt) 

The database server would automatically invoke the char_to_prcnt() function to evaluate the WHERE clause of the following SELECT statement:

SELECT commission FROM sales_rep

WHERE commission > "25%"

Users can also invoke implicit casts explicitly. For more information on how to explicitly invoke a cast function, see Explicit Casts.

When a built-in cast does not exist for conversion between data types, you can create user-defined casts to make the necessary conversion.

The WITH clause of the CREATE CAST statement specifies the name of the user-defined function to invoke to perform the cast. This function is called the cast function. You must specify a function name unless the source data type and the target data type have identical representations. Two data types have identical representations when the following conditions are met:

• Both data types have the same length and alignment
• Both data types are passed by reference or both are passed by value

The cast function must be registered in the same database as the cast at the time the cast is invoked, but need not exist when the cast is created. The CREATE CAST statement does not check permissions on the specified function name, or even verify that the cast function exists. Each time a user invokes the cast explicitly or implicitly, the database server verifies that the user has Execute privilege on the cast function.

Related statements: CREATE FUNCTION, CREATE DISTINCT TYPE, CREATE OPAQUE TYPE, CREATE ROW TYPE and DROP CAST

For more information about data types, casting, and conversion, see the Data Types segment in this manual and the Informix Guide to SQL: Reference.

For examples that show how to create and use casts, see the Informix Guide to SQL: Tutorial.