PostgreSQL 教程

Appearance

CREATE TYPE ​

CREATE TYPE — 定义新的数据类型

语法 ​

CREATE TYPE name AS
    ( [ attribute_name data_type [ COLLATE collation ] [, ... ] ] )

CREATE TYPE name AS ENUM
    ( [ 'label' [, ... ] ] )

CREATE TYPE name AS RANGE (
    SUBTYPE = subtype
    [ , SUBTYPE_OPCLASS = subtype_operator_class ]
    [ , COLLATION = collation ]
    [ , CANONICAL = canonical_function ]
    [ , SUBTYPE_DIFF = subtype_diff_function ]
    [ , MULTIRANGE_TYPE_NAME = multirange_type_name ]
)

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 ]
    [ , SUBSCRIPT = subscript_function ]
    [ , INTERNALLENGTH = { internallength | VARIABLE } ]
    [ , PASSEDBYVALUE ]
    [ , ALIGNMENT = alignment ]
    [ , STORAGE = storage ]
    [ , LIKE = like_type ]
    [ , CATEGORY = category ]
    [ , PREFERRED = preferred ]
    [ , DEFAULT = default ]
    [ , ELEMENT = element ]
    [ , DELIMITER = delimiter ]
    [ , COLLATABLE = collatable ]
)

CREATE TYPE name

描述 ​

CREATE TYPE为当前数据库注册一个新的数据类型。定义类型的用户将成为该类型的所有者。

如果给出了架构名称，则该类型将在指定的架构中创建。否则，它将在当前架构中创建。类型名称必须与同一架构中任何现有类型或域的名称不同。（由于表具有关联的数据类型，因此类型名称还必须与同一架构中任何现有表的名称不同。）

有五种形式的CREATE TYPE，如上文语法概要中所示。它们分别创建一个复合类型、一个枚举类型、一个范围类型、一个基本类型或一个外壳类型。前四种类型将在下面依次讨论。外壳类型只是一个占位符，用于稍后定义的类型；它通过发出CREATE TYPE（除了类型名称之外没有其他参数）来创建。在创建范围类型和基本类型时需要外壳类型作为前向引用，如这些部分中所述。

复合类型 ​

CREATE TYPE的第一种形式创建一个复合类型。复合类型由属性名称和数据类型列表指定。如果属性的数据类型可排序，还可以指定属性的排序规则。复合类型本质上与表的行类型相同，但使用CREATE TYPE无需创建实际表，而只需定义类型即可。独立的复合类型很有用，例如，作为函数的参数或返回类型。

要能够创建复合类型，您必须对所有属性类型拥有USAGE权限。

枚举类型 ​

CREATE TYPE的第二种形式创建一个枚举 (enum) 类型，如第 8.7 节中所述。枚举类型采用带引号的标签列表，每个标签的长度必须小于NAMEDATALEN字节（在标准PostgreSQL构建中为 64 字节）。（可以创建一个没有标签的枚举类型，但此类类型在使用ALTER TYPE添加至少一个标签之前无法用来保存值。）

范围类型 ​

CREATE TYPE的第三种形式创建一个新的范围类型，如第 8.17 节中所述。

范围类型的*subtype可以是任何具有关联的 b 树操作符类的类型（用于确定范围类型值的顺序）。通常使用子类型的默认 b 树操作符类来确定顺序；要使用非默认操作符类，请使用subtype_opclass指定其名称。如果子类型可排序，并且您希望在范围的顺序中使用非默认排序规则，请使用collation*选项指定所需的排序规则。

可选的*canonical函数必须采用一个正在定义的范围类型的参数，并返回相同类型的数值。这用于在适用时将范围值转换为规范形式。有关详细信息，请参见第 8.17.8 节。创建canonical*函数有点棘手，因为它必须在声明范围类型之前定义。为此，您必须首先创建一个外壳类型，这是一个占位符类型，除了名称和所有者之外没有其他属性。这可以通过发出命令CREATE TYPE*name*来完成，无需其他参数。然后可以使用外壳类型作为参数和结果来声明函数，最后可以使用相同的名称来声明范围类型。这将自动用有效的范围类型替换外壳类型条目。

