Postgresql 中文操作指南

38.2. The PostgreSQL Type System #

PostgreSQL 数据类型可以划分为基础类型、容器类型、域和伪类型。

PostgreSQL data types can be divided into base types, container types, domains, and pseudo-types.

38.2.1. Base Types #

基类型是那些在 SQL 语言级别以下（通常在 C 等低级语言中）实现的类型，如 integer 所示。它们通常对应于通常称为抽象数据类型的内容。PostgreSQL 只能通过用户提供的函数操作此类类型，并且仅在用户描述此类类型的行为时才能了解此类类型的行为。内建的基本类型在 Chapter 8 中进行描述。

Base types are those, like integer, that are implemented below the level of the SQL language (typically in a low-level language such as C). They generally correspond to what are often known as abstract data types. PostgreSQL can only operate on such types through functions provided by the user and only understands the behavior of such types to the extent that the user describes them. The built-in base types are described in Chapter 8.

枚举（enum）类型可以被视为基类型的子类别。主要区别在于它们可以使用纯 SQL 命令创建，而无需任何低级编程。有关详细信息，请参阅 Section 8.7。

Enumerated (enum) types can be considered as a subcategory of base types. The main difference is that they can be created using just SQL commands, without any low-level programming. Refer to Section 8.7 for more information.

38.2.2. Container Types #

PostgreSQL 具有三种“容器”类型，即包含其他类型多个值的类型。这些类型包括数组、复合类型和范围。

PostgreSQL has three kinds of “container” types, which are types that contain multiple values of other types. These are arrays, composites, and ranges.

数组可以容纳多个全为相同类型的值。为每个基类型、复合类型、范围类型和域类型自动创建一个数组类型。但是没有数组的数组。就类型系统而言，多维数组与一维数组相同。有关详细信息，请参阅 Section 8.15。

Arrays can hold multiple values that are all of the same type. An array type is automatically created for each base type, composite type, range type, and domain type. But there are no arrays of arrays. So far as the type system is concerned, multi-dimensional arrays are the same as one-dimensional arrays. Refer to Section 8.15 for more information.

每当用户创建表时，都会创建复合类型或行类型。也可以使用 CREATE TYPE 定义没有关联表的“独立”复合类型。复合类型只是带有关联字段名称的类型列表。复合类型的值是行或字段值的记录。有关更多信息，请参考 Section 8.16 。

Composite types, or row types, are created whenever the user creates a table. It is also possible to use CREATE TYPE to define a “stand-alone” composite type with no associated table. A composite type is simply a list of types with associated field names. A value of a composite type is a row or record of field values. Refer to Section 8.16 for more information.

范围类型可以容纳同种类型的两个值，它们分别是范围的下限和上限。范围类型是由用户创建的，虽然也存在一些内建范围类型。有关详细信息，请参阅 Section 8.17。

A range type can hold two values of the same type, which are the lower and upper bounds of the range. Range types are user-created, although a few built-in ones exist. Refer to Section 8.17 for more information.

38.2.3. Domains #

域基于特定底层类型，并且出于许多目的，可以与它的底层类型互换。但是，域可以有约束，以将它的有效值限制为底层类型允许的子集。域使用 SQL 命令 CREATE DOMAIN 创建。有关更多信息，请参阅 Section 8.18 。

A domain is based on a particular underlying type and for many purposes is interchangeable with its underlying type. However, a domain can have constraints that restrict its valid values to a subset of what the underlying type would allow. Domains are created using the SQL command CREATE DOMAIN. Refer to Section 8.18 for more information.

38.2.4. Pseudo-Types #

有几个用于特殊目的的“伪类型”。伪类型不能作为表或容器类型的列出现，但它们可用于声明函数的参数和结果类型。这提供了一个在类型系统中识别特定函数类别的机制。 Table 8.27列出了现有的伪类型。

There are a few “pseudo-types” for special purposes. Pseudo-types cannot appear as columns of tables or components of container types, but they can be used to declare the argument and result types of functions. This provides a mechanism within the type system to identify special classes of functions. Table 8.27 lists the existing pseudo-types.

