A structured user-defined type may have multiple attributes.
ExampleCREATE TYPE record AS (title nchar varying(30), artist nchar varying(30), duration interval minute(3) to second);
An attribute can be given a default value when created, e.g.CREATE TYPE strtype AS (a1 boolean DEFAULT TRUE, ...);
The default value can be any value that is compatible with the type of the attribute, see Mimer SQL Reference Manual, Default Values. If there is no default clause for an attribute, it will be set null initially.
To create an instance of a user-defined type, there is a function with the same name as the user-defined type. This function is created implicitly when the user-defined type is created. It is invoked by using the
ExampleBEGIN DECLARE rec record; SET rec = NEW record(); END;
When the variable rec is declared, it is set to null. By using the new operator a new instance of the user-defined type record is created. This means that the variable rec is set to not null and that each attribute is given it's default value. In this case all attributes will be set to null as there are no default values in the type definition. Note the difference between an instance being null and an instance having all attributes being null.
Example:BEGIN DECLARE album record; IF album IS NULL THEN -- -- this will be true -- END IF; SET album = NEW record(); IF album IS NULL THEN -- -- this will not be true -- END IF; END
It is possible to create constructor methods that are used to initiate attributes when the default value is not suitable. See Constructor methods.
To access the attributes of a structured type there are two methods created for each attribute when the user-defined type is created. Both these methods have the same name as the attribute. The first, which is called an observer method, is used to get the current value for an attribute. The second, which is called a mutator method, is used to set the value for an attribute. There are different ways to use these methods.
ExampleBEGIN DECLARE album record; SET album = NEW record(); -- -- invoke the mutator method for the attribute -- SET album.duration = interval '33:20' minute to second; -- -- alternatively -- SET album = (NEW record()).duration(interval '3:20' minute to second); -- -- multiple attributes can be assigned in one statement -- SET album = (NEW record()).title('5:th symphony').artist('Quinto'). duration(interval '3:20' minute to second); -- -- this is possible since each mutator method returns an instance of -- the type record -- -- invoke the observer method for the attribute -- IF album.duration() IS NOT NULL THEN ... END IF; -- -- as the method has no parameters, the below is also valid -- IF album.duration IS NOT NULL THEN ... END IF; END
The data type for an attribute may be a predefined data type or a user-defined type.
ExampleCREATE TYPE adress AS (adress nchar varying(30), city nchar varying(30)); CREATE TYPE name AS (firstname nchar varying(30), lastname nchar varying(30)); CREATE TYPE person AS (name name, adress adress); -- -- the observer method name returns an instance of type name and thus -- we can apply the observer method firstname to this instance -- CREATE FUNCTION tist(p person) RETURNS boolean RETURN p.name.firstname = n'Yessir';
A type can be modified by adding or dropping attributes. Currently it is not possible to add or drop attributes for a structured user-defined type if it is used in a table definition.
Example:ALTER TYPE person ADD ATTRIBUTE birthdate date; ALTER TYPE person DROP ATTRIBUTE birthdate;
It is not possible to change the data type of an attribute.
Dropping a user-defined type will have effects on objects using it. The
DROPstatement may either have a restrict or cascade option. Restrict means that if there are any objects depending on the user-defined type the drop will not be done. If cascade is specified all such objects will be dropped.
ExampleCREATE TYPE example AS (a1 int,a2 date); CREATE TABLE example(c1 int,c2 example); -- -- this will drop the column c2 from the table example -- DROP TYPE example CASCADE;
Comparison of Structured Types.
It is currently not possible to compare two instances of the same structured user-defined type directly. If there is a need to compare structured types this must be done by comparing the individual attributes of the two instances.
Mimer Information Technology AB
Voice: +46 18 780 92 00
Fax: +46 18 780 92 40