可选的*subtype_diff函数必须采用subtype*类型的两个值作为参数，并返回一个double precision值，表示给定两个值之间的差值。虽然这是可选的，但提供它可以极大地提高范围类型列上的 GiST 索引的效率。有关详细信息，请参见第 8.17.8 节。

可选的*multirange_type_name*参数指定相应的多范围类型的名称。如果未指定，则此名称将自动选择如下。如果范围类型名称包含子字符串range，则通过在范围类型名称中用multirange替换range子字符串来形成多范围类型名称。否则，通过向范围类型名称追加_multirange后缀来形成多范围类型名称。

基本类型 ​

第四种形式的CREATE TYPE创建一个新的基本类型（标量类型）。要创建新的基本类型，您必须是超级用户。（做出此限制是因为错误的类型定义可能会混淆甚至使服务器崩溃。）

这些参数可以按任意顺序出现，而不仅仅是上面说明的顺序，并且大多数参数都是可选的。在定义类型之前，您必须注册两个或更多个函数（使用CREATE FUNCTION）。支持函数*input_function和output_function是必需的，而函数receive_function、send_function、type_modifier_input_function、type_modifier_output_function、analyze_function和subscript_function*是可选的。通常，这些函数必须用 C 或其他低级语言编写。

*input_function将类型的外部文本表示形式转换为由为该类型定义的操作符和函数使用的内部表示形式。output_function*执行相反的转换。可以将输入函数声明为采用一个cstring类型的参数，或采用三个cstring、oid、integer类型的参数。第一个参数是作为 C 字符串的输入文本，第二个参数是类型自己的 OID（数组类型除外，数组类型接收其元素类型的 OID），第三个参数（如果已知）是目标列的typmod（如果未知，将传递 -1）。输入函数必须返回数据类型本身的值。通常，应将输入函数声明为 STRICT；如果不是，则在读取 NULL 输入值时将使用第一个参数 NULL 调用该函数。在这种情况下，该函数仍必须返回 NULL，除非引发错误。（此情况主要用于支持域输入函数，这些函数可能需要拒绝 NULL 输入。）输出函数必须声明为采用新数据类型的一个参数。输出函数必须返回cstring类型。不会为 NULL 值调用输出函数。

可选的*receive_function将类型的外部二进制表示形式转换为内部表示形式。如果未提供此函数，则该类型不能参与二进制输入。应选择二进制表示形式，以便可以廉价地转换为内部形式，同时具有合理的移植性。（例如，标准整数数据类型使用网络字节顺序作为外部二进制表示形式，而内部表示形式采用机器的本机字节顺序。）接收函数应执行充分的检查以确保该值有效。可以将接收函数声明为采用一个internal类型的参数，或采用三个internal、oid、integer类型的参数。第一个参数是指向包含已接收字节字符串的StringInfo缓冲区的指针；可选参数与文本输入函数相同。接收函数必须返回数据类型本身的值。通常，应将接收函数声明为 STRICT；如果不是，则在读取 NULL 输入值时将使用第一个参数 NULL 调用该函数。在这种情况下，该函数仍必须返回 NULL，除非引发错误。（此情况主要用于支持域接收函数，这些函数可能需要拒绝 NULL 输入。）类似地，可选的send_function*从内部表示形式转换为外部二进制表示形式。如果未提供此函数，则该类型不能参与二进制输出。发送函数必须声明为采用新数据类型的一个参数。发送函数必须返回bytea类型。不会为 NULL 值调用发送函数。

