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 the following figure 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 1: 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 the following figure creates an explicit cast that uses the int_to_float() function as its cast function.

Figure 2: 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 HCL OneDB™ Guide to SQL: Syntax.