38.2.5. Polymorphic Types #

一些特殊感兴趣的伪类型是_polymorphic types_，用于声明_polymorphic functions_。此强有力的功能允许一个函数定义对许多不同数据类型进行操作，其中通过实际传递给函数的特定数据类型进行确定。多态类型显示在 Table 38.1 中。其用法的一些示例出现在 Section 38.5.11 中。

Some pseudo-types of special interest are the polymorphic types, which are used to declare polymorphic functions. This powerful feature allows a single function definition to operate on many different data types, with the specific data type(s) being determined by the data types actually passed to it in a particular call. The polymorphic types are shown in Table 38.1. Some examples of their use appear in Section 38.5.11.

Table 38.1. Polymorphic Types

Name

Family

Description

anyelement

Simple

Indicates that a function accepts any data type

anyarray

Simple

Indicates that a function accepts any array data type

anynonarray

Simple

Indicates that a function accepts any non-array data type

anyenum

Simple

Indicates that a function accepts any enum data type (see Section 8.7)

anyrange

Simple

Indicates that a function accepts any range data type (see Section 8.17)

anymultirange

Simple

Indicates that a function accepts any multirange data type (see Section 8.17)

anycompatible

Common

Indicates that a function accepts any data type, with automatic promotion of multiple arguments to a common data type

anycompatiblearray

Common

Indicates that a function accepts any array data type, with automatic promotion of multiple arguments to a common data type

anycompatiblenonarray

Common

Indicates that a function accepts any non-array data type, with automatic promotion of multiple arguments to a common data type

anycompatiblerange

Common

Indicates that a function accepts any range data type, with automatic promotion of multiple arguments to a common data type

anycompatiblemultirange

Common

Indicates that a function accepts any multirange data type, with automatic promotion of multiple arguments to a common data type

多态参数和结果是相互关联的，当解析调用多态函数的查询时，它们会被解析为特定数据类型。当有多个多态参数时，输入值的实际数据类型必须按如下所述匹配。如果函数的结果类型是多态的，或者它具有多态类型的输出参数，那么这些结果的类型将根据以下所述推导出自多态输入的实际类型。

Polymorphic arguments and results are tied to each other and are resolved to specific data types when a query calling a polymorphic function is parsed. When there is more than one polymorphic argument, the actual data types of the input values must match up as described below. If the function’s result type is polymorphic, or it has output parameters of polymorphic types, the types of those results are deduced from the actual types of the polymorphic inputs as described below.

对于多态类型的“简单”系列，匹配和推导规则如下：

For the “simple” family of polymorphic types, the matching and deduction rules work like this:

声明为 anyelement 的每个位置（参数或返回值）允许具有任何特定实际数据类型，但在任何给定的调用中，它们都必须是 same 实际类型。声明为 anyarray 的每个位置可以具有任何数组数据类型，但类似地，它们都必须是相同的类型。类似地，声明为 anyrange 的位置都必须是相同的范围类型。anymultirange 亦是如此。

Each position (either argument or return value) declared as anyelement is allowed to have any specific actual data type, but in any given call they must all be the same actual type. Each position declared as anyarray can have any array data type, but similarly they must all be the same type. And similarly, positions declared as anyrange must all be the same range type. Likewise for anymultirange.

此外，如果存在声明为 anyarray 的位置而存在声明为 anyelement 的其他位置，则 anyarray 位置中的实际数组类型必须是一个元素与 anyelement 位置中出现的类型相同的数组。anynonarray 的处理完全与 anyelement 相同，但增加了实际类型不能是数组类型的额外约束。anyenum 的处理完全与 anyelement 相同，但增加了实际类型必须是枚举类型的额外约束。

Furthermore, if there are positions declared anyarray and others declared anyelement, the actual array type in the anyarray positions must be an array whose elements are the same type appearing in the anyelement positions. anynonarray is treated exactly the same as anyelement, but adds the additional constraint that the actual type must not be an array type. anyenum is treated exactly the same as anyelement, but adds the additional constraint that the actual type must be an enum type.