此时你应该会疑惑，当必须在创建新类型之前创建输入和输出函数时，如何才能声明这些函数具有新类型的结果或参数。答案是，首先应将类型定义为外壳类型，这是一种占位符类型，除了名称和所有者之外没有任何属性。通过发出命令CREATE TYPE*name*来完成此操作，无需其他参数。然后，可以定义 C I/O 函数来引用外壳类型。最后，带有完整定义的CREATE TYPE将外壳条目替换为完整、有效的类型定义，之后可以正常使用新类型。

如果类型支持修饰符（附加到类型声明中的可选约束，例如char(5)或numeric(30,2)），则需要可选的*type_modifier_input_function和type_modifier_output_function。PostgreSQL允许用户定义类型将一个或多个简单常量或标识符作为修饰符。但是，此信息必须能够打包到单个非负整数值中，以存储在系统目录中。以cstring数组的形式将声明的修饰符传递给type_modifier_input_function。它必须检查值的有效性（如果值错误则抛出错误），如果正确，则返回一个非负integer值，该值将作为列“typmod”存储。如果类型没有type_modifier_input_function，则将拒绝类型修饰符。type_modifier_output_function将内部整数 typmod 值转换回正确的形式以供用户显示。它必须返回一个cstring值，该值是要附加到类型名称的确切字符串；例如，numeric的函数可能返回(30,2)。允许省略type_modifier_output_function*，在这种情况下，默认显示格式只是用括号括起来的已存储的 typmod 整数值。

可选的*analyze_function*为数据类型的列执行特定类型的统计信息收集。默认情况下，ANALYZE将尝试使用该类型的“equals”和“less-than”运算符收集统计信息（如果该类型有默认的 b 树运算符类）。对于非标量类型，此行为可能不合适，因此可以通过指定自定义分析函数来覆盖它。分析函数必须声明为接受一个internal类型的参数，并返回一个boolean结果。分析函数的详细 API 载于src/include/commands/vacuum.h。

可选的*subscript_function允许在 SQL 命令中对数据类型进行下标引用。指定此函数不会导致该类型被视为“true”数组类型；例如，它不会成为ARRAY[]构造的结果类型的候选者。但是，如果对该类型的值进行下标引用是从中提取数据的自然表示法，那么可以编写subscript_function*来定义其含义。下标引用函数必须声明为接受一个internal类型的参数，并返回一个internal结果，该结果是一个实现下标引用的方法（函数）的结构体的指针。下标引用函数的详细 API 载于src/include/nodes/subscripting.h。阅读src/backend/utils/adt/arraysubs.c中的数组实现或contrib/hstore/hstore_subs.c中的更简单的代码也可能很有用。更多信息载于下文的数组类型。

虽然新类型的内部表示的详细信息只为 I/O 函数和其他用于处理该类型的函数所知，但必须向PostgreSQL声明内部表示的几个属性。其中最重要的是*internallength。基本数据类型可以是固定长度的，在这种情况下，internallength是一个正整数，或者可变长度的，通过将internallength*设置为VARIABLE来表示。（在内部，这是通过将typlen设置为 -1 来表示的。）所有可变长度类型的内部表示都必须以一个 4 字节整数开头，该整数给出该类型值的总长度。（请注意，长度字段通常是编码的，如第 73.2 节中所述；直接访问它是不明智的。）

可选标志PASSEDBYVALUE表示该数据类型的值按值传递，而不是按引用传递。按值传递的类型必须是固定长度的，并且它们的内部表示不能大于Datum类型的长度（在某些机器上为 4 字节，在其他机器上为 8 字节）。

*alignment*参数指定数据类型所需的存储对齐方式。允许的值等同于在 1、2、4 或 8 字节边界上的对齐方式。请注意，可变长度类型必须至少有 4 的对齐方式，因为它们必然包含一个int4作为其第一个组件。

