How Do You Create a User-Defined Cast?
The CREATE CAST statement registers a cast in the current database. It registers a cast in the syscasts system catalog table. A cast is owned by the person who registers it with CREATE CAST.
The CREATE CAST statement provides the following information about the cast to the database server:
The CREATE CAST statement specifies whether this cast is implicit or explicit.
The CREATE CAST statement can optionally specify the name of the casting function that implements the cast. The database server does not automatically perform data conversion on extended data types. You must specify a casting function if the two data types have different internal structures.
The CREATE CAST statement specifies the source and target data types to determine the direction of the cast. For full data conversion between two data types, you must define one cast in each direction of the conversion.
Choosing the Kind of User-Defined Cast
Universal Server supports two kinds of user-defined casts:
The database server automatically invokes an implicit cast to perform conversions between two data types.
The database server only invokes an explicit cast to perform conversions between two data types when you specify the CAST AS keywords or the double colon (::) cast operator.
Implicit Cast
An implicit cast governs what automatic data conversion occurs for user-defined data types (such as opaque data types, distinct data types, and row types). The database server automatically invokes an implicit cast when it performs the following tasks:
Conversion of one data type to another can involve loss of data. Be careful of creating implicit casts for such conversions. The end user has no ability to control when the database server invokes an implicit cast and therefore cannot avoid the loss of data that is inherent to such a conversion.
The database server invokes an implicit cast automatically, without a cast operator. However, you also can explicitly invoke an implicit cast with the CAST AS keywords or the :: cast operator.
To create an implicit cast, specify the IMPLICIT keyword of the CREATE CAST statement. The following CREATE CAST statement creates an implicit cast from the percent data type to the DECIMAL data type:
Explicit Cast
An explicit cast governs what data conversion an end user can specify for user-defined data types (such as opaque data types, distinct data types, and row types). The database server invokes an explicit cast only when it encounters one of the following syntax structures:
For example, the following expression uses the CAST AS keywords to invoke an explicit cast between the percent and INTEGER data types:
WHERE col1 > (CAST percent AS integer)
For example, the following expression uses the cast operator to invoke an explicit cast between the percent and INTEGER data types:
WHERE col1 > (percent::integer)
For more information on how to invoke explicit casts, see the Informix Guide to SQL: Tutorial.
The conversion of one data type to another can involve loss of data. If you define such conversions as explicit casts, the end user has the ability to control when the loss of data that is inherent to such a conversion is acceptable.
To create an explicit cast, specify the EXPLICIT keyword of the CREATE CAST statement. The following CREATE CAST statement creates an explicit cast from the percent data type to the INTEGER data type:
When you do not specify an IMPLICIT or EXPLICIT keyword, you create an explicit cast because the default is explicit. The following CREATE CAST statement also creates an explicit cast from percent to INTEGER:
Choosing the Cast Mechanism
The database server can implement a cast with one of following mechanisms:
A Straight Cast
A straight cast tells the database server that two data types have the same internal structure. With such a cast, the database server does not need to manipulate data to convert from the source data type to the target data type. Therefore, you do not need to specify a WITH clause in the CREATE CAST statement.
For example, suppose you need to compare values of type INTEGER and a user-defined type my_int that has the same internal structure as the INTEGER type. This conversion does not require a casting function because the database server does not need to perform any manipulation on the values of these two data types to compare them. The following CREATE CAST statements create the explicit casts that allow you to convert between values of type INT and my_int:
The first cast defines a valid conversion from INT to my_int, and the second cast defines a valid conversion from my_int to INT.
System-defined casts have no casting function associated with them. Because a distinct type and its source type have the same internal structure, distinct types do not require casting functions to be cast to their source type. The database server automatically creates explicit casts between a distinct type and its source type.
For the syntax of the CREATE CAST statement, see the Informix Guide to SQL: Syntax.
A Casting Function
The database server provides special SQL-invoked functions, called casting functions, that implement data conversion between two dissimilar data types. When two data types have different storage structures, you must create a casting function that defines how to convert the data in the source data type to data of the target data type.
To create a cast that has a casting function, follow these steps:
1. Write the casting function.
The casting function takes the source data type as its argument and returns the target data type. You can write casting functions in SPL (an SPL function) or in C (an external function).
If you write an external function, you must write the function in an external file, compile the code, and load it into a shared library. If you write an SPL function, you define and register the function with the CREATE FUNCTION statement.
2. Register the casting function with the CREATE FUNCTION statement.
If you create an SPL function, the CREATE FUNCTION statement contains the actual SPL statements of the casting function and registers the function. If you create an external function, CREATE FUNCTION specifies the name of the shared library that contains the compiled code and just registers the function.
3. Register the cast with the CREATE CAST statement.
Use the WITH clause of the CREATE CAST statement to specify the casting function. To invoke a casting function, the function must reside in the current database. However, the function does not need to exist when you register the cast.
For example, suppose you want to compare values of two opaque data types, int_type and float_type. The CREATE FUNCTION statement in Figure 3-1 creates and registers an SPL function, int_to_float() that takes an int_type value as an argument and returns a value of type float_type.
The int_to_float() function uses a nested cast and the support functions of the int_type and float_type opaque types to obtain the return value, as follows:
1. The int_to_float() function converts the int_type argument to LVARCHAR with the inner cast:
CAST(int_arg AS LVARCHAR)
The output support function of the int_type opaque type serves as the casting function for this inner cast. This output support function must be defined as part of the definition of the int_type opaque type; it converts the internal format of int_type to its external (LVARCHAR) format.
2. The int_to_float() function converts the LVARCHAR value to float_type with the outer cast:
CAST((LVARCHAR value from Step 1) AS float_type)
The input support function of the float_type opaque type serves as the casting function for this outer cast. This input support function must be defined as part of the definition of the float_type opaque type; it converts the external (LVARCHAR) format of float_type to its internal format.
Once you create this casting function, you use the CREATE CAST statement to register the function as a cast. The function cannot be used as a cast until it is registered with the CREATE CAST statement. The CREATE CAST statement in Figure 3-2 creates an explicit cast that uses the int_to_float() function as its casting function.
Once you register the function as an explicit cast, the end user can invoke function with the CAST AS keywords or :: cast operator to convert an int_type value to a float_type value. For the syntax of the CREATE FUNCTION and CREATE CAST statements, see the Informix Guide to SQL: Syntax.
Defining the Direction of the Cast
A cast tells the database server how to convert from a source data type to a target data type. The CREATE CAST statement provides the name of the source and target data types for the cast. The source data type is the data type that needs to be converted, and the target data type is the data type. For example, the following CREATE CAST statement creates a cast whose source data type is DECIMAL and whose target data type is a user-defined data type called percent:
When you register a user-defined cast, the combination of source type and target type must be unique within the database.
To provide data conversion between two data types, you must define a cast for each direction of the conversion. For example, the explicit cast in Figure 3-2 enables the database server to convert from the int_type opaque type to the float_type opaque type. Therefore, the end-user can perform the following cast in an INSERT statement to convert an int_type value, it_val, for a float_type column, ft_col:
However, this cast does not provide the inverse conversion: from float_type to int_type. If you tried to insert a float_type value into an int_type column, the database server would generate an error. To enable the database server to perform this conversion, you need to define another casting function, one that takes a float_type argument and returns an int_type value. Figure 3-3 shows the CREATE FUNCTION statement that defines the float_to_int() SPL function.
The float_to_int() function also uses a nested cast and support functions of the int_type and float_type opaque types to obtain the return value:
1. The float_to_int() function converts the float_type value to LVARCHAR with the inner cast.
CAST(float_arg AS LVARCHAR)
The output support function of the float_type opaque type serves as the casting function for this inner cast. This output support function must be defined as part of the definition of the float_type opaque type; it converts the internal format of float_type to its external (LVARCHAR) format.
2. The float_to_int() function converts the LVARCHAR value to int_type with the outer cast.
CAST(LVARCHAR value AS int_type)
The input support function of the int_type opaque type serves as the casting function for this outer cast. This input support function must be defined as part of the definition of the int_type opaque type; it converts the external (LVARCHAR) format of int_type to its internal format.
The CREATE CAST statement in Figure 3-4 creates an explicit cast that uses the int_to_float() function as its casting function.
The end-user can now perform the following cast in an INSERT statement to convert a float_type value, ft_val, for an int_type column, it_col:
Together, the explicit casts in Figure 3-2 and Figure 3-4 enable the database server to convert between the float_type and int_type opaque data types. Each explicit cast provides a casting function that performs one direction of the conversion.
|
