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.
Home | [ Top of Page | Previous Page | Next Page | Contents | Index ]