For more information on casts, refer to Cast Functions.
The following sections describe source data type restrictions and provide procedures for routine resolution with these source types.
The candidate list can contain a routine with a parameter that is the source data type of the invoked routine argument. If the source type is itself a distinct type, the database server checks the source type of that distinct type. However, if the source type is not in the precedence list for that data type, the routine-resolution process eliminates that candidate.
For example, suppose you create the following distinct data types and table:
CREATE DISTINCT TYPE pounds AS INT; CREATE DISTINCT TYPE stones AS INT; CREATE TABLE test(p pounds, s stones);
Figure 3 shows a sample query that an SQL user might execute.
SELECT * FROM test WHERE p=s;
Although the source data types of the two arguments are the same, this query fails because p and s are different distinct data types. The equal() function cannot compare these two different data types.
The database server chooses the built-in equals function when you explicitly cast the arguments. If you modify the SELECT statement as follows, the database server can invoke the equals(int,int) function, and the comparison succeeds:
SELECT * FROM test WHERE p::INT = s::INT;
You can also write and register the following additional functions to allow the SQL user to use the SELECT statement that Figure 3 shows:
CREATE FUNCTION equals(pounds, stones) ...
The advantage of creating an overloaded equals() function is that the SQL user does not need to know that these are new data types that require explicitly casting.
CREATE IMPLICIT CAST (pounds AS stones); CREATE IMPLICIT CAST (stones AS INT);
If the source type is a built-in data type, the distinct type does not inherit any built-in casts provided for the built-in type, but it does inherit any user-defined casts that are defined on the source type. For example, suppose you create the following distinct data type and table:
CREATE DISTINCT TYPE inches AS FLOAT; CREATE TABLE test(col1 inches); INSERT INTO test VALUES (2.5::FLOAT::inches);
An SQL user might execute the following sample query:
SELECT 4.8 + col1 FROM test;
Although the source data type of the col1 argument has a built-in cast function to convert from FLOAT to DECIMAL (the 4.8 is DECIMAL), this query fails because the distinct type inches does not inherit the built-in cast.
You must use explicit casts in the SQL query. The following queries succeed:
SELECT 4.8 + col1::INT from test; SELECT 4.8::FLOAT::inches + col1 FROM test;
A collection data type is a complex data type whose instances are groups of elements of the same data type that are stored in a SET, MULTISET, or LIST. An element within a collection can be an opaque data type, distinct data type, built-in data type, collection data type, or row type.
Home | [ Top of Page | Previous Page | Next Page | Contents | Index ]