Postgresql 中文操作指南

CREATE CAST

CREATE CAST — 定义新类型转换

CREATE CAST — define a new cast

Synopsis

CREATE CAST (source_type AS target_type)
    WITH FUNCTION function_name [ (argument_type [, ...]) ]
    [ AS ASSIGNMENT | AS IMPLICIT ]

CREATE CAST (source_type AS target_type)
    WITHOUT FUNCTION
    [ AS ASSIGNMENT | AS IMPLICIT ]

CREATE CAST (source_type AS target_type)
    WITH INOUT
    [ AS ASSIGNMENT | AS IMPLICIT ]

Description

CREATE CAST 定义了新类型转换。类型转换指定如何执行两种数据类型间的转换。例如，

CREATE CAST defines a new cast. A cast specifies how to perform a conversion between two data types. For example,

SELECT CAST(42 AS float8);

通过调用以前指定的函数（在此情况下为 float8(int4) ）将整数常量 42 转换为 float8 类型。（如果没有定义适当的类型转换，则转换将失败。）

converts the integer constant 42 to type float8 by invoking a previously specified function, in this case float8(int4). (If no suitable cast has been defined, the conversion fails.)

两种类型可以是 binary coercible ，这意味着可以免费执行转换，而无需调用任何函数。这需要相应的值使用相同的内部表示形式。例如，类型 text 和 varchar 在两个方向上都是二进制可转换的。二进制可转换性并不一定是对称关系。例如，从 xml 到 text 的类型转换可在当前实现中免费执行，但反向则需要一个至少执行语法检查的函数。（在两个方向上均可进行二进制转换的两种类型也称为二进制兼容。）

Two types can be binary coercible, which means that the conversion can be performed “for free” without invoking any function. This requires that corresponding values use the same internal representation. For instance, the types text and varchar are binary coercible both ways. Binary coercibility is not necessarily a symmetric relationship. For example, the cast from xml to text can be performed for free in the present implementation, but the reverse direction requires a function that performs at least a syntax check. (Two types that are binary coercible both ways are also referred to as binary compatible.)

您可以使用 WITH INOUT 语法将类型转换定义为 I/O conversion cast 。通过调用源数据类型的输出函数并传入所得字符串到目标数据类型的输入函数，便能执行 I/O 转换类型转换。在许多常见情况下，此功能避免了为转换编写单独的类型转换函数的需要。I/O 转换类型转换的作用与基于函数的常规类型转换相同；只有其实现有所不同。

You can define a cast as an I/O conversion cast by using the WITH INOUT syntax. An I/O conversion cast is performed by invoking the output function of the source data type, and passing the resulting string to the input function of the target data type. In many common cases, this feature avoids the need to write a separate cast function for conversion. An I/O conversion cast acts the same as a regular function-based cast; only the implementation is different.

默认情况下，类型转换只能通过显式类型转换请求（即显式 CAST(_x AS typename )_ 或 x::_typename_ 结构）调用。

By default, a cast can be invoked only by an explicit cast request, that is an explicit CAST(_x AS typename)_ or x::typename construct.

如果类型转换标记为 AS ASSIGNMENT ，则可以将其隐式调用，此时会将值指定给目标数据类型的一列。例如，假设 foo.f1 是一列 text 类型，则

If the cast is marked AS ASSIGNMENT then it can be invoked implicitly when assigning a value to a column of the target data type. For example, supposing that foo.f1 is a column of type text, then:

INSERT INTO foo (f1) VALUES (42);

在从类型 integer 到类型 text 的类型转换标记为 AS ASSIGNMENT 时才会被允许，否则不会。（我们通常使用 assignment cast 一词来描述这种类型转换。）

will be allowed if the cast from type integer to type text is marked AS ASSIGNMENT, otherwise not. (We generally use the term assignment cast to describe this kind of cast.)

如果类型转换标记为 AS IMPLICIT ，则可以在任何上下文中（不管是赋值还是表达式内部）隐式地调用它。（我们通常使用 implicit cast 一词来描述这种类型转换。）例如，考虑以下查询：

If the cast is marked AS IMPLICIT then it can be invoked implicitly in any context, whether assignment or internally in an expression. (We generally use the term implicit cast to describe this kind of cast.) For example, consider this query:

SELECT 2 + 4.0;

