User Defined Types

The MonetDB kernel supports creation of user-defined types, e.g. complex types.

Relationship between SQL and MAL world is expressed using an external name.

type_definition:
   CREATE TYPE [ IF NOT EXISTS ] [ schema_name '.' ] type_name  EXTERNAL NAME  MAL_type_name

drop_type:
   DROP   TYPE [ IF EXISTS ]     [ schema_name '.' ] type_name [ RESTRICT | CASCADE ]

The type_name must be unique within the schema.

Note: These statements are MonetDB specific. EXTERNAL implies language MAL.

The type implementation (structure, operators & functions both scalar and bulk) must be specified externally in C code and MAL script.
The C code implementation of new atomary types is best postponed until there is no other performance-wise acceptable solution. Addition of an atomary type in the kernel would be beneficial if it is also complemented with bulk-operations for fast bulk processing, functions which accept the new type as argument and type specific optimizers which could exploit their semantics.

For examples see the C implementations of data types: blob, inet, json, url, uuid and xml and SQL script code for json type.

See also the MAL Type System.

Example

CREATE TYPE xml EXTERNAL NAME xml;

SELECT * FROM sys.types WHERE sqlname = 'xml';
-- returns 1 row for the new type: xml

CREATE TABLE msgs (msg xml);

SELECT * FROM msgs;

DROP TYPE xml CASCADE;
-- note: this drop type will also drop the created table because CASCADE is specified

Associated system table: sys.types