Create user-defined type.
A new type is defined. A user-defined type may be used as the data type for columns in
ALTER TABLEstatements. It can also be used in stored procedures and triggers as the type for variables and parameters.
The type-name should follow the normal rules for naming database objects (see Identifiers). If the type-name is unqualified, the type will be created in the schema with the same name as the current ident. If the type-name is qualified with a schema name, this schema must be owned by the current ident. The permitted values for data-type are described in Data Types in SQL Statements.
A distinct type has a single data type whereas a structured type has a list of attributes.
User-defined types are strongly typed, which means that it is only possible to compare values of the same type. When comparing a predefined data type and a distinct user-defined type a type cast must be used. For this purpose there are two routines created automatically when the type is created. Firstly, a function that can be used for casting from the type on which the user-defined type is based to the distinct type. If a cast-source clause is specified the identifier will be used as the name for the function, otherwise the function will have the same name as the user-defined type. Secondly, a function for casting from the user-defined type to the type on which it is based is also created. If cast distinct as source is specified the identifier in this clause is used for the function otherwise the name depends on the source type as seen in the following table.
National character varying
Any interval type
Binary large object
Character large object
National character large object
ExamplesCREATE TYPE weight AS int; CREATE FUNCTION checkWeight(w weight) RETURNS boolean RETURN integer(w) > 100; SET :v = checkWeight(weight(200)); BEGIN DECLARE w weight; DECLARE i int; ... SET i = integer(w); ... SET w = weight(i); ... END
The following access options may be specified:
- CONTAINS SQL
The method may not contain any data-manipulation-statements. All other procedural-sql-statements are permitted. The method may only invoke methods, functions and procedures with the access option CONTAINS SQL. This option effectively prevents a routine from performing read or write operations on data in the database.
- READS SQL DATA
All procedural-sql-statements are permitted except those performing updates (i.e. DELETE, INSERT and UPDATE). The method may only invoke methods, functions and procedures with the access option CONTAINS SQL or READS SQL DATA.
- MODIFIES SQL DATA
If no access options is specified, CONTAINS SQL is implicit
The type-name must be unique within a schema.
A method specification must be unique for a user-defined type with regard to the number of parameters and data types. This means that user-defined type may have multiple method specifications with the same name as long as either the number of parameters differ or if the data types for the parameters differ.
If a parameter name is specified in a parameter list it must be unique within the parameter list.
The ROW data type cannot be used at any place in a type definition.
A domain may not be used as the type for a distinct user-defined type.
The parameter mode for a parameter cannot be specified. It is always IN.
cast-optioncan only be specified for a distinct type.
When dropping a type with cascade option, any column using that type will be dropped. If this column is the last column in the table, the table will be dropped as well. See DROP TYPE for more details.
The ALTER TYPE statement can be used for adding and dropping method specifications. (See ALTER TYPE.)
The keywords FINAL and INSTANTIABLE are supported for compliance with SQL-2016. SQL-2016 has support for single inheritance and polymorphism, which is not supported in this version of Mimer SQL.
Mimer Information Technology AB
Phone: +46 18 780 92 00