解析器最初将常量标记为分别为类型 integer 和 numeric 。系统目录中没有 integer + numeric 运算符，但有一个 numeric + numeric 运算符。因此，如果从 integer 到 numeric 的类型转换可用且标记为 AS IMPLICIT ，则查询将成功——事实确实如此。解析器将应用隐式类型转换，解析查询，就好像写成这样

The parser initially marks the constants as being of type integer and numeric respectively. There is no integer + numeric operator in the system catalogs, but there is a numeric + numeric operator. The query will therefore succeed if a cast from integer to numeric is available and is marked AS IMPLICIT — which in fact it is. The parser will apply the implicit cast and resolve the query as if it had been written

SELECT CAST ( 2 AS numeric ) + 4.0;

现在，目录还提供从 numeric 到 integer 的类型转换。如果该类型转换标记为 AS IMPLICIT ——它不是——那么解析器将面临在以上解释和将 numeric 常量转换为 integer 并应用 integer + integer 运算符的选择之间做出选择。由于缺乏任何关于哪种选择更好的知识，因此它将放弃并声称查询不明确。只有其中一个类型转换是隐式的这个事实是我们教解析器首选将混合的 numeric -和- integer 表达式解析为 numeric 的方式；这是没有内置知识的。

Now, the catalogs also provide a cast from numeric to integer. If that cast were marked AS IMPLICIT — which it is not — then the parser would be faced with choosing between the above interpretation and the alternative of casting the numeric constant to integer and applying the integer + integer operator. Lacking any knowledge of which choice to prefer, it would give up and declare the query ambiguous. The fact that only one of the two casts is implicit is the way in which we teach the parser to prefer resolution of a mixed numeric-and-integer expression as numeric; there is no built-in knowledge about that.

将转换标记为隐式时最好保持保守态度。过多的隐式转换路径会导致 PostgreSQL 对命令进行令人惊讶的解释，或者由于有多重可能的解释而无法解析命令。一条好的经验法则是仅对同一类型类别中类型之间保留信息的转换进行隐式调用。例如，从 int2 到 int4 的转换可以合理地是隐式的，但从 float8 到 int4 的转换可能应该仅用于赋值。跨类型类别的转换，例如 text 到 int4 ，最好仅显式进行。

It is wise to be conservative about marking casts as implicit. An overabundance of implicit casting paths can cause PostgreSQL to choose surprising interpretations of commands, or to be unable to resolve commands at all because there are multiple possible interpretations. A good rule of thumb is to make a cast implicitly invokable only for information-preserving transformations between types in the same general type category. For example, the cast from int2 to int4 can reasonably be implicit, but the cast from float8 to int4 should probably be assignment-only. Cross-type-category casts, such as text to int4, are best made explicit-only.

Note

有时出于可用性或标准遵守原因，需要在类型集之间提供多个隐式转换，导致在上述情况下无法避免的歧义。解析器有一个基于 type categories 和 preferred types 的后备启发式算法，可以在这种情况下帮助提供所需的的行为。有关更多信息，请参见 CREATE TYPE 。

Sometimes it is necessary for usability or standards-compliance reasons to provide multiple implicit casts among a set of types, resulting in ambiguity that cannot be avoided as above. The parser has a fallback heuristic based on type categories and preferred types that can help to provide desired behavior in such cases. See CREATE TYPE for more information.

要创建转换，您必须拥有源数据类型或目标数据类型并在另一类型上拥有 USAGE 权限。要创建二进制可强制转换，您必须是超用户。（此限制的制定是因为错误的二进制可强制转换可能会轻松导致服务器崩溃。）

To be able to create a cast, you must own the source or the target data type and have USAGE privilege on the other type. To create a binary-coercible cast, you must be superuser. (This restriction is made because an erroneous binary-coercible cast conversion can easily crash the server.)

Parameters

  • source_type

    • The name of the source data type of the cast.

  • target_type

    • The name of the target data type of the cast.

  • _function_name[(argument_type [, …​])]_

    • The function used to perform the cast. The function name can be schema-qualified. If it is not, the function will be looked up in the schema search path. The function’s result data type must match the target type of the cast. Its arguments are discussed below. If no argument list is specified, the function name must be unique in its schema.

  • WITHOUT FUNCTION

    • Indicates that the source type is binary-coercible to the target type, so no function is required to perform the cast.

  • WITH INOUT

    • Indicates that the cast is an I/O conversion cast, performed by invoking the output function of the source data type, and passing the resulting string to the input function of the target data type.

  • AS ASSIGNMENT

    • Indicates that the cast can be invoked implicitly in assignment contexts.

  • AS IMPLICIT

    • Indicates that the cast can be invoked implicitly in any context.

