Mimer SQL Documentation TOC PREV NEXT INDEX

Mimer SQL Developer Site


Distinct Types


A distinct type is based on a predefined data type.

Example
 CREATE 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.

Example
 CREATE 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, a function for converting to the base type is created. This function has the same name as the base data type.

Example
 SELECT name, integer(length) FROM container;
 

It is not possible to compare two instances of different distinct types. This is regardless of if the types on which the distinct types are based are comparable or not. Also, it is not possible to declare an instance of a distinct type with a value of the type on which the distinct type is based. I.e. a statement like

 SELECT name FROM container WHERE length = 450;
 

is not valid. To do this comparison, either value need to be converted:

 SELECT name FROM container WHERE integer(length) = 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:

Example
 CREATE 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, cast_from_size_to_int(length) FROM container;

Mimer
Mimer Information Technology AB
Voice: +46 18 780 92 00
Fax: +46 18 780 92 40
info@mimer.se
Mimer SQL Documentation TOC PREV NEXT INDEX