User-defined data types

UDT stands for user-defined type, which allows you to create custom types suitable for your business structure. Previously, it was only supported within PL, but starting from OceanBase Database V4.4.2 BP2, it is also supported as a table column type, providing a more flexible data model for table creation.

Currently, the following two types of UDTs are supported as table columns (including complex nested types):

• OBJECT
• VARRAY

Syntax support

The following syntax features are currently supported:

• Supports creating tables with UDT column types using CREATE.
• Supports adding, dropping, and modifying UDT columns using ALTER TABLE ADD COLUMN, DROP COLUMN, and MODIFY COLUMN.
• Supports inserting data into UDT columns using INSERT.
• Supports updating arrays of UDT columns using UPDATE.
• Supports accessing data in UDT columns.
• Supports accessing attributes or member functions of UDT columns.
• Supports using VARRAY-type UDT columns in TABLE FUNCTIONS.

Usage examples

• Create an object type

  CREATE TYPE type_obj1 AS OBJECT (
      a int,
      b int
  );
  
  CREATE TYPE type_array AS VARRAY(10) of int;
  

• Create a table with UDT columns

  CREATE TABLE t_udt (c1 int, c2 type_obj1, c3 type_array);
  

• Insert data

  INSERT INTO T_UDT VALUES(1, TYPE_OBJ1(1,2), TYPE_ARRAY(1,2,3));
  

• UPDATE data

  UPDATE T_UDT SET C2 = TYPE_OBJ1(11,22);
  

• Query a UDT column

  SELECT C1, C2, C3 FROM T_UDT;
  

• Query UDT column attributes

  SELECT T.C2.A, T.C2.B FROM T_UDT T;
  

• Access a varray-type UDT column using table function

  SELECT tf.* from T_UDT, table(T_UDT.c3) tf;
  

Limitations

• A UDT that is referenced by a table column does not support changing its attributes using alter type.
• A UDT that is referenced by a table does not support drop force.
• The object.addr syntax is not supported in constraints or order by clauses.
• Updates do not support changing attributes.
• In the current version of OceanBase, written UDT COLUMN values require CDC processing in online schema mode.