Using Explicit Casts on Fields

Before you can explicitly cast between two row types (named or unnamed), whose fields contain different data types, a cast (either system-defined or user-defined) must exist to handle conversions between the corresponding field data types.

When you explicitly cast between two row types, the database server automatically invokes any explicit casts that are necessary to handle conversions between field data types. In other words, when you perform an explicit cast on a row type value, you do not have to explicitly cast individual fields of the row type, unless more than one level of casting is necessary to handle the data type conversion on the field.

The row types and tables in the following example are used throughout this section to show the behavior of explicit casts on named and unnamed row types:

CREATE DISTINCT TYPE d_float AS FLOAT;
CREATE ROW TYPE row_t (a INT, b d_float);

CREATE TABLE tab1 (col1 ROW (a INT, b d_float));
CREATE TABLE tab2(col2 ROW (a INT, b FLOAT));
CREATE TABLE tab3 (col3 row_t); 

Explicit Casts on Fields of an Unnamed Row Type

When a conversion between two row types involves an explicit cast to convert between particular field values, you can explicitly cast the row type value but do not need to explicitly cast the individual field.

The following statement shows how to insert a value into the tab1 table:

INSERT INTO tab1 VALUES (ROW( 3, 5.66::FLOAT::d_float))

To insert a value from col1 of tab1 into col2 of tab2, you must explicitly cast the row value because the database server does not automatically handle conversions between the d_float distinct type of tab1 to the FLOAT type of table tab2:

INSERT INTO tab2 SELECT col1::ROW(a INT, b FLOAT) FROM tab1

In this example, the cast that is used to convert the b field is explicit because the conversion from d_float to FLOAT requires an explicit cast (to convert a distinct type to its source type requires an explicit cast).

In general, to cast between two unnamed row types where one or more of the fields uses an explicit cast, you must explicitly cast at the level of the row type, not at the level of the field.

Explicit Casts on Fields of a Named Row Type

When you explicitly cast a value as a named row type, the database server automatically invokes any implicit or explicit casts that are used to convert field values to the target data type. In the following statement, the explicit cast of col1 to type row_t automatically invokes the explicit cast that converts a field value of type FLOAT to d_float:

INSERT INTO tab3 SELECT col2::row_t FROM tab2

The following INSERT statement includes an explicit cast to the row_t type. The explicit cast to the row type also invokes an explicit cast to convert the b field of type row_t from FLOAT to d_float. In general, an explicit cast to a row type also invokes any explicit casts on the individual fields (one-level deep) that the row type contains to handle conversions.

INSERT INTO tab3 VALUES (ROW(5, 6.55::FLOAT)::row_t)

The following statement is also valid and returns the same results as the preceding statement. However, this statement shows all the explicit casts that are performed to insert a row_t value into the tab3 table.

INSERT INTO tab3 VALUES (ROW(5, 6.55::float::d_float)::row_t)

In the preceding examples, the conversions between the b fields of the row types require two levels of casting. The database server handles any value that contains a decimal point as a DECIMAL type. In addition, no implicit casts exist between the DECIMAL and d_float data types, so two levels of casting are necessary: a cast from DECIMAL to FLOAT and a second cast from FLOAT to d_float.