转换实现函数可以有一个、两个或三个参数。第一个参数类型必须与转换源类型相同或可从转换源类型进行二进制强制转换。第二个参数（如果存在）必须为类型 integer ；它接收与目标类型关联的类型修饰符，或者在没有类型修饰符时接收 -1 。第三个参数（如果存在）必须为类型 boolean ；它接收 true （如果转换是显式转换），否则接收 false 。（奇怪的是，SQL 标准在某些情况下要求显式和隐式转换的行为不同。为必须实现此类转换的函数提供此参数。不建议您设计自己的数据类型，使得这些设置至关重要。）

Cast implementation functions can have one to three arguments. The first argument type must be identical to or binary-coercible from the cast’s source type. The second argument, if present, must be type integer; it receives the type modifier associated with the destination type, or -1 if there is none. The third argument, if present, must be type boolean; it receives true if the cast is an explicit cast, false otherwise. (Bizarrely, the SQL standard demands different behaviors for explicit and implicit casts in some cases. This argument is supplied for functions that must implement such casts. It is not recommended that you design your own data types so that this matters.)

转换函数的返回类型必须与转换目标类型相同或可从转换目标类型进行二进制强制转换。

The return type of a cast function must be identical to or binary-coercible to the cast’s target type.

通常，转换必须具有不同的源数据类型和目标数据类型。但是，如果转换具有具有多个参数的转换实现函数，则允许声明具有相同源数据类型和目标数据类型的转换。这用于在系统编目中表示特定于类型的长度强制转换函数。命名的函数用于将此类型的的值强制转换为由其第二个参数给出的类型修饰符值。

Ordinarily a cast must have different source and target data types. However, it is allowed to declare a cast with identical source and target types if it has a cast implementation function with more than one argument. This is used to represent type-specific length coercion functions in the system catalogs. The named function is used to coerce a value of the type to the type modifier value given by its second argument.

当转换具有不同的源数据类型和目标数据类型以及一个采用多个参数的函数时，它支持以单步方式从一种类型转换到另一种类型并应用长度强制转换。当没有此类条目时，对使用类型修饰符的类型的强制转换涉及两个转换步骤，一个用于在数据类型之间进行转换，另一个用于应用修饰符。

When a cast has different source and target types and a function that takes more than one argument, it supports converting from one type to another and applying a length coercion in a single step. When no such entry is available, coercion to a type that uses a type modifier involves two cast steps, one to convert between data types and a second to apply the modifier.

当前对域类型进行或从域类型进行转换无效。对域进行或从域进行转换使用与该域的底层类型关联的转换。

A cast to or from a domain type currently has no effect. Casting to or from a domain uses the casts associated with its underlying type.

Notes

用 DROP CAST 删除用户自定义转换。

Use DROP CAST to remove user-defined casts.

请记住，如果您希望能够双向转换类型，您需要显式地声明双向转换。

Remember that if you want to be able to convert types both ways you need to declare casts both ways explicitly.

