CREATE TYPE 中文man页面

NAME

CREATE TYPE - 定义一个新的数据类型

SYNOPSIS

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

CREATE TYPE name (
    INPUT = input_function,
    OUTPUT = output_function
    [ , RECEIVE = receive_function ]
    [ , SEND = send_function ]
    [ , INTERNALLENGTH = { internallength | VARIABLE } ]
    [ , PASSEDBYVALUE ]
    [ , ALIGNMENT = alignment ]
    [ , STORAGE = storage ]
    [ , DEFAULT = default ]
    [ , ELEMENT = element ]
    [ , DELIMITER = delimiter ]
)

DESCRIPTION 描述

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

 如果给出模式名，那么该类型是在指定模式中创建。 否则它是在当前模式中创建。类型名必需和同一模式中任何现有的类型或者域不同。 （因为表和数据类型有联系，类型名不能和同模式中的表名字冲突。）  

COMPOSITE TYPES 复合类型

 ***种形式的 CREATE TYPE 创建一个复合类型。 复合类型是通过一列属性名和数据类型声明的。这样实际上和一个表的行类型一样， 但是如果我们只是想定义一个类型，那么使用 CREATE TYPE 避免了直接创建实际的表。 一个独立的复合类型对于一个函数的返回类型非常有用。  

BASE TYPES 基本类型

 第二种形式的CREATE TYPE创建一种新的基本类型（标量类型）。 参数可以以任意的顺序出现，而不是上面显示的那样。并且大多数都是可选的。 它要求要在定义类型之前先注册两个函数（用CREATE FUNCTION命令）。 支持函数 input_function 和 output_function 是必须的， 而函数 receive_function 和 send_function 是可选的。 通常，这些函数必须用 C 或者其它低层语言编写。

 函数 input_function  将该类型的外部文本形式转换成可以被对该类型操作的操作符和函数识别的内部形式。 output_function  用做相反用途。 输入函数可以声明为接受一个类型为 c_string 的参数，或者接受三个类型分别为 c_string，oid，integer 的参数。 （***个参数是 C 字串形式的输入文本，第二个是在该类型为数组类型时其元素的类型， 第三个是目标字段的typmod，如果已知的话。） 它应该返回一个该数据类型本身的数值。 输出函数可以声明为接受一个类型为新数据类型的参数， 或者接受两个类型，第二个参数的类型是 oid。 第二个参数也是用于数组类型的数组元素类型。输出函数应该返回类型 cstring。

 可选的 receive_function 把该类型的外部二进制表现形式转换成内部表现形式。 如果没有提供这个函数，那么该类型不能用二进制输入。二进制格式应该选取那种转换成内部格式比较容易的，同时还有一定移植性的。 （比如，标准的整数数据类型使用网络字节序作为外部的二进制表现形式，而内部表现形式是机器的本机字节序。） 接收函数应该声明为接受一个类型为 internal 的参数，或者两个类型分别为 internal 和 oid 的参数。 它必须返回一个数据类型自身的数值。（***个参数是一个指向一个 StringInfo 缓冲区的，保存接受字节串的指针； 可选的第二个参数是元素类型——如果类型是一个数组类型的话。）类似的，可选的 send_function  把类型转换为外部二进制表现形式。 如果没有提供这些函数，那么类型就不能用二进制方式输出。发送函数可以声明为接收一个新数据类型， 或者接收两个参数，第二个参数的类型是 oid。第二个参数仍然是用做数组类型的。 发送函数必须返回 bytea。

 这个时候你应该觉得奇怪，就是输入和输出函数怎么可以声明为返回新类型的结果或者是接受新类型的参数， 而且是在新类型创建之前就需要创建它们。 答案是输入函数必须先创建，然后是输出函数，***是数据类型。 PostgreSQL 将首先把新数据类型的名字看作输入函数的返回类型。 它将创建一个"壳"类型，这个类型只是在 pg_type里面的一个占位符，然后把输入函数定义和这个壳类型连接起来。 类似的是输出函数将连接到（现在已经存在）的壳类型。***， CREATE TYPE 把这个壳类型替换成完整的类型定义，这样就可以使用新类型了。

 尽管新类型的内部表现形式只有 I/O 函数和其它你创建来使用该类型的函数了解， 但内部表现还是有几个属性必须为 PostgreSQL 声明。 这些中最重要的是 internallength。 基本数据类型可定义成为定长，这时 internallength  是一个正整数，也可以是变长的，通过把 internallength  设置为 VARIABLE 表示。（在内部，这个状态 是通过将typlen设置为 -1 实现的。）所有变长类型的内部形式都必须以一个四字节整数开头，这个整数给出此类型这个数值的全长。

 可选的标记 PASSEDBYVALUE 表明该类型的数值是用值传递的， 而不是用引用。你不能传递那些内部形式大于 Datum （大多数机器上是 4 字节，有些是 8 字节）类型的尺寸的数据类型的值。