类似地，如果存在声明为 anyrange 的位置而存在声明为 anyelement 或 anyarray 的其他位置，则 anyrange 位置中的实际范围类型必须是一个子类型与 anyelement 位置中出现的类型相同的范围，且与 anyarray 位置的元素类型相同。如果存在声明为 anymultirange 的位置，那么它们的实际多重范围类型必须包含与 anyrange 中声明的参数匹配的范围和与 anyelement 和 anyarray 中声明的参数匹配的基本元素。

Similarly, if there are positions declared anyrange and others declared anyelement or anyarray, the actual range type in the anyrange positions must be a range whose subtype is the same type appearing in the anyelement positions and the same as the element type of the anyarray positions. If there are positions declared anymultirange, their actual multirange type must contain ranges matching parameters declared anyrange and base elements matching parameters declared anyelement and anyarray.

因此，当多个参数位置声明为具有多态类型时，最终结果是仅允许实际参数类型的某些组合。例如，声明为 equal(anyelement, anyelement) 的函数将采用任意两个输入值，只要它们具有相同的类型即可。

Thus, when more than one argument position is declared with a polymorphic type, the net effect is that only certain combinations of actual argument types are allowed. For example, a function declared as equal(anyelement, anyelement) will take any two input values, so long as they are of the same data type.

当函数的返回值声明为多态类型时，必须至少有一个参数位置也是多态的，并且为多态参数提供的实际数据类型将确定该调用的实际结果类型。例如，如果没有现有的数组下标编制机制，可以定义一个实现 subscript(anyarray, integer) returns anyelement 式下标编制的函数。此声明会将实际第一个参数限定为数组类型，并允许解析器从实际第一个参数的类型推断正确的结果类型。另一个示例是一个声明为 f(anyarray) returns anyenum 的函数，该函数仅接受枚举类型的数组。

When the return value of a function is declared as a polymorphic type, there must be at least one argument position that is also polymorphic, and the actual data type(s) supplied for the polymorphic arguments determine the actual result type for that call. For example, if there were not already an array subscripting mechanism, one could define a function that implements subscripting as subscript(anyarray, integer) returns anyelement. This declaration constrains the actual first argument to be an array type, and allows the parser to infer the correct result type from the actual first argument’s type. Another example is that a function declared as f(anyarray) returns anyenum will only accept arrays of enum types.

在大多数情况下，解析器可以从不同多态类型的参数推断多态结果类型的实际数据类型；例如 anyarray 可以从 anyelement 中推断出来，反之亦然。一个例外是，多态结果类型 anyrange 需要类型 anyrange 的参数；它不能从 anyarray 或 anyelement 参数推断出来。这是因为可能有多个具有相同子类型的范围类型。

In most cases, the parser can infer the actual data type for a polymorphic result type from arguments that are of a different polymorphic type in the same family; for example anyarray can be deduced from anyelement or vice versa. An exception is that a polymorphic result of type anyrange requires an argument of type anyrange; it cannot be deduced from anyarray or anyelement arguments. This is because there could be multiple range types with the same subtype.

请注意，anynonarray 和 anyenum 并未表示单独的类型变量；它们与 anyelement 具有相同的类型，只是有额外的约束。例如，将函数声明为 f(anyelement, anyenum) 等效于将其声明为 f(anyenum, anyenum)：两个实际参数都必须是相同的枚举类型。

Note that anynonarray and anyenum do not represent separate type variables; they are the same type as anyelement, just with an additional constraint. For example, declaring a function as f(anyelement, anyenum) is equivalent to declaring it as f(anyenum, anyenum): both actual arguments have to be the same enum type.