*storage*参数允许为可变长度数据类型选择存储策略。（对于固定长度类型，只允许plain。）plain指定该类型的数据将始终以行内方式存储，并且不会压缩。extended指定系统将首先尝试压缩长数据值，如果它仍然太长，则会将该值移出主表行。external允许将该值移出主表，但系统不会尝试压缩它。main允许压缩，但会阻止将该值移出主表。（具有此存储策略的数据项在没有其他方法使行适合的情况下仍可能被移出主表，但它们将优先于extended和external项保留在主表中。）

除了plain之外的所有其他*storage值都意味着数据类型的函数可以处理已烤制*的值，如第 73.2 节和第 38.13.1 节中所述。给出的特定其他值仅仅确定可烤制数据类型列的默认 TOAST 存储策略；用户可以使用ALTER TABLE SET STORAGE为各个列选择其他策略。

like_type参数提供了一种指定数据类型基本表示属性的替代方法：从某些现有类型中复制它们。internallength、passedbyvalue、*alignment和storage*的值从命名类型中复制。（虽然通常不希望这样做，但可以通过使用LIKE子句指定这些值来覆盖其中一些值。）以这种方式指定表示在以下情况下特别有用：新类型“搭载”的底层实现以某种方式基于现有类型。

category和preferred参数可用于帮助控制在模棱两可的情况下应用哪种隐式转换。每个数据类型都属于由单个 ASCII 字符命名的类别，并且每个类型在其类别中要么是“preferred”，要么不是。当此规则有助于解决重载函数或运算符时，解析器将优先转换为首选类型（但仅从同一类别中的其他类型）。有关更多详细信息，请参阅第 10 章。对于没有到或来自任何其他类型的隐式转换的类型，将这些设置保留为默认值就足够了。但是，对于具有一组隐式转换的相关类型，通常将它们全部标记为属于一个类别，并将一个或两个“最通用”类型标记为该类别中的首选类型。当将用户定义类型添加到现有的内置类别（例如数字或字符串类型）时，category参数特别有用。但是，还可以创建全新的用户定义类型类别。选择大写字母以外的任何 ASCII 字符来命名此类类别。

如果用户希望数据类型的列默认为非空值，则可以指定默认值。使用DEFAULT关键字指定默认值。（此类默认值可以被附加到特定列的显式DEFAULT子句覆盖。）

要指示类型为固定长度数组类型，请使用ELEMENT关键字指定数组元素的类型。例如，要定义 4 字节整数数组（int4），请指定ELEMENT = int4。有关更多详细信息，请参阅下面的数组类型。

要指示在此类型的数组的外部表示中值之间使用的分隔符，可以将delimiter设置为特定字符。默认分隔符是逗号（,）。请注意，分隔符与数组元素类型相关，而不是与数组类型本身相关。

如果可选布尔参数collatable为真，则该类型的列定义和表达式可以通过使用COLLATE子句携带校对信息。由操作该类型的函数的实现来实际使用校对信息；仅仅通过将类型标记为可校对并不会自动发生这种情况。

数组类型 ​

每当创建用户定义类型时，PostgreSQL都会自动创建一个关联的数组类型，其名称由元素类型的名称加上下划线组成，并在必要时截断以使其长度小于NAMEDATALEN字节。（如果这样生成的名称与现有类型名称冲突，则重复此过程，直到找到非冲突的名称。）此隐式创建的数组类型是可变长度的，并使用内置的输入和输出函数array_in和array_out。此外，此类型是系统用于用户定义类型上的ARRAY[]等构造的类型。数组类型会跟踪其元素类型的拥有者或架构中的任何更改，如果元素类型被删除，则数组类型也会被删除。

系统自动生成正确的数组类型，因此你可能会合理地询问为什么会有ELEMENT选项。使用ELEMENT最有用的主要情况是，当你要制作一个定长类型时，该类型恰好是内部包含多个相同事物的数组，并且除了你计划为整个类型提供的任何操作之外，你还希望允许通过下标直接访问这些事物。例如，类型point仅表示为两个浮点数，可以使用point[0]和point[1]访问它们。请注意，此功能仅适用于内部形式完全是相同定长字段序列的定长类型。出于历史原因（即，这显然是错误的，但现在更改为时已晚），定长数组类型的下标从零开始，而不是像可变长数组那样从一开始。