alignment 参数声明该数据类型要求的对齐存储方式。 允许的数值等效于按照 1，2，4，或者 8 字节边界对齐。请注意变长类型必须有至少 4 字节的对齐， 因为它们必须包含一个 int4 作为它们的***个成份。

storage 参数允许为变长数据类型选择存储策略。 （定长类型只允许使用 plain）。 plain 声明该数据类型总是用内联的方式而不是压缩的方式存储。 extended 声明系统将首先试图压缩一个长的数据值，然后如果它仍然太长的话就将它的值移出主表的行， 但系统将不会压缩它。 main 允许压缩，但是不赞成把数值移动出主表。 （用这种存储策略的数据项可能仍将移动出主表，如果不能放在一行里的话， 但是它们将比 extended 和 external 项更愿意呆在主表里。）

 如果用户希望字段的数据类型缺省时不是 NULL，而是其它什么东西， 那么你可以声明一个缺省值。 在 DEFAULT 关键字里面声明缺省值。 （这样的缺省可以被附着在特定字段上的明确的 DEFAULT 子句覆盖。）

 要表示一个类型是数组，用 ELEMENT 关键字声明数组元素的类型。 比如，要定义一个 4 字节整数(int4)的数组，声明
  ELEMENT = int4

。 有关数组类型的更多细节在下面描述。

 要声明用于这种类型数组的外部形式的数值之间的分隔符，可用 delimiter  声明指定分隔符。缺省的分隔符是逗号（,）。 请注意分隔符是和数组元素类型相关联，而不是数组类型本身。  

ARRAY TYPES 数组类型

 在创建用户定义数据类型的时候，PostgreSQL  自动创建一个与之关联的数组类型，其名字由该基本类型的名字前缀一个下划线组成。 分析器理解这个命名传统，并且把对类型为 foo[] 的字段的请求转换成对类型为 _foo  的字段的请求。这个隐含创建的数组类型是变长并且使用内建的输入和输出函数 array_in 和 array_out。

 你很可能会问如果系统自动制作正确的数组类型，那为什么有个 ELEMENT选项？使用 ELEMENT 有用的唯一的场合是在你制作的定长类型碰巧在内部是一个一定数目相同事物的数组， 而你又想允许这 N 个事物可以通过脚标直接关联，以及那些你准备把该类型当做整体进行的操作。 比如，类型 name 就允许其构成 char 用这种方法关联。 一个二维的 point 类型也可以允许其两个构成浮点型按照类似 point[0] 和 point[1] 的方法关联。  请注意这个功能只适用与那些内部形式是一个相同的定长域的序列的类型。 一个可以脚标化的变长类型必须有被 array_in 和 array_out 使用的一般化的内部表现形式。 出于历史原因（也就是说，那些明显错误但补救来得太迟的问题），定长数组类型的脚标从零开始，而不是象变长类型那样的从一开始。  

PARAMETERS 参数

