Multilevel Casting

A multilevel cast refers to an operation that requires two or more levels of casting in an expression to convert a value of one data type to the target data type. Because no casts exist between yen and sterling values, a query that compares the two data types requires multiple casts. The first (inner) cast converts sterling values to dollar values; the second (outer) cast converts dollar values to yen values.

SELECT * FROM manufact_price 
   WHERE japan_price < uk_price::dollar::yen

You might add another cast function to handle yen to sterling conversions directly. The following example creates the function yen_to_sterling() and registers it as a cast. To account for the exchange rate, the function multiplies yen values by .01 to derive equivalent sterling values.

CREATE FUNCTION yen_to_sterling(y yen) 
   RETURNS sterling 
   RETURN (y::DOUBLE PRECISION * .01)::CHAR(20)::sterling; 
END FUNCTION;

CREATE CAST (yen AS sterling WITH yen_to_sterling); 

With the addition of the yen to sterling cast, you can use a single-level cast to compare yen and sterling values, as the following query shows:

SELECT japan_price::sterling, uk_price FROM manufact_price
   WHERE japan_price::sterling) < uk_price;

In the SELECT statement, the explicit cast returns yen values as their sterling equivalents. In the WHERE clause, the cast allows comparisons between yen and sterling values.