Casting Between a Distinct Type and Its Source Type

Although data of a distinct type has the same representation as its source type, a distinct type cannot be compared directly to its source type. For this reason, when you create a distinct data type, Dynamic Server automatically registers the following explicit casts:

• A cast from the distinct type to its source type
• A cast from the source type to the distinct type

Suppose you create two distinct types: one to handle movie titles and the other to handle music recordings. You might create the following distinct types that are based on the VARCHAR data type:

CREATE DISTINCT TYPE movie_type AS VARCHAR(30);
CREATE DISTINCT TYPE music_type AS VARCHAR(30);

You can then create the entertainment table that includes columns of type movie_type, music_type, and VARCHAR.

CREATE TABLE entertainment 
( 
video         movie_type,
compact_disc  music_type,
laser_disv    VARCHAR(30)
);

To compare a distinct type with its source type or vice versa, you must perform an explicit cast from one data type to the other. For example, suppose you want to check for movies that are available on both video and laser disc. The following statement requires an explicit cast in the WHERE clause to compare a value of a distinct type (music_type) with a value of its source type (VARCHAR). In this example, the source type is explicitly cast to the distinct type.

SELECT video FROM entertainment
   WHERE video = laser_disc::movie_type

However, you might also explicitly cast the distinct type to the source type as the following statement shows:

SELECT video FROM entertainment
   WHERE video::VARCHAR(30) = laser_disc

To perform a conversion between two distinct types that are defined on the same source type, you must make an intermediate cast back to the source type before casting to the target distinct type. The following statement compares a value of music_type with a value of movie_type:

SELECT video FROM entertainment
   WHERE video = compact_disc::VARCHAR(30)::movie_type