name

 将要创建的类型名（可以有模式修饰）。

attribute_name

 复合类型的一个属性（字段）的名字。

data_type

 一个要成为一个复合类型的字段的现有数据类型的名字。

input_function

 一个函数的名称， 将数据从外部类型转换成内部类型。

output_function

 一个函数的名称， 将数据从内部格式转换成适于显示的形式。

receive_function

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

send_function

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

internallength

 一个数值常量，说明新类型的内部表现形式的长度。缺省的假设是它是变长的。

alignment

 该数据类型的存储对齐要求。如果声明了，必须是 char， int2， int4 或 double； 缺省是 int4。

storage

 该数据类型的存储策略。如果声明了，必须是 plain，external， extended，或 main； 缺省是 plain。

default

 该类型的缺省值。通常是省略它的，所以缺省是 NULL。

element

 被创建的类型是数组；这个声明数组元素的类型。

delimiter

 将用做数组的数据元素之间分隔符的字符。

NOTES 注意

 用户定义类型名不能以下划线（_） 开头而且只能有 62 个字符长。（或者通常是 NAMEDATALEN-2， 而不是其它名字那样的可以有 NAMEDATALEN-1 个字符）。 以下划线开头的类型名被解析成内部创建的数组类型名。

 在 PostgreSQL 版本 7.3 以前，我们要通过使用占位伪类型 opaque 代替函数的前向引用来避免创建壳类型。 7.3 之前 cstring 参数和结果同样需要声明伪 opaque。 要支持装载旧的转储外那间，CREATE TYPE 将接受那些用 opaque声明的函数， 但是它回发出一条通知并且用正确的类型改变函数的声明。  

EXAMPLES 例子

 这个例子创建一个复合类型并且在一个函数定义中使用它：

CREATE TYPE compfoo AS (f1 int, f2 text);
CREATE FUNCTION getfoo() RETURNS SETOF compfoo AS
  'SELECT fooid, fooname FROM foo' LANGUAGE SQL;

 这个命令创建box数据类型，并且将这种类型用于一个表定义：

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
);

 更多的例子，包括合适的输入和输出函数，在 Chapter 31``Extending SQL'' in the documentation。  

COMPATIBILITY 兼容性

CREATE TYPE 命令是 PostgreSQL 扩展。在 SQL99 里有一个 CREATE TYPE 语句，但是细节上和 PostgreSQL 的有比较大区别。  

SEE ALSO 参见

CREATE FUNCTION [create_function(7)], DROP TYPE [drop_type(l)]  

#p#

NAME

CREATE TYPE - define a new data type

SYNOPSIS

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

CREATE TYPE name (
    INPUT = input_function,
    OUTPUT = output_function
    [ , RECEIVE = receive_function ]
    [ , SEND = send_function ]
    [ , INTERNALLENGTH = { internallength | VARIABLE } ]
    [ , PASSEDBYVALUE ]
    [ , ALIGNMENT = alignment ]
    [ , STORAGE = storage ]
    [ , DEFAULT = default ]
    [ , ELEMENT = element ]
    [ , DELIMITER = delimiter ]
)

DESCRIPTION

CREATE TYPE registers a new data type for use in the current data base. The user who defines a type becomes its owner.

If a schema name is given then the type is created in the specified schema. Otherwise it is created in the current schema. The type name must be distinct from the name of any existing type or domain in the same schema. (Because tables have associated data types, the type name must also be distinct from the name of any existing table in the same schema.)  

COMPOSITE TYPES

The first form of CREATE TYPE creates a composite type. The composite type is specified by a list of attribute names and data types. This is essentially the same as the row type of a table, but using CREATE TYPE avoids the need to create an actual table when all that is wanted is to define a type. A stand-alone composite type is useful as the return type of a function.  

BASE TYPES