通常不需要在用户自定义类型和标准字符串类型 ( text 、 varchar 和 char(_n ) 之间创建转换，以及定义为属于字符串类别的用户自定义类型之间创建转换）。PostgreSQL 为此提供自动 I/O 转换。对字符串类型的自动转换被视为赋值转换，而从字符串类型的自动转换仅为显式转换。您可以通过声明自己的转换来覆盖此行为以替换自动转换，但通常这样做的唯一理由是您希望转换比标准仅赋值或仅显式的设置更容易调用。另一个可能的原因是您希望转换的行为不同于类型的 I/O 函数；但这足够令人惊讶，以至于您应该好好考虑是否是个好主意。（少数内置类型确实在转换方面具有不同的行为方式，这主要是由于 SQL 标准的要求。）

It is normally not necessary to create casts between user-defined types and the standard string types (text, varchar, and char(_n)_, as well as user-defined types that are defined to be in the string category). PostgreSQL provides automatic I/O conversion casts for that. The automatic casts to string types are treated as assignment casts, while the automatic casts from string types are explicit-only. You can override this behavior by declaring your own cast to replace an automatic cast, but usually the only reason to do so is if you want the conversion to be more easily invokable than the standard assignment-only or explicit-only setting. Another possible reason is that you want the conversion to behave differently from the type’s I/O function; but that is sufficiently surprising that you should think twice about whether it’s a good idea. (A small number of the built-in types do indeed have different behaviors for conversions, mostly because of requirements of the SQL standard.)

虽然不是必需的，但建议您继续遵循此惯例，即以目标数据类型来命名转换实现函数。许多用户习惯于能够使用函数样式符号 typename ( x ) 来转换数据类型。实际上，此符号只不过是调用转换实现函数；它并不被视为转换而受到特殊对待。如果您的转换函数未被命名为支持此约定，那么您将使用户感到惊讶。由于 PostgreSQL 允许使用不同参数类型重载同一函数名称，因此可以轻松地拥有从不同类型进行转换并全部使用目标类型名称的多个转换函数。

While not required, it is recommended that you continue to follow this old convention of naming cast implementation functions after the target data type. Many users are used to being able to cast data types using a function-style notation, that is typename(x). This notation is in fact nothing more nor less than a call of the cast implementation function; it is not specially treated as a cast. If your conversion functions are not named to support this convention then you will have surprised users. Since PostgreSQL allows overloading of the same function name with different argument types, there is no difficulty in having multiple conversion functions from different types that all use the target type’s name.

Note

实际上，前面的段落过于简单化：函数调用结构在两种情况下会被视为强制转换请求，而无需与实际函数匹配。如果函数调用 name ( x ) 与任何现有函数完全不匹配，但 name 是某个数据类型的名称，且 pg_cast 提供了一个从 x 的类型到该类型的二进制可强制转换转换，那么该调用将被解释为二进制可强制转换转换。实现此例外是为了使二进制可强制转换转换可以使用函数语法进行调用，即使它们不具备任何函数。同样，如果没有 pg_cast 条目但转换是到字符串类型或者从字符串类型转换，那么该调用将被解释为 I/O 转换转换。实现此例外是为了使 I/O 转换转换可以使用函数语法进行调用。

Actually the preceding paragraph is an oversimplification: there are two cases in which a function-call construct will be treated as a cast request without having matched it to an actual function. If a function call name(x) does not exactly match any existing function, but name is the name of a data type and pg_cast provides a binary-coercible cast to this type from the type of x, then the call will be construed as a binary-coercible cast. This exception is made so that binary-coercible casts can be invoked using functional syntax, even though they lack any function. Likewise, if there is no pg_cast entry but the cast would be to or from a string type, the call will be construed as an I/O conversion cast. This exception allows I/O conversion casts to be invoked using functional syntax.

Note

此外，还存在针对该例外的例外：无法使用函数语法调用从复合类型到字符串类型的 I/O 转换转换，但必须使用显式强制转换语法编写（ CAST 或 :: 表示法）。添加此例外是因为在引入自动提供的 I/O 转换转换之后，发现很容易在打算调用函数或列引用时意外调用此类转换。

There is also an exception to the exception: I/O conversion casts from composite types to string types cannot be invoked using functional syntax, but must be written in explicit cast syntax (either CAST or :: notation). This exception was added because after the introduction of automatically-provided I/O conversion casts, it was found too easy to accidentally invoke such a cast when a function or column reference was intended.

Examples

若要使用函数 int4(bigint) 创建从类型 bigint 到类型 int4 的赋值转换：

To create an assignment cast from type bigint to type int4 using the function int4(bigint):

CREATE CAST (bigint AS int4) WITH FUNCTION int4(bigint) AS ASSIGNMENT;

（此转换已经在系统中预定义。）

(This cast is already predefined in the system.)

Compatibility

CREATE CAST 命令符合 SQL 标准，但 SQL 没有针对二进制可强制转换类型或针对实现函数的额外参数做出规定。 AS IMPLICIT 也是 PostgreSQL 的扩展。

The CREATE CAST command conforms to the SQL standard, except that SQL does not make provisions for binary-coercible types or extra arguments to implementation functions. AS IMPLICIT is a PostgreSQL extension, too.

See Also

CREATE FUNCTION ， CREATE TYPE ， DROP CAST

CREATE FUNCTION, CREATE TYPE, DROP CAST