Choosing the Cast Mechanism

Home | Previous Page | Next Page Creating User-Defined Casts > Creating a User-Defined Cast >

Choosing the Cast Mechanism

The CREATE CAST statement can optionally specify the name of a cast function that implements the cast. The database server does not automatically perform data conversion on extended data types. You must specify a cast function if the two data types have different internal structures.

The database server can implement a cast with one of following mechanisms:

Perform a straight cast if two data types have internal structures that are the same
Call a cast function to perform the data conversion

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 the values of an INTEGER data type and a UDT my_int that has the same internal structure as the INTEGER data type. This conversion does not require a cast 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 data type INT and my_int:

CREATE CAST (INT AS my_int)
CREATE CAST (my_int AS 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.

Built-in casts have no cast function associated with them. Because a distinct data type and its source data type have the same internal structure, distinct types do not require cast functions to be cast to their source data type. The database server automatically creates explicit casts between a distinct data type and its source data type.

Cast Function

You can create special SQL-invoked functions, called cast functions, that implement data conversion between two dissimilar data types. When two data types have different storage structures, you must create a cast 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 cast function

Write the cast function.
The cast function takes the source data type as its argument and returns the target data type.
Register the cast function with the CREATE FUNCTION statement.
Register the cast with the CREATE CAST statement.
Use the WITH clause of the CREATE CAST statement to specify the cast function. To invoke a cast function, the function must reside in the current database. However, the cast function does not need to exist when you register the cast.

Example of a Cast Function

For example, suppose you want to compare values of two opaque data types, int_type and float_type. Both types have an external LVARCHAR format that you can use as an intermediate type for converting from one to the other. The CREATE FUNCTION statement in Figure 7 creates and registers an SPL function, int_to_float(), as an argument. It casts the int_type input value to an LVARCHAR, and then casts the LVARCHAR result to float_type and returns the float_type result.

Figure 7. An SPL Function as a Cast Function from int_type to float_type

CREATE FUNCTION int_to_float(int_arg int_type) 
   RETURNS float_type 
   RETURN CAST(CAST(int_arg AS LVARCHAR) AS float_type);
END FUNCTION;

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:

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 data type serves as the cast function for this inner cast. This output support function must be defined as part of the definition of the int_type opaque data type; it converts the internal format of int_type to its external (LVARCHAR) format.
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 data type serves as the cast function for this outer cast. This input support function must be defined as part of the definition of the float_type opaque data type; it converts the external (LVARCHAR) format of float_type to its internal format.

For information about input and output support functions, refer to Locale-Sensitive Input and Output Support Functions.

After you create this cast function, use the CREATE CAST statement to register the function as a cast. You cannot use the function as a cast until you register it with the CREATE CAST statement. The CREATE CAST statement in Figure 8 creates an explicit cast that uses the int_to_float() function as its cast function.

Figure 8. An Explicit Cast from int_type to a float_type

CREATE EXPLICIT CAST (int_type AS float_type 
   WITH int_to_float);

After you register the function as an explicit cast, the end user can invoke the function with the CAST AS keywords or with the :: cast operator to convert an int_type value to a float_type value. For the syntax of the CREATE FUNCTION and CREATE CAST statements, refer to the IBM Informix: Guide to SQL Syntax.