Informix Guide to SQL: Tutorial
Chapter 13: Casting Data Types
Home
Contents
Index
Master Index
New Book
Casting Distinct Data Types
A distinct type inherits all the functions and casts defined on its source type. Anywhere a cast exists to convert between a source type and particular data type, a cast also exists to convert between the distinct type (that is defined on the source type) and the particular data type. However, to compare or substitute between values of a distinct type and its source type, you must explicitly cast one type to the other. For example, to insert into or update a column of a distinct type with values of the source type, you must explicitly cast the values to the distinct type.
Applying Casts that a Distinct Type Inherits
A distinct type has available for its use any casts that are defined on its source type. Consequently, if a cast exists to convert between values of the source type and
INTEGER
type, a cast also exists to convert between values of the distinct type and
INTEGER
type.
Suppose you create a distinct type,
num_type
, that is based on the
NUMERIC
data type and a table with two columns, one of type
num_type
and the other of type
NUMERIC
.
CREATE DISTINCT TYPE num_type AS NUMERIC;
CREATE TABLE tab(col1 num_type, col2 NUMERIC);
Universal Server can invoke any cast that
num_type
inherits from the
NUMERIC
data type to resolve expressions involving the
num_type
and some other type. In the following
INSERT
statement, the database server invokes a cast to convert the
INT
value
35
to a
num_type
value:
INSERT INTO tab (col1) VALUES (35);
When the preceding statement is parsed, the database server identifies 35 as an
INT
value. Because an implicit cast exists to convert
INT
values to
NUMERIC
values, a cast also exists to convert
INT
values to
num_type
.
Important:
You cannot drop or alter the casts that a distinct type inherits from its source type.
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, Universal 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.
Figure 13-8
shows how you might create two distinct types that are based on the
VARCHAR
data type.
Figure 13-8
CREATE DISTINCT TYPE movie_type AS VARCHAR(30);
CREATE DISTINCT TYPE music_type AS VARCHAR(30);
Figure 13-9
creates the
entertainment
table that includes columns of type
movie_type
,
music_type
, and
VARCHAR
.
Figure 13-9
CREATE TABLE entertainment
(
video movie_type,
compact_disc music_type,
laser_disc 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
In the preceding example, the source type is explicitly cast to the distinct 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 use an explicit cast. The following statement requires an explicit cast to compare a value of
music_type
with a value of
movie_type
:
SELECT video
FROM entertainment
WHERE video = compact_disc::movie_type
Adding and Dropping Casts on a Distinct Type
To enforce strong typing on a distinct type, the database server provides explicit casts to handle conversions between a distinct type and its source type. However, the creator of a distinct type can drop the existing explicit casts and create implicit casts, so that conversions between a distinct type and its source type do not require an explicit cast. The following
DROP CAST
statements drop the two explicit casts that were automatically defined on the
movie_type
that
Figure 13-8
shows:
DROP CAST(movie_type as VARCHAR(30))
DROP CAST(VARCHAR(30) AS movie_type)
Once the existing casts are dropped, you can create two implicit casts to handle conversions between
movie_type and
VARCHAR
. The following
CREATE CAST
statements create two implicit casts:
CREATE IMPLICIT CAST (movie_type AS VARCHAR(30))
CREATE IMPLICIT CAST (VARCHAR(30) AS movie_type)
You cannot create a cast to convert between two data types if such a cast already exists in the database.
Once you create implicit casts to convert between the distinct type and its source type, you can make comparisons between the two types without an explicit cast. In the following statement, the comparison between the
video
and
laser_disc
column requires a conversion. Because an implicit cast has been created, the conversion between
VARCHAR
and
movie_type
is implicit.
SELECT video
FROM entertainment
WHERE video = laser_disc
Informix Guide to SQL: Tutorial
, version 9.1
Copyright © 1998, Informix Software, Inc. All rights reserved.