The second form of CREATE TYPE creates a new base type (scalar type). The parameters may appear in any order, not only that illustrated above, and most are optional. You must register two or more functions (using CREATE FUNCTION) before defining the type. The support functions input_function and output_function are required, while the functions receive_function and send_function are optional. Generally these functions have to be coded in C or another low-level language.

The input_function converts the type's external textual representation to the internal representation used by the operators and functions defined for the type. output_function performs the reverse transformation. The input function may be declared as taking one argument of type cstring, or as taking three arguments of types cstring, oid, integer. The first argument is the input text as a C string, the second argument is the element type in case this is an array type, and the third is the typmod of the destination column, if known. The input function should return a value of the data type itself. The output function may be declared as taking one argument of the new data type, or as taking two arguments of which the second is type oid. The second argument is again the array element type for array types. The output function should return type cstring.

The optional receive_function converts the type's external binary representation to the internal representation. If this function is not supplied, the type cannot participate in binary input. The binary representation should be chosen to be cheap to convert to internal form, while being reasonably portable. (For example, the standard integer data types use network byte order as the external binary representation, while the internal representation is in the machine's native byte order.) The receive function should perform adequate checking to ensure that the value is valid. The receive function may be declared as taking one argument of type internal, or two arguments of types internal and oid. It must return a value of the data type itself. (The first argument is a pointer to a StringInfo buffer holding the received byte string; the optional second argument is the element type in case this is an array type.) Similarly, the optional send_function converts from the internal representation to the external binary representation. If this function is not supplied, the type cannot participate in binary output. The send function may be declared as taking one argument of the new data type, or as taking two arguments of which the second is type oid. The second argument is again the array element type for array types. The send function must return type bytea.

You should at this point be wondering how the input and output functions can be declared to have results or arguments of the new type, when they have to be created before the new type can be created. The answer is that the input function must be created first, then the output function (and the binary I/O functions if wanted), and finally the data type. PostgreSQL will first see the name of the new data type as the return type of the input function. It will create a ``shell'' type, which is simply a placeholder entry in the system catalog, and link the input function definition to the shell type. Similarly the other functions will be linked to the (now already existing) shell type. Finally, CREATE TYPE replaces the shell entry with a complete type definition, and the new type can be used.

While the details of the new type's internal representation are only known to the I/O functions and other functions you create to work with the type, there are several properties of the internal representation that must be declared to PostgreSQL. Foremost of these is internallength. Base data types can be fixed-length, in which case internallength is a positive integer, or variable length, indicated by setting internallength to VARIABLE. (Internally, this is represented by setting typlen to -1.) The internal representation of all variable-length types must start with a 4-byte integer giving the total length of this value of the type.

The optional flag PASSEDBYVALUE indicates that values of this data type are passed by value, rather than by reference. You may not pass by value types whose internal representation is larger than the size of the Datum type (4 bytes on most machines, 8 bytes on a few).

The alignment parameter specifies the storage alignment required for the data type. The allowed values equate to alignment on 1, 2, 4, or 8 byte boundaries. Note that variable-length types must have an alignment of at least 4, since they necessarily contain an int4 as their first component.

The storage parameter allows selection of storage strategies for variable-length data types. (Only plain is allowed for fixed-length types.) plain specifies that data of the type will always be stored in-line and not compressed. extended specifies that the system will first try to compress a long data value, and will move the value out of the main table row if it's still too long. external allows the value to be moved out of the main table, but the system will not try to compress it. main allows compression, but discourages moving the value out of the main table. (Data items with this storage strategy may still be moved out of the main table if there is no other way to make a row fit, but they will be kept in the main table preferentially over extended and external items.)

A default value may be specified, in case a user wants columns of the data type to default to something other than the null value. Specify the default with the DEFAULT key word. (Such a default may be overridden by an explicit DEFAULT clause attached to a particular column.)

To indicate that a type is an array, specify the type of the array elements using the ELEMENT key word. For example, to define an array of 4-byte integers (int4), specify ELEMENT = int4. More details about array types appear below.