对于“公共”多态类型族，匹配和推导规则的作用与“简单”类型族的工作方式大致相同，具有一处主要不同：参数的实际类型不必相同，只要它们可以隐式转换为单一公共类型即可。公共类型根据与 UNION_和相关结构相同规则选择（参见 Section 10.5）。公共类型的选择考虑 _anycompatible_和 _anycompatiblenonarray_输入的实际类型，_anycompatiblearray_输入的数组元素类型，_anycompatiblerange_输入的范围子类型和 _anycompatiblemultirange_输入的多范围子类型。如果存在 _anycompatiblenonarray，则要求公共类型为非数组类型。一旦确定公共类型，位于 _anycompatible_和 _anycompatiblenonarray_位置的参数将自动转换为该类型，位于 _anycompatiblearray_位置的参数将自动转换为该类型的数组类型。

For the “common” family of polymorphic types, the matching and deduction rules work approximately the same as for the “simple” family, with one major difference: the actual types of the arguments need not be identical, so long as they can be implicitly cast to a single common type. The common type is selected following the same rules as for UNION and related constructs (see Section 10.5). Selection of the common type considers the actual types of anycompatible and anycompatiblenonarray inputs, the array element types of anycompatiblearray inputs, the range subtypes of anycompatiblerange inputs, and the multirange subtypes of anycompatiblemultirange inputs. If anycompatiblenonarray is present then the common type is required to be a non-array type. Once a common type is identified, arguments in anycompatible and anycompatiblenonarray positions are automatically cast to that type, and arguments in anycompatiblearray positions are automatically cast to the array type for that type.

由于无法只知道子类型就选择范围类型，因此使用 anycompatiblerange 和/或 anycompatiblemultirange 要求用该类型声明的所有参数具有相同的实际范围和/或多范围类型，且该类型的子类型必须与所选的公共类型一致，这样就不需要转换范围值。与 anyrange 和 anymultirange 一样，使用 anycompatiblerange 和 anymultirange 作为函数结果类型要求存在 anycompatiblerange 或 anycompatiblemultirange 参数。

Since there is no way to select a range type knowing only its subtype, use of anycompatiblerange and/or anycompatiblemultirange requires that all arguments declared with that type have the same actual range and/or multirange type, and that that type’s subtype agree with the selected common type, so that no casting of the range values is required. As with anyrange and anymultirange, use of anycompatiblerange and anymultirange as a function result type requires that there be an anycompatiblerange or anycompatiblemultirange argument.

请注意，没有 anycompatibleenum 类型。这种类型并不是很实用，因为通常没有对枚举类型的隐式转换，这意味着无法解析不相同枚举输入的公共类型。

Notice that there is no anycompatibleenum type. Such a type would not be very useful, since there normally are not any implicit casts to enum types, meaning that there would be no way to resolve a common type for dissimilar enum inputs.

“简单”和“公共”多态族代表两个互相独立的类型变量集。以为例：

The “simple” and “common” polymorphic families represent two independent sets of type variables. Consider for example

CREATE FUNCTION myfunc(a anyelement, b anyelement,
                       c anycompatible, d anycompatible)
RETURNS anycompatible AS ...

在这个函数的实际调用中，前两个输入必须具有完全相同的类型。最后两个输入必须可提升为公共类型，但此类型不一定与前两个输入的类型有关。结果将具有最后两个输入的公共类型。

In an actual call of this function, the first two inputs must have exactly the same type. The last two inputs must be promotable to a common type, but this type need not have anything to do with the type of the first two inputs. The result will have the common type of the last two inputs.

可变参数函数（采用一个可变参数数，如此 Section 38.5.6中的）可以是多态的：这是通过将其最后一个参数声明为 _VARIADICanyarray_或 _VARIADICanycompatiblearray_来实现的。为了参数匹配和确定实际结果类型，此函数的行为与编写了相应数量的 _anynonarray_或 _anycompatiblenonarray_参数一样。

A variadic function (one taking a variable number of arguments, as in Section 38.5.6) can be polymorphic: this is accomplished by declaring its last parameter as VARIADIC anyarray or VARIADIC anycompatiblearray. For purposes of argument matching and determining the actual result type, such a function behaves the same as if you had written the appropriate number of anynonarray or anycompatiblenonarray parameters.