指定SUBSCRIPT选项允许对数据类型进行下标，即使系统不将其视为数组类型。针对定长数组刚刚描述的行为实际上是由SUBSCRIPT处理程序函数raw_array_subscript_handler实现的，如果你为定长类型指定ELEMENT而没有同时编写SUBSCRIPT，则会自动使用该函数。

在指定自定义SUBSCRIPT函数时，无需指定ELEMENT，除非SUBSCRIPT处理程序函数需要查阅typelem以找出要返回的内容。请注意，指定ELEMENT会导致系统假设新类型包含元素类型或在某种程度上物理依赖于元素类型；因此，例如，如果存在任何从属类型的列，则不允许更改元素类型的属性。

参数 ​

name

要创建的类型的名称（可选架构限定）。

attribute_name

复合类型的属性（列）的名称。

data_type

现有数据类型的名称，该类型将成为复合类型的列。

collation

现有校对的名称，该校对将与复合类型的列或范围类型相关联。

label

表示与枚举类型的一个值关联的文本标签的字符串文字。

subtype

范围类型将表示其范围的元素类型的名称。

subtype_operator_class

子类型的 b 树运算符类的名称。

canonical_function

范围类型的规范化函数的名称。

subtype_diff_function

子类型的差分函数的名称。

multirange_type_name

相应多范围类型的名称。

input_function

将数据从类型的外部文本形式转换为内部形式的函数的名称。

output_function

将数据从类型的内部形式转换为外部文本形式的函数的名称。

receive_function

将数据从类型的外部二进制形式转换为内部形式的函数的名称。

send_function

将数据从类型的内部形式转换为外部二进制形式的函数的名称。

type_modifier_input_function

将类型的修饰符数组转换为内部形式的函数的名称。

type_modifier_output_function

将类型的修饰符的内部形式转换为外部文本形式的函数的名称。

analyze_function

对数据类型执行统计分析的函数的名称。

subscript_function

定义对数据类型值进行下标操作的函数的名称。

internallength

指定新类型的内部表示长度（以字节为单位）的数字常量。默认假设其为可变长度。

alignment

数据类型的存储对齐要求。如果指定，则必须为 char、int2、int4 或 double；默认值为 int4。

storage

数据类型的存储策略。如果指定，则必须为 plain、external、extended 或 main；默认值为 plain。

like_type

新类型将具有与其相同的表示形式的现有数据类型的名称。internallength、passedbyvalue、alignment 和 storage 的值将从该类型复制，除非在此 CREATE TYPE 命令中的其他位置通过显式指定进行覆盖。

category

此类型的类别代码（单个 ASCII 字符）。默认值为 'U'，表示 “用户定义类型”。可以在 表 53.65 中找到其他标准类别代码。您还可以选择其他 ASCII 字符来创建自定义类别。

首选

如果此类型在其类型类别内为首选类型，则为 True，否则为 false。默认值为 false。在现有类型类别中创建新的首选类型时务必小心，因为这可能会导致行为发生意外更改。

默认

数据类型的默认值。如果省略，则默认值为 null。

元素

正在创建的类型为数组；这指定数组元素的类型。

分隔符

在此类型组成的数组中的值之间使用的分隔符字符。

可整理

如果此类型的操作可以使用整理信息，则为 True。默认值为 false。

注释 ​

由于在创建数据类型后对其使用没有任何限制，因此创建基本类型或范围类型等同于授予类型定义中提到的函数的公共执行权限。对于类型定义中可用的函数类型来说，这通常不是问题。但是，在设计类型时，您可能需要三思而后行，因为这需要在将其转换为外部形式或从外部形式转换时使用““秘密””信息。

