A distinct type is based on a predefined data type.
ExampleCREATE TYPE size AS INTEGER;
This creates a distinct type. When a distinct type is created, there is an implicit creation of a function for converting a value of the type on which the user-defined type is based to the user-defined type. By default this function has the same name as the user-defined type.
ExampleCREATE TABLE container (name NCHAR VARYING(30), length size); INSERT INTO container VALUES ('Large x450', size(450));
In addition, when the user-defined type is created, an instance method (see Instance methods) for converting to the base type is created. This method has the same name as the base data type. A method is invoked by using dot notation.
ExampleSELECT name, length.integer() FROM container;
If the method does not have any parameters, the parentheses may be omitted. (It may be good practice to always include the parentheses to make the code more readable.)
It is not possible to compare two instances of different user-defined types. This is regardless of if the types on which the user-defined types are based are comparable or not. Also, it is not possible to declare an instance of a user-defined type with a value of the type on which the user-defined type is based. I.e. a statement likeSELECT name FROM container WHERE length = 450;
is not valid. To do this comparison, either value need to be converted:SELECT name FROM container WHERE length.integer() = 450; SELECT name FROM container WHERE length = size(450);
It is possible to override the default naming of the implicitly created function and method. This is done by using the following syntax:
ExampleCREATE TYPE size AS integer CAST(source as distinct) WITH cast_from_int_to_size CAST(distinct as source) WITH cast_from_size_to_int; INSERT INTO container VALUES ('Large x450', cast_from_int_to_size(450)); SELECT name, length.cast_from_size_to_int() FROM container;
Mimer Information Technology AB
Voice: +46 18 780 92 00
Fax: +46 18 780 92 40