CREATE TYPE

定义新的数据类型。

概要

  1. CREATE TYPE name AS
  2. ( attribute_name data_type [ COLLATE collation ] [, ... ] ] )
  4. CREATE TYPE name AS ENUM 
  5.     ( [ 'label' [, ... ] ] )
  7. CREATE TYPE name AS RANGE (
  8.     SUBTYPE = subtype
  9.     [ , SUBTYPE_OPCLASS = subtype_operator_class ]
  10.     [ , COLLATION = collation ]
  11.     [ , CANONICAL = canonical_function ]
  12.     [ , SUBTYPE_DIFF = subtype_diff_function ]
  13. )
  15. CREATE TYPE name (
  16.     INPUT = input_function,
  17.     OUTPUT = output_function
  18.     [, RECEIVE = receive_function]
  19.     [, SEND = send_function]
  20.     [, TYPMOD_IN = type_modifier_input_function ]
  21.     [, TYPMOD_OUT = type_modifier_output_function ]
  22.     [, INTERNALLENGTH = {internallength | VARIABLE}]
  23.     [, PASSEDBYVALUE]
  24.     [, ALIGNMENT = alignment]
  25.     [, STORAGE = storage]
  26.     [, LIKE = like_type
  27.     [, CATEGORY = category]
  28.     [, PREFERRED = preferred]
  29.     [, DEFAULT = default]
  30.     [, ELEMENT = element]
  31.     [, DELIMITER = delimiter]
  32.     [, COLLATABLE = collatable]
  33.     [, COMPRESSTYPE = compression_type]
  34.     [, COMPRESSLEVEL = compression_level]
  35.     [, BLOCKSIZE = blocksize] )
  37. CREATE TYPE name

描述

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

如果提供了模式名称，则在指定的模式中创建类型。 否则，它将在当前模式中创建。 类型名称必须不同于同一模式中任何现有类型或域的名称。 类型名称还必须与同一模式中任何现有表的名称都不同。

如上面的语法概要所示，有五种形式的CREATE TYPE。 它们分别创建组合类型，枚举类型，范围类型，基础类型或shell类型。 下面依次讨论其中的前四个。 shell类型只是用于稍后定义的类型的占位符； 它是通过发出CREATE TYPE来创建的，除了类型名称外，不带任何参数。 如这些部分所述，在创建范围类型和基础类型时，需要使用Shell类型作为前向引用。

组合类型

第一种形式的CREATE TYPE创建组合类型。 组合类型由一个属性名和数据类型的列表指定。 如果属性的数据类型是可排序的，也可以指定该属性的排序规则。 组合类型本质上和表的行类型相同，但是如果只想定义一种类型，使用CREATE TYPE避免了创建一个实际的表。 单独的组合类型也是很有用的，例如可以作为函数的参数或者返回类型。

为了能够创建组合类型，必须拥有在其所有属性类型上的USAGE特权。

枚举类型

正如PostgreSQL文档中描述的枚举类型， 第二种形式的CREATE TYPE创建枚举类型。 枚举类型需要由一个或者更多带引号的标签构成的列表， 每一个标签长度必须不超过NAMEDATALEN字节（在标准的 PostgreSQL编译中是 64 字节）。

范围类型

正如范围类型描述， 第三种形式的CREATE TYPE创建范围类型。

范围类型的subtype可以是任何带有一个相关的B树操作符类（用来决定该范围类型值的顺序）的类型。 通常，子类型的默认B树操作符类被用来决定顺序。 要使用一种非默认操作符类，可以用subtype_opclass指定它的名字。 如果子类型是可排序的并且希望在该范围的顺序中使用一种非默认的排序规则，可以用collation选项来指定。

可选的canonical函数必须接受一个所定义的范围类型的参数，并且返回同样类型的一个值。 在适用时，它被用来把范围值转换成一种规范的形式。 更多信息请见定义新的范围类型。 创建一个canonical函数有点棘手，因为必须在声明范围类型之前定义它。 要这样做，必须首先创建一种shell类型，它是一种没有属性只有名称和拥有者的占位符类型。 这可以通过 发出不带额外参数的命令CREATE TYPE name来完成。 然后可以使用该shell类型作为参数和结果来声明该函数，并且最终用同样的名称来声明范围类型。 这会自动用一种合法的范围类型替换shell类型项。

可选的subtype_diff函数必须接受两个subtype类型的值作为参数， 并且返回一个double precision值表示两个给定值之间的差别。 虽然这是可选的，但是提供这个函数会让该范围类型列上GiST索引效率更高。 更多信息请参考定义新的范围类型。

Base Types

第四种形式的CREATE TYPE创建一种新的基础类型（标量类型）。 为了创建一种新的基础类型，你必须是一个超级用户。 参数可以以任意顺序出现（而不仅是按照语法所示的顺序），并且大部分是可选的。 在定义类型前，必须注册两个或者更多函数（使用CREATE FUNCTION）。 支持函数input_function以及output_function是必需的， 而函数receive_function、send_function、type_modifier_input_function、 type_modifier_output_function和analyze_function是可选的。 通常来说这些函数必须是用C或者另外一种低层语言编写的。 在Greenplum数据库中，用于实现数据类型的任何函数都必须定义为IMMUTABLE。

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值不会调用发送函数。

如果类型支持修饰符， 则需要可选的type_modifier_input_function和type_modifier_output_function。 修饰符是附加到类型声明的可选约束，例如char(5)或numeric(30,2)。 尽管Greenplum数据库允许用户定义的类型将一个或多个简单的常量或标识符用作修饰符， 但此信息必须适合单个非负整数值，以存储在系统catalog中。 Greenplum数据库以cstring数组的形式将声明的修饰符传递给type_modifier_input_function。 修饰符输入函数必须检查值的有效性，如果值不正确，则会引发错误。 如果值正确，则修饰符输入函数将返回单个非负整数值，该值将被Greenplum数据库存储为typmod列。 如果未使用type_modifier_input_function定义类型，则拒绝类型修饰符。 type_modifier_output_function将内部整数typmod值转换回正确的格式以供用户显示。 修饰符输出函数必须返回一个cstring值，该值是要附加到类型名称后的确切字符串。 例如，数字函数可能返回(30,2)。 type_modifier_output_function是可选的。 如果未指定，则默认显示格式为括号内存储的typmod整数值。

到这里你应该在疑惑输入和输出函数是如何能被声明为具有新类型的结果或参数的。 因为必须在创建新类型之前创建这两个函数。 这个问题的答案是，新类型应该首先被定义为一种shell类型，它是一种占位符类型，除了名称和拥有者之外它没有其他属性。 这可以通过不带额外参数的命令CREATE TYPE name做到。 然后I/O函数可以被定义为引用这种shell类型。 最后，用带有完整定义的CREATE TYPE把该shell类型替换为一个完全的、合法的类型定义，之后新类型就可以正常使用了。

like_type参数提供了一种用于指定数据类型的基本表示形式属性的替代方法：从某些现有类型中复制它们。 从指定类型复制值internallength，passedbyvalue，alignment和storage。 （尽管通常不希望这样做，但可以通过与LIKE子句一起指定它们来覆盖其中的某些值。） 以这种方式指定表示形式对于在现有类型中新类型“搭载”的低级实现在某种方式上非常有用。

虽然新类型的内部表示的详细信息仅由I/O函数和您创建的与该类型一起使用的其他函数才知道， 但是内部表示的一些属性必须声明给Greenplum数据库。 其中最重要的是internallength。 基础数据类型可以是固定长度，在这种情况下，internallength是正整数）， 也可以是可变长度，通过将internallength设置为VARIABLE表示）。 （内部，这是通过将typlen设置为-1来表示的。） 所有可变长度类型的内部表示必须以4字节整数开头，并给出该类型值的总长度。

可选的标志PASSEDBYVALUE表示这种数据类型的值需要被传值而不是传引用。 您不能传递内部表示形式大于Datum类型的大小的值类型 （大多数计算机上为4字节，少数计算机上为8字节）。

alignment参数指定数据类型的存储对齐要求。 允许的值等同于以1、2、4或8字节边界对齐。 注意变长类型的参数必须至少按4字节对齐，因为它们需要包含一个int4作为它们的第一个组成部分。

storage参数允许为变长数据类型选择存储策略（对定长类型只允许plain）。 plain指定该类型的数据将总是被存储在线内并且不会被压缩。 extended指定系统将首先尝试压缩一个长的数据值，并且将在数据仍然太长的情况下把值移出主表行。 external允许值被移出主表，但是系统将不会尝试对它进行压缩。 main允许压缩，但是不鼓励把值移出主表。 （如果没有其他办法让行的大小变得合适，具有这种存储策略的数据项仍将被移出主表， 但比起extended以及external项，这种存储策略的数据项会被优先考虑保留在主表中）。

如果用户希望数据类型的列默认为空值以外的其他值，则可以指定一个默认值。 使用DEFAULT关键字指定默认值。 （这种默认值可能会被附加到特定列的显式DEFAULT子句覆盖。）

要指示类型是数组，请使用ELEMENT关键字指定数组元素的类型。 例如，要定义一个4字节整数（int4）的数组，请指定ELEMENT = int4。 有关数组类型的更多详细信息显示在下面。

category和preferred参数可以被用来帮助控制在混淆的情况下应用哪一种隐式造型。 每种数据类型均属于以单个ASCII字符命名的类别，并且每种类型均为“首选”或不在其类别内。 当此规则有助于解析重载的函数或运算符时，解析器将更喜欢强制转换为首选类型（但只能从同一类别中的其他类型）。 对于没有隐式转换为其他类型或从任何其他类型隐式转换的类型，保留默认设置就足够了。 但是，对于具有隐式强制转换的一组相关类型， 将它们全部标记为属于一个类别并选择一个或两个“最一般”类型作为该类别中的首选类型通常是有帮助的。 当您将用户定义的类型（例如数字或字符串类型）添加到现有内置类别时，category参数特别有用。 也可以创建新的完全由用户定义的类型类别。 选择除大写字母以外的任何ASCII字符以命名此类。

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

如果可选的布尔参数collatable为true， 则类型的列定义和表达式可以通过使用COLLATE子句来携带排序规则信息。 在该类型上操作的函数的实现负责真正利用这些信息， 仅把类型标记为可排序的并不会让它们自动地去使用这类信息。

数组类型

每当创建用户定义的类型时，Greenplum数据库都会自动创建一个关联的数组类型， 其名称由元素类型的名称组成，该名称前面带有下划线， 并在必要时将其截断以使其长度小于NAMEDATALEN字节。 （如果这样生成的名称与现有类型名称冲突，则重复该过程，直到找到一个非冲突名称为止。） 此隐式创建的数组类型为可变长度，并使用内置的输入和输出函数array_in和array_out。 数组类型跟踪其元素类型的所有者或模式中的所有更改，并且在元素类型被删除时也被删除。

如果系统会自动地创建正确的数组类型，你可能会理所当然地问为什么会有一个ELEMENT选项。 使用ELEMENT唯一有用的情况是：当你在创建一种定长类型， 它正好在内部是一个多个相同东西的数组，并且除了计划给该类型提供的整体操作之外， 你想要允许用下标来直接访问这些东西。 例如，类型point被表示为两个浮点数， 可以使用point[0]以及point[1]来访问它们。 注意，这种功能只适用于内部形式正好是一个相同定长域序列的定长类型。 可用下标访问的变长类型必须具有array_in以及array_out使用的一般化的内部表达。 由于历史原因（即很明显是错的，但现在改已经太晚了），定长数组类型的下标是从零开始的，而不是像变长数组那样。

参数

name

要创建的类型的名称（可以由模式指定）。

attribute_name

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

data_type

要成为复合类型的列的现有数据类型的名称。

collation

与复合类型的列或范围类型关联的现有排序规则的名称。

label

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

subtype

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

subtype_operator_class

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

canonical_function

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

subtype_diff_function

子类型的差函数的名称。

input_function

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

output_function

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

receive_function

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

send_function

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

type_modifier_input_function

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

type_modifier_output_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’，表示用户定义的类型。 您可以在pg_type类别代码中找到其他标准类别代码。 您还可以将未使用的ASCII字符分配给您创建的自定义类别。

preferred

如果此类型是其类型类别中的首选类型，则为true，否则为false。 默认值为false。 在现有类型类别中创建新的首选类型时要小心；这可能会导致令人惊讶的行为更改。

default

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

element

创建的类型是一个数组；这指定了数组元素的类型。

delimiter

在此类型的数组中的值之间使用定界符。

collatable

如果此类型的操作可以使用排序规则信息，则为true。默认为false。

compression_type

设置为ZLIB（默认值），ZSTD， RLE_TYPE或QUICKLZ1以指定在此类型的列中使用的压缩类型。

Note: 1QuickLZ压缩仅在Pivotal Greenplum数据库的商业版本中可用。

compression_level

对于Zstd压缩，将其设置为1（最快压缩）到19（最高压缩率）之间的整数值。 对于zlib压缩，有效范围是1到9。 QuickLZ压缩级别只能设置为1。 对于RLE_TYPE，压缩级别可以设置为从1（最快压缩）到4（最高压缩率）的整数值。 默认压缩级别为1。

blocksize

设置为列中每个块的大小（以字节为单位）。 BLOCKSIZE必须介于8192和2097152字节之间，并且是8192的倍数。 默认块大小为32768。

注解

用户定义的类型名称不能以下划线字符（_）开头，并且只能为62个字符长 （或者通常为NAMEDATALEN - 2，而不是其他名称允许的NAMEDATALEN - 1个字符）。 以下划线开头的类型名称保留给内部创建的数组类型名称。

Greenplum数据库不支持为行或复合类型添加存储选项。

在表级和列级定义的存储选项将覆盖为标量类型定义的默认存储选项。

因为一旦创建数据类型就没有使用限制，所以创建基础类型或范围类型无异于授予对类型定义中提到的函数的公共执行权限。 （因此，类型的创建者必须拥有这些函数。） 对于在类型定义中有用的各种函数而言，这通常不是问题。 但是您在设计一种在将其转换为外部格式或从外部格式转换要求使用“秘密”信息的类型时，可能需要三思而后行。

示例

本示例创建一个复合类型并将其用于函数定义中：

  1. CREATE TYPE compfoo AS (f1 int, f2 text);
  3. CREATE FUNCTION getfoo() RETURNS SETOF compfoo AS $$
  4.     SELECT fooid, fooname FROM foo
  5. $$ LANGUAGE SQL;

本示例创建枚举类型mood，并在表定义中使用它。

  1. CREATE TYPE mood AS ENUM ('sad', 'ok', 'happy');
  2. CREATE TABLE person (
  3.     name text,
  4.     current_mood mood
  5. );
  6. INSERT INTO person VALUES ('Moe', 'happy');
  7. SELECT * FROM person WHERE current_mood = 'happy';
  8.  name | current_mood 
  9. ------+--------------
  10.  Moe  | happy
  11. (1 row)

本示例创建一个范围类型：

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

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

  1. CREATE TYPE box;
  3. CREATE FUNCTION my_box_in_function(cstring) RETURNS box AS 
  4. ... ;
  6. CREATE FUNCTION my_box_out_function(box) RETURNS cstring AS 
  7. ... ;
  9. CREATE TYPE box (
  10.     INTERNALLENGTH = 16,
  11.     INPUT = my_box_in_function,
  12.     OUTPUT = my_box_out_function
  13. );
  15. CREATE TABLE myboxes (
  16.     id integer,
  17.     description box
  18. );

如果box的内部结构是四个float4元素的数组，则可以改用：

  1. CREATE TYPE box (
  2.     INTERNALLENGTH = 16,
  3.     INPUT = my_box_in_function,
  4.     OUTPUT = my_box_out_function,
  5.     ELEMENT = float4
  6. );

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

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

  1. CREATE TYPE bigobj (
  2.     INPUT = lo_filein, OUTPUT = lo_fileout,
  3.     INTERNALLENGTH = VARIABLE
  4. );
  6. CREATE TABLE big_objs (
  7.     id integer,
  8.     obj bigobj
  9.     );

兼容性

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

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

另见

ALTER TYPE , CREATE DOMAIN , CREATE FUNCTION , DROP TYPE

Parent topic: SQL Command Reference