create_cast 中文man页面

NAME

CREATE CAST - 定义一个用户定义的转换

SYNOPSIS

CREATE CAST (sourcetype AS targettype)
    WITH FUNCTION funcname (argtype)
    [ AS ASSIGNMENT | AS IMPLICIT ]

CREATE CAST (sourcetype AS targettype)
    WITHOUT FUNCTION
    [ AS ASSIGNMENT | AS IMPLICIT ]

DESCRIPTION 描述

CREATE CAST 定义一个新的转换。 一个转换说明如何在两个类型之间进行转换。比如：

SELECT CAST(42 AS text);

通过调用前面声明的一个函数，把整数常量 42 转换成类型 text， 在这个例子里是 text(int4)。（如果没有预先定义好合适的转换，那么这个转换失败。）

两种类型可以是二进制兼容的， 意思是它们之间可以"自由转换"而不用调用任何函数。 这就需要那个对应的数值使用同样的内部表现形式。 比如，类型 text 和 varchar 是二进制兼容的。

缺省时，只有在明确要求转换的情况下才调用一个转换， 也就是一个明确的 CAST(x AS typename)， x::typename，或者 typename(x) 构造。

如果转换标记为 AS ASSIGNMENT，那么在赋一个数值给目标数据类型的字段的时候， 可以隐含调用它。比如，假设 foo.f1 是一个类型为 text 的字段，那么

INSERT INTO foo (f1) VALUES (42);

如果从类型 integer 到类型 text 的转换标记为 AS ASSIGNMENT， 上面的这句就被允许，否则就不允许。（我们通常用术语赋值转换来描述这种转换。）

如果转换标记为 AS IMPLICIT，那么它就可以在任何环境里调用， 不管是赋值还是在表达式的内部。比如，因为 || 接受 text 操作数，

SELECT 'The time is ' || now();

将只有在类型 timestamp 到 text 的转换标记为 AS IMPLICIT 的时候才允许。否则我们就必须明确书写转换， 比如

SELECT 'The time is ' || CAST(now() AS text);

（我们通常使用术语隐含转换来描述这种类型的转换。）

在标记转换为隐含的这个问题上保守一些是明智的。 过于丰富的隐含转换路径会导致 PostgreSQL 选择让人奇怪的命令的解析， 或者是完全不能解析命令，因为存在多个可能的解析。 一条好的拇指定律是，只有在同一个通用类型表里面的那些可以保留转换信息的类型之间才标记为可隐含调用转换。 比如，从 int2 到 int4 可以合理地标记为隐含转换，但是从 float8 到 int4 可能应该是标记为赋值转换。跨类型表的转换，比如 text 到 int4，最好是只能明确地转换。

要想创建一个转换，你必须拥有源或者目的数据类型。要创建一个二进制兼容的转换， 你必须是超级用户。（做这个限制是因为一种有问题的二进制兼容转换可以很容易摧毁服务器。）  

PARAMETERS 参数

sourcetype

转换的源数据类型。

targettype

转换的目标数据类型。

funcname(argtype)

用于执行转换的函数。这个函数名可以是用模式名修饰的。 如果它没有用模式名修饰，那么该函数将从路径中找出来。 参数类型必须和源数据类型相同，结果数据类型必须匹配转换的目标类型。

WITHOUT FUNCTION

表示源数据类型和目标数据类型是二进制兼容的， 所以不需要什么函数来执行转换。

AS ASSIGNMENT

表示转换可以在赋值环境里隐含调用。

AS IMPLICIT

表示这个转换可以在任何环境里隐含调用。

NOTES 注意

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

请注意，如果你想能双向转换类型，那么你需要明确地定义两个方向的转换。

在 PostgreSQL 7.3 之前，如果一个函数的名字和一个数据类型相同， 并且返回该种数据类型，而且还接受另外一种类型的参数自动就是一个转换函数。 这个传统随着模式的引入以及为了能在系统表种表示二进制兼容的转换就被废弃了。 （内置的转换函数仍然遵循这个命名规则，但是它们现在必须在系统表 pg_cast 里显示为转换。）  

EXAMPLES 例子

要使用函数 int4(text) 创建一个从类型 text 到类型 int4的转换：

CREATE CAST (text AS int4) WITH FUNCTION int4(text);

（这个转换在系统中已经预先定义了。）  

COMPATIBILITY 兼容性

CREATE CAST 命令遵循 SQL99，只不过 SQL99 没有提供二进制兼容类型。AS IMPLICIT 也是 PostgreSQL 的扩展。  

SEE ALSO 参见

CREATE FUNCTION [create_function(7)], CREATE TYPE [create_type(7)], DROP CAST [drop_cast(7)]  

#p#

NAME

CREATE CAST - define a new cast

SYNOPSIS

CREATE CAST (sourcetype AS targettype)
    WITH FUNCTION funcname (argtype)
    [ AS ASSIGNMENT | AS IMPLICIT ]

CREATE CAST (sourcetype AS targettype)
    WITHOUT FUNCTION
    [ AS ASSIGNMENT | AS IMPLICIT ]

DESCRIPTION

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

SELECT CAST(42 AS text);

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

Two types may be binary compatible, which means that they can be converted into one another ``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 compatible.

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

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

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

If the cast is marked AS IMPLICIT then it can be invoked implicitly in any context, whether assignment or internally in an expression. For example, since || takes text operands,

SELECT 'The time is ' || now();

will be allowed only if the cast from type timestamp to text is marked AS IMPLICIT. Otherwise it will be necessary to write the cast explicitly, for example

SELECT 'The time is ' || CAST(now() AS text);

(We generally use the term implicit cast to describe this kind of cast.)

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.

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

PARAMETERS

sourcetype

The name of the source data type of the cast.

targettype

The name of the target data type of the cast.

funcname(argtype)

The function used to perform the cast. The function name may be schema-qualified. If it is not, the function will be looked up in the path. The argument type must be identical to the source type, the result data type must match the target type of the cast.

WITHOUT FUNCTION

Indicates that the source type and the target type are binary compatible, so no function is required to perform the cast.

AS ASSIGNMENT

Indicates that the cast may be invoked implicitly in assignment contexts.

AS IMPLICIT

Indicates that the cast may be invoked implicitly in any context.

NOTES

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.

Prior to PostgreSQL 7.3, every function that had the same name as a data type, returned that data type, and took one argument of a different type was automatically a cast function. This convention has been abandoned in face of the introduction of schemas and to be able to represent binary compatible casts in the system catalogs. (The built-in cast functions still follow this naming scheme, but they have to be shown as casts in the system catalog pg_cast now.)  

EXAMPLES

To create a cast from type text to type int4 using the function int4(text):

CREATE CAST (text AS int4) WITH FUNCTION int4(text);

(This cast is already predefined in the system.)  

COMPATIBILITY

The CREATE CAST command conforms to SQL99, except that SQL99 does not make provisions for binary-compatible types. AS IMPLICIT is a PostgreSQL extension, too.  

SEE ALSO

CREATE FUNCTION [create_function(7)], CREATE TYPE [create_type(7)], DROP CAST [drop_cast(7)]