To indicate the delimiter to be used between values in the external representation of arrays of this type, delimiter can be set to a specific character. The default delimiter is the comma (,). Note that the delimiter is associated with the array element type, not the array type itself.  

ARRAY TYPES

Whenever a user-defined base data type is created, PostgreSQL automatically creates an associated array type, whose name consists of the base type's name prepended with an underscore. The parser understands this naming convention, and translates requests for columns of type foo[] into requests for type _foo. The implicitly-created array type is variable length and uses the built-in input and output functions array_in and array_out.

You might reasonably ask why there is an ELEMENT option, if the system makes the correct array type automatically. The only case where it's useful to use ELEMENT is when you are making a fixed-length type that happens to be internally an array of a number of identical things, and you want to allow these things to be accessed directly by subscripting, in addition to whatever operations you plan to provide for the type as a whole. For example, type name allows its constituent char elements to be accessed this way. A 2-D point type could allow its two component numbers to be accessed like point[0] and point[1]. Note that this facility only works for fixed-length types whose internal form is exactly a sequence of identical fixed-length fields. A subscriptable variable-length type must have the generalized internal representation used by array_in and array_out. For historical reasons (i.e., this is clearly wrong but it's far too late to change it), subscripting of fixed-length array types starts from zero, rather than from one as for variable-length arrays.  

PARAMETERS

name

The name (optionally schema-qualified) of a type to be created.

attribute_name

The name of an attribute (column) for the composite type.

data_type

The name of an existing data type to become a column of the composite type.

input_function

The name of a function that converts data from the type's external textual form to its internal form.

output_function

The name of a function that converts data from the type's internal form to its external textual form.

receive_function

The name of a function that converts data from the type's external binary form to its internal form.

send_function

The name of a function that converts data from the type's internal form to its external binary form.

internallength

A numeric constant that specifies the length in bytes of the new type's internal representation. The default assumption is that it is variable-length.

alignment

The storage alignment requirement of the data type. If specified, it must be char, int2, int4, or double; the default is int4.

storage

The storage strategy for the data type. If specified, must be plain, external, extended, or main; the default is plain.

default

The default value for the data type. If this is omitted, the default is null.

element

The type being created is an array; this specifies the type of the array elements.

delimiter

The delimiter character to be used between values in arrays made of this type.

NOTES

User-defined type names cannot begin with the underscore character (_) and can only be 62 characters long (or in general NAMEDATALEN - 2, rather than the NAMEDATALEN - 1 characters allowed for other names). Type names beginning with underscore are reserved for internally-created array type names.

In PostgreSQL versions before 7.3, it was customary to avoid creating a shell type by replacing the functions' forward references to the type name with the placeholder pseudotype opaque. The cstring arguments and results also had to be declared as opaque before 7.3. To support loading of old dump files, CREATE TYPE will accept functions declared using opaque, but it will issue a notice and change the function's declaration to use the correct types.  

EXAMPLES

This example creates a composite type and uses it in a function definition:

CREATE TYPE compfoo AS (f1 int, f2 text);
CREATE FUNCTION getfoo() RETURNS SETOF compfoo AS
  'SELECT fooid, fooname FROM foo' LANGUAGE SQL;

This example creates the base data type box and then uses the type in a table definition:

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

CREATE TABLE myboxes (
    id integer,
    description box
);

If the internal structure of box were an array of four float4 elements, we might instead use

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

which would allow a box value's component numbers to be accessed by subscripting. Otherwise the type behaves the same as before.

This example creates a large object type and uses it in a table definition:

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

More examples, including suitable input and output functions, are in the chapter called ``Extending SQL'' in the documentation.  

COMPATIBILITY

This CREATE TYPE command is a PostgreSQL extension. There is a CREATE TYPE statement in SQL99 that is rather different in detail.  

SEE ALSO

CREATE FUNCTION [create_function(7)], DROP TYPE [drop_type(l)]