User-defined routines and extended data types

The data type system of HCL OneDB™ is an extensible and flexible system that supports the creation of following kinds of data types:

• Extensions of existing data types by, redefining some of the behavior for data types that the database server provides
• Definitions of customized data types by a user

The superstores_demo database creates the distinct data type, percent, in a UDR, as follows:

CREATE DISTINCT TYPE percent AS DECIMAL(5,5);
DROP CAST (DECIMAL(5,5) AS percent);
CREATE IMPLICIT CAST (DECIMAL(5,5) AS percent);
The superstores_demo database creates the following named row types:

• location hierarchy:
  • location_t
  • loc_us_t
  • loc_non_us_t
• customer hierarchy:
  • name_t
  • customer_t
  • retail_t
  • whlsale_t
• orders table
  • ship_t

location_id               SERIAL
loc_type                  CHAR(2)
company                   VARCHAR(20)
street_addr               LIST(VARCHAR(25) NOT NULL)
city                      VARCHAR(25)
country                   VARCHAR(25)

state_code               CHAR(2)
zip                      ROW(code INTEGER, suffix SMALLINT)
phone                    CHAR(18)

province_code            CHAR(2)
zipcode                  CHAR(9)
phone                    CHAR(15)

first               VARCHAR(15)
last                VARCHAR(15)

customer_num                   SERIAL
customer_type                  CHAR(1)
customer_name                  name_t
customer_loc                   INTEGER
contact_dates                  LIST(DATETIME YEAR TO DAY NOT NULL)
cust_discount                  percent
credit_status                  CHAR(1)

credit_num                  CHAR(19)
expiration                  DATE

resale_license              CHAR(15)
terms_net                   SMALLINT

date                  DATE
weight                DECIMAL(8,2)
charge                MONEY(6,2)
instruct              VARCHAR(40)