# CREATE TYPE #### 功能描述 该语法用于创建**用户自定义** **类型（User-defined type，简称UDT)** ，本质是将一组相关字段或值封装成一个新的、可复用的数据类型，类似编程语言中的"结构体"，让数据库表设计更贴合业务逻辑，减少冗余、提升可读性。**该功能仅适用于行存表。** 当前支持创建的UDT包括：复合类型、基本类型、shell类型和枚举类型。 - **复合类型** 复合类型通过"属性名 + 对应数据类型"的列表来定义；若属性的数据类型支持排序，还可专门为该属性指定排序规则。复合类型的本质与数据表的行类型完全一致，但如果仅需定义一套结构化的数据类型，通过使用CREATE TYPE语法可实现更轻量化的类型定义，而无需创建一个实际的数据表。 独立的复合类型具备很高的实用价值，例如作为自定义函数的参数类型或返回值类型。典型场景如用户表需存储家庭、工作地址，地址包含省、市、详细地址、邮编等信息，需复用地址结构，避免表字段冗余，此时可创建复合类型。 需要注意的是，创建复合类型时，用户必须拥有其所有属性对应数据类型的USAGE权限。 - **基本类型** 创建一种全新的基本类型（非组合、枚举），需自定义输入、 输出函数实现类型的编解码，适用于业务专属的特殊类型（如手机号、身份证号、带币种的金额）。使用了该基本类型的自定义函数必须使用C语言或者另外一种底层语言所编写。 典型场景，例如用户联系方式需强制为11位手机号，自动过滤非数字字符，避免非法格式录入。 - **shell类型** shell类型是一种用于后面要定义的类型的占位符，通过发出一个只带类型名参数的CREATE TYPE命令来创建该类型。在创建**基本类型**时，需要shell类型作为一种向前引用。 - **枚举类型** 由若干个标签构成的列表，每一个标签值都是一个非空字符串，且字符串长度必须不超过64个字节。其核心作用是限定字段取值为固定的枚举值集合，替代VARCHAR等其他数据类型的检查约束，提升数据一致性和可读性，例如可以避免拼写错误。 典型场景如，订单状态仅允许：待支付、已支付、已发货 、已送达、已取消，需保证所有表（订单表、退款表）的状态值一致，避免填入其他取值，导致拼写错误。 #### 注意事项 - **该功能仅适用于行存表，列存表不支持。** - 如果给定一个模式名，那么该类型将被创建在指定的模式中，否则它会被创建在当前模式中。类型名称必须与同一个模式中任何现有的类型或者域有所区别（因为表具有相关的数据类型，类型名称也必须与同一个模式中任何现有表的名字不同）。 - 自定义基本类型时，参数可以以任意顺序出现，input_function和output_function为必选参数，其它为可选参数。 - 输入和输出函数能被声明为具有新类型的结果或参数是因为：必须在创建新类型之前先创建这两个函数。而新类型应该首先被定义为一种shell type，它是一种占位符类型，除了名称和拥有者之外它没有其他属性，这可以通过不带额外参数的命令CREATE TYPE name做到，接着用C语言写的I/O函数可以被定义为引用这种shell type。最后，用带有完整定义的CREATE TYPE把该shell type替换为一个完全的、合法的类型定义，之后新类型就可以正常使用了。 #### 语法格式 **复合类型** ``` CREATE TYPE name AS ( [ attribute_name data_type [ COLLATE collation ] [, ... ] ] ) ``` **基本类型** ``` CREATE TYPE name ( INPUT = input_function, OUTPUT = output_function [ , RECEIVE = receive_function ] [ , SEND = send_function ] [ , TYPMOD_IN = type_modifier_input_function ] [ , TYPMOD_OUT = type_modifier_output_function ] [ , ANALYZE = analyze_function ] [ , INTERNALLENGTH = { internallength | VARIABLE } ] [ , PASSEDBYVALUE ] [ , ALIGNMENT = alignment ] [ , STORAGE = storage ] [ , LIKE = like_type ] [ , CATEGORY = category ] [ , PREFERRED = preferred ] [ , DEFAULT = default ] [ , ELEMENT = element ] [ , DELIMITER = delimiter ] [ , COLLATABLE = collatable ] ) ``` **shell类型** ``` CREATE TYPE name ``` **枚举类型** ``` CREATE TYPE name AS ENUM ( [ 'label' [, ... ] ] ) ``` #### 参数说明 表1复合类型参数说明 | 参数 | 描述 | 取值范围 | |:---|:---|:---| | name | 要创建的类型的名称，可以用模式修饰。 | 需符合[标识符命名规范](https://support.huaweicloud.com/sqlreference-911-dws/dws_06_0007.html#ZH-CN_TOPIC_0000002202094021__section1475018612353)。 | | attribute_name | 复合类型的一个属性（列）的名称。 | - | | data_type | 复合类型属性对应的数据类型，必须是DWS已存在的类型（比如 int、varchar (50)、date 等）。 | - | | collation | 复合类型属性的 "排序规则"，控制字符串类数据的排序、比较逻辑（比如中文拼音排序、大小写不敏感排序）。 | - | 表2基本类型参数说明 | 参数 | 描述 | 取值范围 | |:---|:---|:---| | name | 要创建的类型的名称，可以用模式修饰。 | 需符合[标识符命名规范](https://support.huaweicloud.com/sqlreference-911-dws/dws_06_0007.html#ZH-CN_TOPIC_0000002202094021__section1475018612353)。 | | input_function | 将数据从类型的**外部文本形式**转换为内部形式的函数名。 输入函数可以被声明为有一个cstring类型的参数，或者有三个类型分别为cstring、 oid、integer的参数。 - cstring参数是以C字符串存在的输入文本。 - oid参数是该类型自身的OID（对于数组类型则是其元素类型的OID）。 - integer参数是目标列的typmod，如果typmod不明确，则将传递 -1。 输入函数必须返回一个该数据类型本身的值。通常，一个输入函数应该被声明为STRICT。 如果不是这样，在读到一个NULL输入值时，调用输入函数时第一个参数会是NULL。在这种情况下，该函数必须仍然返回NULL，除非调用函数发生了错误（这种情况主要是想支持域输入函数，域输入函数可能需要拒绝NULL输入）。 | - | | output_function | 将数据从类型的内部形式转换为**外部文本形式**的函数名。 输出函数必须被声明为有一个新数据类型的参数。输出函数必须返回类型cstring。对于NULL值不会调用输出函数。 | - | | receive_function（可选参数） | 将数据从类型的**外部二进制形式**转换成内部形式的函数名。 如果没有该函数，该类型不能参与到二进制输入中。二进制表达转换成内部形式代价更低，然而却更容易移植（例如，标准的整数数据类型使用网络字节序作为外部二进制表达，而内部表达是机器本地的字节序）。receive_function应该执行足够的检查以确保该值是有效的。 接收函数可以被声明为有一个internal类型的参数，或者有三个类型分别为internal、oid、integer的参数。 - internal参数是一个指向StringInfo缓冲区的指针，其中保存着接收到的字节串。 - oid和integer参数和文本输入函数的相同。 接收函数必须返回一个该数据类型本身的值。通常，一个接收函数应该被声明为STRICT。如果不是这样，在读到一个NULL输入值时调用接收函数时第一个参数会是NULL。在这种情况下，该函数必须仍然返回NULL，除非接收函数发生了错误（这种情况主要是想支持域接收函数，域接收函数可能需要拒绝NULL输入）。 | - | | send_function（可选参数） | 将数据从类型的内部形式转换为**外部二进制形式**的函数名。 如果没有该函数，该类型将不能参与到二进制输出中。发送函数必须被声明为有一个新数据类型的参数。发送函数必须返回类型bytea。对于NULL值不会调用发送函数。 | - | | type_modifier_input_function（可选参数）参数。 | 将类型的**修饰符数组**转换为内部形式的函数名。 如果类型支持修饰符（附加在类型声明上的可选约束，例如，char(5)或numeric(30,2)），则需要可选的type_modifier_input_function和type_modifier_output_function。DWS允许用户定义的类型有一个或者多个简单常量或者标识符作为修饰符。不过，为了存储在系统目录中，该信息必须能被打包到一个非负整数值中。所声明的修饰符会被以cstring数组的形式传递给type_modifier_input_function。type_modifier_input_function必须检查该值的合法性（如果值错误就抛出一个错误），如果值正确，要返回一个非负integer值，该值将被存储在"typmod"列中。如果类型没有type_modifier_input_function则类型修饰符将被拒绝。type_modifier_output_function把内部的整数typmod值转换回正确的形式用于用户显示。type_modifier_output_function必须返回一个cstring值，该值就是追加到类型名称后的字符串。例如，numeric的函数可能会返回(30,2)。如果默认的显示格式就是只把存储的typmod整数值放在圆括号内，则允许省略type_modifier_output_function。 | - | | type_modifier_output_function（可选参数） | 将类型的修饰符的内部形式转换为外部文本形式的函数名。 | - | | analyze_function（可选参数） | 为该数据类型执行统计分析的函数名的可选参数。 默认情况下，如果该类型有一个默认的B-Tree操作符类，ANALYZE将尝试用类型的"equals"和"less-than"操作符来收集统计信息。这种行为对于非标量类型并不合适，因此可以通过指定一个自定义分析函数来覆盖这种行为。分析函数必须被声明为有一个类型为internal的参数，并且返回一个boolean结果。 | - | | internallength（可选参数） | 一个数字常量，用于指定新类型的内部表达的字节长度。默认为变长。 虽然只有I/O函数和其他为该类型创建的函数才知道新类型的内部表达的细节， 但是内部表达的一些属性必须被向DWS声明。其中最重要的是internallength。基本数据类型可以是定长的（这种情况下internallength是一个正整数）或者是变长的（把internallength设置为VARIABLE，在内部通过把typlen设置为-1表示）。所有变长类型的内部表达都必须以一个4字节整数开始，internallength定义了总长度。 | - | | PASSEDBYVALUE（可选参数） | 表示此数据类型的值需要被传值而不是传引用。传值的类型必须是定长的，并且它们的内部表达不能超过Datum类型（某些机器上是4字节，其他机器上是8字节）的尺寸。 | - | | alignment（可选参数） | 该参数指定数据类型的存储对齐需求。如果被指定，必须是char、int2、int4或者double。默认是int4。 允许的值等同于以1、2、4或8字节边界对齐。要注意变长类型的alignment参数必须至少为4，因为它们需要包含一个int4作为它们的第一个组成部分。 | - | | storage（可选参数） | 该数据类型的存储策略。 如果被指定，必须是plain、external、extended或者main。 默认是plain。 - plain指定该类型的数据将总是被存储在线内并且不会被压缩。（对定长类型只允许plain） - extended指定系统将首先尝试压缩一个长的数据值，并且将在数据仍然太长的情况下把值移出主表行。 - external允许值被移出主表， 但是系统将不会尝试对它进行压缩。 - main允许压缩，但是不鼓励把值移出主表（如果没有其他办法让行的大小变得合适，具有这种存储策略的数据项仍将被移出主表，但比起extended以及external项来，这种存储策略的数据项会被优先考虑保留在主表中）。 除plain之外所有的storage值都暗示该数据类型的函数能处理被TOAST过的值。指定的值仅决定一种可TOAST数据类型的列的默认TOAST存储策略，用户可以使用ALTER TABLE SET STORAGE为列选取其他策略。 | - | | like_type（可选参数） | 与新类型具有相同表达的现有数据类型的名称。会从这个类型中复制internallength、 passedbyvalue、 alignment以及storage的值（ 除非在这个CREATE TYPE命令的其他地方用显式说明覆盖）。 当新类型的底层实现是以一种现有的类型为参考时，用这种方式指定表达特别有用。 | - | | category（可选参数） | 此类型的分类码（单个ASCII字符）。 默认为'U'，表示用户定义类型。为了创建自定义分类， 也可以选择其他ASCII字符。 category和preferred参数可以被用来帮助控制在混淆的情况下应用哪一种隐式造型。每一种数据类型都属于一个用单个ASCII字符命名的分类，并且每一种类型可以是其所属分类中的"首选"。当有助于解决重载函数或操作符时，解析器将优先造型到首选类型（但是只能从同类的其他类型造型）。对于没有隐式转换到或来自任意其他类型的类型，让这些设置保持默认即可。不过，对于有隐式转换的相关类型的组，把它们都标记为属于同一个类别并且选择一种或两种"最常用"的类型作为该类别的首选通常是很有用的。在把一种用户定义的类型增加到一个现有的内建类别（例如，数字或者字符串类型）中时，category参数特别有用。不过，也可以创建新的全部是用户定义类型的类别。对这样的类别，可选择除大写字母之外的任何ASCII字符。 | - | | preferred（可选参数） | 如果此类型是其类型分类中的优先类型则为TRUE，否则为FALSE。默认为FALSE。在现有类型分类中创建新的优先类型要非常谨慎， 因为这可能会导致很大的改变。 | - | | default（可选参数） | 数据类型的默认值。如果被省略，默认值是空。 如果用户希望该数据类型的列被默认为某种非空值，可以指定一个默认值。默认值可以用DEFAULT关键词指定（这样一个默认值可以被附加到一个特定列的显式DEFAULT子句覆盖）。 | - | | element（可选参数） | 被创建的类型是一个数组，element指定了数组元素的类型。例如，要定义一个4字节整数的数组（int4）， 应指定ELEMENT = int4。 | - | | delimiter（可选参数） | 指定此类型组成的数组中分隔值的定界符。 可以把delimiter设置为一个特定字符，默认的定界符是逗号（,）。注意定界符是与数组元素类型相关的，而不是数组类型本身相关。 | - | | collatable（可选参数） | 如果此类型的操作可以使用排序规则信息，则为TRUE。默认为FALSE。 如果collatable为TRUE，此类型的列定义和表达式可能通过使用COLLATE子句携带有排序规则信息。在该类型上操作的函数的实现负责真正利用这些信息，仅把类型标记为可排序的并不会让它们自动地去使用这类信息。 | - | 表3shell类型参数说明 | 参数 | 描述 | 取值范围 | |:---|:---|:---| | name | 要创建的类型的名称，可以用模式修饰。 | 需符合[标识符命名规范](https://support.huaweicloud.com/sqlreference-911-dws/dws_06_0007.html#ZH-CN_TOPIC_0000002202094021__section1475018612353)。 | 表4枚举类型参数说明 | 参数 | 描述 | 取值范围 | |:---|:---|:---| | name | 要创建的类型的名称，可以用模式修饰。 | 需符合[标识符命名规范](https://support.huaweicloud.com/sqlreference-911-dws/dws_06_0007.html#ZH-CN_TOPIC_0000002202094021__section1475018612353)。 | | label（可选参数） | 与枚举类型的一个值相关的文本标签，其值为长度不超过64个字符的非空字符串。 | - | #### 示例：创建复合类型表示地址信息 1. 创建一个复合类型用于表示地址信息，包括所在省、城市、街道、门牌号。 ``` DROP TYPE IF EXISTS address_type; CREATE TYPE address_type AS ( province VARCHAR(20), city VARCHAR(50), street VARCHAR(100), number VARCHAR(10) ); ``` 2. 使用该类型创建表。 ``` DROP TABLE IF EXISTS customers; CREATE TABLE customers ( customer_id SERIAL PRIMARY KEY, name VARCHAR(100), billing_address address_type, shipping_address address_type ); ``` 3. 插入数据。 ``` INSERT INTO customers (name, billing_address, shipping_address) VALUES ( 'John Doe', ('SDong', 'QD', '123 Main St', '10001')::address_type, ('SDong', 'QD', '456 Park Ave', '10022')::address_type ); ``` 4. 查询数据。 ``` SELECT name, (billing_address).street as bill_street, (shipping_address).city as ship_city FROM customers; ``` ![](https://support.huaweicloud.com/sqlreference-911-dws/figure/zh-cn_image_0000002515751986.png "点击放大") 5. 更新数据。 ``` UPDATE customers SET billing_address = ('SDong', 'QD','789 Broadway', '10003')::address_type WHERE customer_id = 1; ``` #### 示例：创建枚举类型用于电商订单管理 1. 创建枚举类型，里面的枚举值分别对应"待支付"、"已支付"、"已发货"、"已送达"、"已取消"。 ``` DROP TYPE IF EXISTS order_status; CREATE TYPE order_status AS ENUM ( 'pending', 'paid', 'shipped', 'delivered', 'cancelled' ); ``` 2. 创建订单表，状态列status使用枚举类型order_status。 ``` DROP TABLE IF EXISTS orders; CREATE TABLE orders ( order_id varchar(32) PRIMARY KEY, user_id int, goods_id int, amount decimal(10,2), status order_status DEFAULT 'pending', -- 默认待支付 create_time timestamp DEFAULT now(), update_time timestamp DEFAULT now() ); ``` 3. 插入测试数据。 ``` INSERT INTO orders (order_id, user_id, goods_id, amount) VALUES ('ORD20260127001', 1001, 5001, 299.99); -- 状态默认pending ``` 4. 更新订单状态，仅允许更新为限定的枚举值。 ``` UPDATE orders SET status = 'paid', update_time = now() WHERE order_id = 'ORD20260127001'; ``` 5. 查询数据。 ``` SELECT * FROM orders WHERE order_id = 'ORD20260127001'; ``` ![](https://support.huaweicloud.com/sqlreference-911-dws/figure/zh-cn_image_0000002516045246.png "点击放大") 6. 更新为非枚举值中的其他值时，系统会报错，以此来保证数据一致性。 ``` UPDATE orders SET status = 'wait_pay' WHERE order_id = 'ORD20260127001'; ``` ![](https://support.huaweicloud.com/sqlreference-911-dws/figure/zh-cn_image_0000002547734785.png "点击放大") #### 示例：创建基本类型，用于C语言函数调用 1. 在创建基本类型前，先创建shell类型（占位符），用于后续定义I/O函数时引用该类型。 ``` DROP TYPE IF EXISTS complex; CREATE TYPE complex; ``` 这个语句的作用是为要定义的类型创建了一个占位符，这样允许在定义其I/O函数时引用该类型。 2. 定义I/O函数，这些函数都引用到前面的shell类型complex，需要注意的是在创建函数时function必须声明为NOT FENCED模式，即约束该函数的执行在CN或者DN进程中进行，减少fork进程以及通信的开销。了解更多请参见[CREATE FUNCTION](https://support.huaweicloud.com/sqlreference-911-dws/dws_06_0163.html)。 ``` --创建文本输入函数 CREATE OR REPLACE FUNCTION complex_in(cstring) RETURNS complex AS 'filename' --替换为实际的.so文件路径，例如/opt/dws/complex.so LANGUAGE C IMMUTABLE STRICT not fenced; --创建文本输出函数 CREATE FUNCTION complex_out(complex) RETURNS cstring AS 'filename' --同上，替换为实际的.so文件路径。 LANGUAGE C IMMUTABLE STRICT not fenced; --创建二进制输入函数 CREATE FUNCTION complex_recv(internal) RETURNS complex AS 'filename' --同上，替换为实际的.so文件路径。 LANGUAGE C IMMUTABLE STRICT not fenced; --创建二进制输出函数 CREATE FUNCTION complex_send(complex) RETURNS bytea AS 'filename' --同上，替换为实际的.so文件路径。 LANGUAGE C IMMUTABLE STRICT not fenced; ``` 3. 创建定义完整的基本类型，替换之前的shell类型。 ``` CREATE TYPE complex ( internallength = 16, -- 定长16字节（2个double各8字节） input = complex_in, -- 绑定文本输入函数 output = complex_out, -- 绑定文本输出函数 receive = complex_recv, -- 绑定二进制输入函数 send = complex_send, -- 绑定二进制输出函数 alignment = double -- 指定8字节的存储对齐需求，匹配double类型存储 ); ``` 4. 下面是input、output、receive及send函数对应的C语言函数代码，供进一步开发参考。 ``` --定义结构体Complex如下： typedef struct Complex { double x; double y; } Complex; --定义input函数： PG_FUNCTION_INFO_V1(complex_in); Datum complex_in(PG_FUNCTION_ARGS) { char *str = PG_GETARG_CSTRING(0); double x, y; Complex *result; if (sscanf(str, " ( %lf , %lf )", &x, &y) != 2) ereport(ERROR, (errcode(ERRCODE_INVALID_TEXT_REPRESENTATION), errmsg("invalid input syntax for complex: \"%s\"", str))); result = (Complex *) palloc(sizeof(Complex)); result->x = x; result->y = y; PG_RETURN_POINTER(result); } --定义output函数： PG_FUNCTION_INFO_V1(complex_out); Datum complex_out(PG_FUNCTION_ARGS) { Complex *complex = (Complex *) PG_GETARG_POINTER(0); char *result; result = (char *) palloc(100); snprintf(result, 100, "(%g,%g)", complex->x, complex->y); PG_RETURN_CSTRING(result); } --定义receive函数： PG_FUNCTION_INFO_V1(complex_recv); Datum complex_recv(PG_FUNCTION_ARGS) { StringInfo buf = (StringInfo) PG_GETARG_POINTER(0); Complex *result; result = (Complex *) palloc(sizeof(Complex)); result->x = pq_getmsgfloat8(buf); result->y = pq_getmsgfloat8(buf); PG_RETURN_POINTER(result); } --定义send函数： PG_FUNCTION_INFO_V1(complex_send); Datum complex_send(PG_FUNCTION_ARGS) { Complex *complex = (Complex *) PG_GETARG_POINTER(0); StringInfoData buf; pq_begintypsend(&buf); pq_sendfloat8(&buf, complex->x); pq_sendfloat8(&buf, complex->y); PG_RETURN_BYTEA_P(pq_endtypsend(&buf)); } ``` #### 相关链接 [ALTER TYPE](https://support.huaweicloud.com/sqlreference-911-dws/dws_06_0148.html#ZH-CN_TOPIC_0000002255113337)，[DROP TYPE](https://support.huaweicloud.com/sqlreference-911-dws/dws_06_0213.html)