Define 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 to which the source data type should be converted. For example, the following CREATE CAST statement creates a cast whose source data type is DECIMAL and whose target data type is a UDT called percent:

CREATE CAST (DECIMAL AS percent)

When you register a user-defined cast, the combination of source data type and target data 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 2 enables the database server to convert from the int_type opaque data type to the float_type opaque data type. Therefore, the end user can perform the following cast in an INSERT statement to convert an int_type value, it_val, to a float_type column, ft_col:

INSERT INTO table1 (ft_col) VALUES (it_value::float_type)

However, this cast does not provide the inverse conversion: from float_type to int_type. If you try to insert a float_type value in an int_type column, the database server generates an error. To enable the database server to perform this conversion, you need to define another cast function, one that takes a float_type argument and returns an int_type value. The following figure shows the CREATE FUNCTION statement that defines the float_to_int() SPL function.

Figure 1. An SPL function as a cast function from float_type to int_type

CREATE FUNCTION float_to_int(float_arg float_type) 
   RETURNS int_type 
   RETURN CAST(CAST(float_arg AS LVARCHAR) AS int_type);
END FUNCTION;

The float_to_int() function also uses a nested cast and the support functions of the int_type and float_type opaque types to obtain the return value:

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 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 float_type opaque data type; it converts the internal format of float_type to its external (LVARCHAR) format.
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 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 int_type opaque data type; it converts the external (LVARCHAR) format of int_type to its internal format.

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 float_type to int_type

CREATE EXPLICIT CAST (float_type AS int_type 
   WITH float_to_int);

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:

INSERT INTO table1 (it_col) VALUES (ft_value::int_type)

Together, the explicit casts in Figure 2 and in Figure 2 enable the database server to convert between the float_type and int_type opaque data types. Each explicit cast provides a cast function that performs one direction of the conversion.