User-Defined Types And Methods
User-defined types provides a mechanism for defining new data types that can be used in table definitions and stored procedures. For a user-defined type it is possible to create methods belonging to the type. See Methods for more details.
There are two categories of user-defined types - distinct types and structured 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
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;
There are three different types of methods - constructor, and instance.
Static methods
A static method does not have the connection with an instance of a user-defined type (like a constructor method has), but works almost the same as a function. The only difference is how they are invoked.
An instance method can only be used with an actual instance of a user-defined type.
Instance methods have an implicit parameter SELF that represents the actual value instance used when invoking the method.
Creating Methods
Creating a method is done in two steps, the first is the creation of a method specification and the second is the actual creation.
There can be multiple methods with the same name as long as they either differ by the number of parameters or the type of the parameters.
The method specification can either be given when creating the type or it can be added by using an ALTER TYPE statement.
Examples
CREATE TYPE bool AS boolean
CAST(distinct AS source) WITH bool
METHOD asChar() RETURNS varchar(5);
CREATE METHOD asChar() FOR bool
RETURN CASE WHEN self THEN 'TRUE' ELSE 'FALSE' END;
As can be seen, the type of method is specified in the method specification and the CREATE METHOD statement. (The method type can be omitted if an instance method is created.) It is possible to use all PSM statements when creating a method. The for clause with the type name is needed since it is possible to have methods with the same name and parameters for different user-defined types.
There are some specific rules for constructor methods, the method name must be the same as the name of the user-defined type and the return type must be the user-defined type.
A method specification can be dropped from a type by using a variant of the alter type statement. If there are any method defined using that method specification, that method will be dropped if cascade is specified.
Example
ALTER TYPE ymd DROP CONSTRUCTOR METHOD ymd(date) CASCADE;
ALTER TYPE ymd DROP STATIC METHOD add RESTRICT;
If neither RESTRICT nor CASCADE is specified, RESTRICT is default. Note that it is necessary to specify the data type of the parameters if there are multiple methods with the same name and method type for the user-defined type. The second example will only be successful if there is only one static method named add for the type ymd. To specify a method with no parameters an empty pair of parenthesis () can be used.
See Mimer SQL Reference Manual, CREATE METHOD for additional information.
Methods are invoked differently depending on the type of the method.
Invoking a constructor method
A constructor method is used with the NEW operator when creating a new instance of a user-defined type.
When using the NEW operator, the constructor function will be invoked first. This function assigns default values to all attributes. The constructor function returns the instance and this will be passed as an implicit parameter to the constructor method together with the explicit parameter. The constructor method modifies the attributes and returns the instance. It is possible to have a constructor method without parameters.
Example
BEGIN
DECLARE a ymd;
SET a = NEW ymd(CURRENT_DATE);
END
Invoking an instance method
An instance method is invoked by using dot notation on a expression that evaluates to a user-defined type.
Example
BEGIN
DECLARE a ymd;
DECLARE b int;
SET a = NEW ymd(DATE'2010-04-23');
--
-- invoke the implicitly created instance method m
-- for retrieving the value of the attribute m
--
SET b = a.m()
...
--
-- as the instance method compare returns a user-defined type,
-- the method asChar for this user-defined type can be invoked on that result
--
IF a.compare(2010,11,2).asChar() = 'TRUE' THEN
...
END IF;
END
Invoking a static method
A static method is invoked with a double-colon syntax.
Example
SELECT ymd::add(c) FROM t;
Dropping Methods
Dropping a method will have effects on objects using it. The DROP statement may either have a restrict or cascade option. Restrict means that if there are any objects depending on the method the drop will not be done. If cascade is specified all such objects will be dropped.
Example
DROP STATIC METHOD add FOR ymd CASCADE;
See Mimer SQL Reference Manual, DROP for details.