NAME

CREATE CAST - 定義一個(gè)用戶定義的轉(zhuǎn)換

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 定義一個(gè)新的轉(zhuǎn)換。 一個(gè)轉(zhuǎn)換說(shuō)明如何在兩個(gè)類型之間進(jìn)行轉(zhuǎn)換。比如：

SELECT CAST(42 AS text);

通過(guò)調(diào)用前面聲明的一個(gè)函數(shù)，把整數(shù)常量 42 轉(zhuǎn)換成類型 text， 在這個(gè)例子里是 text(int4)。（如果沒(méi)有預(yù)先定義好合適的轉(zhuǎn)換，那么這個(gè)轉(zhuǎn)換失敗。）

兩種類型可以是二進(jìn)制兼容的， 意思是它們之間可以"自由轉(zhuǎn)換"而不用調(diào)用任何函數(shù)。 這就需要那個(gè)對(duì)應(yīng)的數(shù)值使用同樣的內(nèi)部表現(xiàn)形式。 比如，類型 text 和 varchar 是二進(jìn)制兼容的。

缺省時(shí)，只有在明確要求轉(zhuǎn)換的情況下才調(diào)用一個(gè)轉(zhuǎn)換， 也就是一個(gè)明確的 CAST(x AS typename)， x::typename，或者 typename(x) 構(gòu)造。

如果轉(zhuǎn)換標(biāo)記為 AS ASSIGNMENT，那么在賦一個(gè)數(shù)值給目標(biāo)數(shù)據(jù)類型的字段的時(shí)候， 可以隱含調(diào)用它。比如，假設(shè) foo.f1 是一個(gè)類型為 text 的字段，那么

INSERT INTO foo (f1) VALUES (42);

如果從類型 integer 到類型 text 的轉(zhuǎn)換標(biāo)記為 AS ASSIGNMENT， 上面的這句就被允許，否則就不允許。（我們通常用術(shù)語(yǔ)賦值轉(zhuǎn)換來(lái)描述這種轉(zhuǎn)換。）

如果轉(zhuǎn)換標(biāo)記為 AS IMPLICIT，那么它就可以在任何環(huán)境里調(diào)用， 不管是賦值還是在表達(dá)式的內(nèi)部。比如，因?yàn)?|| 接受 text 操作數(shù)，

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

將只有在類型 timestamp 到 text 的轉(zhuǎn)換標(biāo)記為 AS IMPLICIT 的時(shí)候才允許。否則我們就必須明確書寫轉(zhuǎn)換， 比如

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

（我們通常使用術(shù)語(yǔ)隱含轉(zhuǎn)換來(lái)描述這種類型的轉(zhuǎn)換。）

在標(biāo)記轉(zhuǎn)換為隱含的這個(gè)問(wèn)題上保守一些是明智的。 過(guò)于豐富的隱含轉(zhuǎn)換路徑會(huì)導(dǎo)致 PostgreSQL 選擇讓人奇怪的命令的解析， 或者是完全不能解析命令，因?yàn)榇嬖诙鄠€(gè)可能的解析。 一條好的拇指定律是，只有在同一個(gè)通用類型表里面的那些可以保留轉(zhuǎn)換信息的類型之間才標(biāo)記為可隱含調(diào)用轉(zhuǎn)換。 比如，從 int2 到 int4 可以合理地標(biāo)記為隱含轉(zhuǎn)換，但是從 float8 到 int4 可能應(yīng)該是標(biāo)記為賦值轉(zhuǎn)換?？珙愋捅淼霓D(zhuǎn)換，比如 text 到 int4，最好是只能明確地轉(zhuǎn)換。

要想創(chuàng)建一個(gè)轉(zhuǎn)換，你必須擁有源或者目的數(shù)據(jù)類型。要?jiǎng)?chuàng)建一個(gè)二進(jìn)制兼容的轉(zhuǎn)換， 你必須是超級(jí)用戶。（做這個(gè)限制是因?yàn)橐环N有問(wèn)題的二進(jìn)制兼容轉(zhuǎn)換可以很容易摧毀服務(wù)器。）  

PARAMETERS 參數(shù)

sourcetype

轉(zhuǎn)換的源數(shù)據(jù)類型。

targettype

轉(zhuǎn)換的目標(biāo)數(shù)據(jù)類型。

funcname(argtype)

用于執(zhí)行轉(zhuǎn)換的函數(shù)。這個(gè)函數(shù)名可以是用模式名修飾的。 如果它沒(méi)有用模式名修飾，那么該函數(shù)將從路徑中找出來(lái)。 參數(shù)類型必須和源數(shù)據(jù)類型相同，結(jié)果數(shù)據(jù)類型必須匹配轉(zhuǎn)換的目標(biāo)類型。

WITHOUT FUNCTION

表示源數(shù)據(jù)類型和目標(biāo)數(shù)據(jù)類型是二進(jìn)制兼容的， 所以不需要什么函數(shù)來(lái)執(zhí)行轉(zhuǎn)換。

AS ASSIGNMENT

表示轉(zhuǎn)換可以在賦值環(huán)境里隱含調(diào)用。

AS IMPLICIT

表示這個(gè)轉(zhuǎn)換可以在任何環(huán)境里隱含調(diào)用。

NOTES 注意

用 DROP CAST 刪除用戶定義的轉(zhuǎn)換。

請(qǐng)注意，如果你想能雙向轉(zhuǎn)換類型，那么你需要明確地定義兩個(gè)方向的轉(zhuǎn)換。

在 PostgreSQL 7.3 之前，如果一個(gè)函數(shù)的名字和一個(gè)數(shù)據(jù)類型相同， 并且返回該種數(shù)據(jù)類型，而且還接受另外一種類型的參數(shù)自動(dòng)就是一個(gè)轉(zhuǎn)換函數(shù)。 這個(gè)傳統(tǒng)隨著模式的引入以及為了能在系統(tǒng)表種表示二進(jìn)制兼容的轉(zhuǎn)換就被廢棄了。 （內(nèi)置的轉(zhuǎn)換函數(shù)仍然遵循這個(gè)命名規(guī)則，但是它們現(xiàn)在必須在系統(tǒng)表 pg_cast 里顯示為轉(zhuǎn)換。）  

EXAMPLES 例子

要使用函數(shù) int4(text) 創(chuàng)建一個(gè)從類型 text 到類型 int4的轉(zhuǎn)換：

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

（這個(gè)轉(zhuǎn)換在系統(tǒng)中已經(jīng)預(yù)先定義了。）  

COMPATIBILITY 兼容性

CREATE CAST 命令遵循 SQL99，只不過(guò) SQL99 沒(méi)有提供二進(jìn)制兼容類型。AS IMPLICIT 也是 PostgreSQL 的擴(kuò)展。  

SEE ALSO 參見(jiàn)

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