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