在PostgreSQL8.3 版之前，生成数组类型的名称始终与元素类型的名称完全相同，前面加上一个下划线字符 (_)。（因此，类型名称的长度比其他名称少一个字符。）虽然通常仍然如此，但数组类型名称在最大长度名称或与以下划线开头的用户类型名称冲突的情况下可能与此不同。因此，编写依赖于此约定的代码已弃用。相反，使用pg_type.typarray来查找与给定类型关联的数组类型。

建议避免使用以下划线开头的类型和表名称。虽然服务器会更改生成的数组类型名称以避免与用户给定的名称冲突，但仍然存在混淆的风险，特别是对于可能假设以下划线开头的类型名称始终表示数组的旧客户端软件。

在PostgreSQL8.2 版本之前，不存在 shell 类型创建语法CREATE TYPE*name*。创建新基本类型的方法是首先创建其输入函数。在这种方法中，PostgreSQL将首先看到新数据类型的名称作为输入函数的返回类型。在这种情况下，将隐式创建 shell 类型，然后可以在其余 I/O 函数的定义中引用它。此方法仍然有效，但已弃用，并且在某些未来版本中可能会被禁止。此外，为了避免由于函数定义中的简单拼写错误而意外地用 shell 类型弄乱目录，只有当输入函数是用 C 编写的时，才会以这种方式创建 shell 类型。

在PostgreSQL16 及更高版本中，期望基本类型的输入函数使用新的errsave()/ereturn()机制返回“soft”错误，而不是像以前版本那样抛出ereport()异常。有关更多信息，请参见src/backend/utils/fmgr/README。

示例 ​

此示例创建一个复合类型并在函数定义中使用它

CREATE TYPE compfoo AS (f1 int, f2 text);

CREATE FUNCTION getfoo() RETURNS SETOF compfoo AS $$
    SELECT fooid, fooname FROM foo
$$ LANGUAGE SQL;

此示例创建一个枚举类型并在表定义中使用它

CREATE TYPE bug_status AS ENUM ('new', 'open', 'closed');

CREATE TABLE bug (
    id serial,
    description text,
    status bug_status
);

此示例创建一个范围类型

CREATE TYPE float8_range AS RANGE (subtype = float8, subtype_diff = float8mi);

此示例创建基本数据类型box，然后在表定义中使用该类型

CREATE TYPE box;

CREATE FUNCTION my_box_in_function(cstring) RETURNS box AS ... ;
CREATE FUNCTION my_box_out_function(box) RETURNS cstring AS ... ;

CREATE TYPE box (
    INTERNALLENGTH = 16,
    INPUT = my_box_in_function,
    OUTPUT = my_box_out_function
);

CREATE TABLE myboxes (
    id integer,
    description box
);

如果box的内部结构是一个包含四个float4元素的数组，我们可能会使用

CREATE TYPE box (
    INTERNALLENGTH = 16,
    INPUT = my_box_in_function,
    OUTPUT = my_box_out_function,
    ELEMENT = float4
);

这将允许通过下标访问 box 值的组件号。否则，该类型的行为与之前相同。

此示例创建一个大对象类型并在表定义中使用它

CREATE TYPE bigobj (
    INPUT = lo_filein, OUTPUT = lo_fileout,
    INTERNALLENGTH = VARIABLE
);
CREATE TABLE big_objs (
    id integer,
    obj bigobj
);

更多示例（包括合适的输入和输出函数）请参见第 38.13 节。

兼容性 ​

创建复合类型的CREATE TYPE命令的第一种形式符合SQL标准。其他形式是PostgreSQL扩展。SQL标准中的CREATE TYPE语句还定义了PostgreSQL中未实现的其他形式。

创建具有零个属性的复合类型的能力是PostgreSQL对标准的特定偏差（类似于CREATE TABLE中的相同情况）。

另请参见 ​

ALTER TYPE、CREATE DOMAIN、CREATE FUNCTION、DROP TYPE