NAME

CREATE TYPE - 定義一個新的數據類型

SYNOPSIS

CREATE TYPE name AS
    ( attribute_name data_type [, ... ] )

CREATE TYPE name (
    INPUT = input_function,
    OUTPUT = output_function
    [ , RECEIVE = receive_function ]
    [ , SEND = send_function ]
    [ , INTERNALLENGTH = { internallength | VARIABLE } ]
    [ , PASSEDBYVALUE ]
    [ , ALIGNMENT = alignment ]
    [ , STORAGE = storage ]
    [ , DEFAULT = default ]
    [ , ELEMENT = element ]
    [ , DELIMITER = delimiter ]
)

DESCRIPTION 描述

CREATE TYPE 為當前數據庫注冊一個新的數據類型。 定義該類型的用戶成為其所有者。

 如果給出模式名，那么該類型是在指定模式中創建。 否則它是在當前模式中創建。類型名必需和同一模式中任何現有的類型或者域不同。 （因為表和數據類型有聯系，類型名不能和同模式中的表名字沖突。）  

COMPOSITE TYPES 復合類型

 ***種形式的 CREATE TYPE 創建一個復合類型。 復合類型是通過一列屬性名和數據類型聲明的。這樣實際上和一個表的行類型一樣， 但是如果我們只是想定義一個類型，那么使用 CREATE TYPE 避免了直接創建實際的表。 一個獨立的復合類型對于一個函數的返回類型非常有用。  

BASE TYPES 基本類型

 第二種形式的CREATE TYPE創建一種新的基本類型（標量類型）。 參數可以以任意的順序出現，而不是上面顯示的那樣。并且大多數都是可選的。 它要求要在定義類型之前先注冊兩個函數（用CREATE FUNCTION命令）。 支持函數 input_function 和 output_function 是必須的， 而函數 receive_function 和 send_function 是可選的。 通常，這些函數必須用 C 或者其它低層語言編寫。

 函數 input_function  將該類型的外部文本形式轉換成可以被對該類型操作的操作符和函數識別的內部形式。 output_function  用做相反用途。 輸入函數可以聲明為接受一個類型為 c_string 的參數，或者接受三個類型分別為 c_string，oid，integer 的參數。 （***個參數是 C 字串形式的輸入文本，第二個是在該類型為數組類型時其元素的類型， 第三個是目標字段的typmod，如果已知的話。） 它應該返回一個該數據類型本身的數值。 輸出函數可以聲明為接受一個類型為新數據類型的參數， 或者接受兩個類型，第二個參數的類型是 oid。 第二個參數也是用于數組類型的數組元素類型。輸出函數應該返回類型 cstring。

 可選的 receive_function 把該類型的外部二進制表現形式轉換成內部表現形式。 如果沒有提供這個函數，那么該類型不能用二進制輸入。二進制格式應該選取那種轉換成內部格式比較容易的，同時還有一定移植性的。 （比如，標準的整數數據類型使用網絡字節序作為外部的二進制表現形式，而內部表現形式是機器的本機字節序。） 接收函數應該聲明為接受一個類型為 internal 的參數，或者兩個類型分別為 internal 和 oid 的參數。 它必須返回一個數據類型自身的數值。（***個參數是一個指向一個 StringInfo 緩沖區的，保存接受字節串的指針； 可選的第二個參數是元素類型——如果類型是一個數組類型的話。）類似的，可選的 send_function  把類型轉換為外部二進制表現形式。 如果沒有提供這些函數，那么類型就不能用二進制方式輸出。發送函數可以聲明為接收一個新數據類型， 或者接收兩個參數，第二個參數的類型是 oid。第二個參數仍然是用做數組類型的。 發送函數必須返回 bytea。

 這個時候你應該覺得奇怪，就是輸入和輸出函數怎么可以聲明為返回新類型的結果或者是接受新類型的參數， 而且是在新類型創建之前就需要創建它們。 答案是輸入函數必須先創建，然后是輸出函數，***是數據類型。 PostgreSQL 將首先把新數據類型的名字看作輸入函數的返回類型。 它將創建一個"殼"類型，這個類型只是在 pg_type里面的一個占位符，然后把輸入函數定義和這個殼類型連接起來。 類似的是輸出函數將連接到（現在已經存在）的殼類型。***， CREATE TYPE 把這個殼類型替換成完整的類型定義，這樣就可以使用新類型了。

 盡管新類型的內部表現形式只有 I/O 函數和其它你創建來使用該類型的函數了解， 但內部表現還是有幾個屬性必須為 PostgreSQL 聲明。 這些中最重要的是 internallength。 基本數據類型可定義成為定長，這時 internallength  是一個正整數，也可以是變長的，通過把 internallength  設置為 VARIABLE 表示。（在內部，這個狀態 是通過將typlen設置為 -1 實現的。）所有變長類型的內部形式都必須以一個四字節整數開頭，這個整數給出此類型這個數值的全長。

 可選的標記 PASSEDBYVALUE 表明該類型的數值是用值傳遞的， 而不是用引用。你不能傳遞那些內部形式大于 Datum （大多數機器上是 4 字節，有些是 8 字節）類型的尺寸的數據類型的值。

alignment 參數聲明該數據類型要求的對齊存儲方式。 允許的數值等效于按照 1，2，4，或者 8 字節邊界對齊。請注意變長類型必須有至少 4 字節的對齊， 因為它們必須包含一個 int4 作為它們的***個成份。

storage 參數允許為變長數據類型選擇存儲策略。 （定長類型只允許使用 plain）。 plain 聲明該數據類型總是用內聯的方式而不是壓縮的方式存儲。 extended 聲明系統將首先試圖壓縮一個長的數據值，然后如果它仍然太長的話就將它的值移出主表的行， 但系統將不會壓縮它。 main 允許壓縮，但是不贊成把數值移動出主表。 （用這種存儲策略的數據項可能仍將移動出主表，如果不能放在一行里的話， 但是它們將比 extended 和 external 項更愿意呆在主表里。）

 如果用戶希望字段的數據類型缺省時不是 NULL，而是其它什么東西， 那么你可以聲明一個缺省值。 在 DEFAULT 關鍵字里面聲明缺省值。 （這樣的缺省可以被附著在特定字段上的明確的 DEFAULT 子句覆蓋。）

 要表示一個類型是數組，用 ELEMENT 關鍵字聲明數組元素的類型。 比如，要定義一個 4 字節整數(int4)的數組，聲明
  ELEMENT = int4

。 有關數組類型的更多細節在下面描述。

 要聲明用于這種類型數組的外部形式的數值之間的分隔符，可用 delimiter  聲明指定分隔符。缺省的分隔符是逗號（,）。 請注意分隔符是和數組元素類型相關聯，而不是數組類型本身。  

ARRAY TYPES 數組類型

 在創建用戶定義數據類型的時候，PostgreSQL  自動創建一個與之關聯的數組類型，其名字由該基本類型的名字前綴一個下劃線組成。 分析器理解這個命名傳統，并且把對類型為 foo[] 的字段的請求轉換成對類型為 _foo  的字段的請求。這個隱含創建的數組類型是變長并且使用內建的輸入和輸出函數 array_in 和 array_out。

 你很可能會問如果系統自動制作正確的數組類型，那為什么有個 ELEMENT選項？使用 ELEMENT 有用的唯一的場合是在你制作的定長類型碰巧在內部是一個一定數目相同事物的數組， 而你又想允許這 N 個事物可以通過腳標直接關聯，以及那些你準備把該類型當做整體進行的操作。 比如，類型 name 就允許其構成 char 用這種方法關聯。 一個二維的 point 類型也可以允許其兩個構成浮點型按照類似 point[0] 和 point[1] 的方法關聯。  請注意這個功能只適用與那些內部形式是一個相同的定長域的序列的類型。 一個可以腳標化的變長類型必須有被 array_in 和 array_out 使用的一般化的內部表現形式。 出于歷史原因（也就是說，那些明顯錯誤但補救來得太遲的問題），定長數組類型的腳標從零開始，而不是象變長類型那樣的從一開始。  

PARAMETERS 參數

name

 將要創建的類型名（可以有模式修飾）。

attribute_name

 復合類型的一個屬性（字段）的名字。

data_type

 一個要成為一個復合類型的字段的現有數據類型的名字。

input_function

 一個函數的名稱， 將數據從外部類型轉換成內部類型。

output_function

 一個函數的名稱， 將數據從內部格式轉換成適于顯示的形式。

receive_function

 把數據從類型的外部二進制形式轉換成其內部形式的函數的名字。

send_function

 把數據從類型的內部形式轉換成其外部二進制形式的函數名。

internallength

 一個數值常量，說明新類型的內部表現形式的長度。缺省的假設是它是變長的。

alignment

 該數據類型的存儲對齊要求。如果聲明了，必須是 char， int2， int4 或 double； 缺省是 int4。

storage

 該數據類型的存儲策略。如果聲明了，必須是 plain，external， extended，或 main； 缺省是 plain。

default

 該類型的缺省值。通常是省略它的，所以缺省是 NULL。

element

 被創建的類型是數組；這個聲明數組元素的類型。

delimiter

 將用做數組的數據元素之間分隔符的字符。

NOTES 注意

 用戶定義類型名不能以下劃線（_） 開頭而且只能有 62 個字符長。（或者通常是 NAMEDATALEN-2， 而不是其它名字那樣的可以有 NAMEDATALEN-1 個字符）。 以下劃線開頭的類型名被解析成內部創建的數組類型名。

 在 PostgreSQL 版本 7.3 以前，我們要通過使用占位偽類型 opaque 代替函數的前向引用來避免創建殼類型。 7.3 之前 cstring 參數和結果同樣需要聲明偽 opaque。 要支持裝載舊的轉儲外那間，CREATE TYPE 將接受那些用 opaque聲明的函數， 但是它回發出一條通知并且用正確的類型改變函數的聲明。  

EXAMPLES 例子

 這個例子創建一個復合類型并且在一個函數定義中使用它：

CREATE TYPE compfoo AS (f1 int, f2 text);
CREATE FUNCTION getfoo() RETURNS SETOF compfoo AS
  'SELECT fooid, fooname FROM foo' LANGUAGE SQL;

 這個命令創建box數據類型，并且將這種類型用于一個表定義：

CREATE TYPE box (
    INTERNALLENGTH = 16,
    INPUT = my_box_in_function,
    OUTPUT = my_box_out_function
);

CREATE TABLE myboxes (
    id integer,
    description box
);

 如果 box 的內部結構是一個四個 float4 的數組，我們可以說

CREATE TYPE box (
    INTERNALLENGTH = 16,
    INPUT = my_box_in_function,
    OUTPUT = my_box_out_function,
    ELEMENT = float4
);

 它允許一個 box 的數值成分成員可以用腳標訪問。 否則該類型和前面的行為一樣。

 這條命令創建一個大對象類型并將其用于一個表定義：

CREATE TYPE bigobj (
    INPUT = lo_filein, OUTPUT = lo_fileout,
    INTERNALLENGTH = VARIABLE
);
CREATE TABLE big_objs (
    id integer,
    obj bigobj
);

 更多的例子，包括合適的輸入和輸出函數，在 Chapter 31``Extending SQL'' in the documentation。  

COMPATIBILITY 兼容性

CREATE TYPE 命令是 PostgreSQL 擴展。在 SQL99 里有一個 CREATE TYPE 語句，但是細節上和 PostgreSQL 的有比較大區別。  

SEE ALSO 參見

CREATE FUNCTION [create_function(7)], DROP TYPE [drop_type(l)]  

#p#

NAME

CREATE TYPE - define a new data type

SYNOPSIS

CREATE TYPE name AS
    ( attribute_name data_type [, ... ] )

CREATE TYPE name (
    INPUT = input_function,
    OUTPUT = output_function
    [ , RECEIVE = receive_function ]
    [ , SEND = send_function ]
    [ , INTERNALLENGTH = { internallength | VARIABLE } ]
    [ , PASSEDBYVALUE ]
    [ , ALIGNMENT = alignment ]
    [ , STORAGE = storage ]
    [ , DEFAULT = default ]
    [ , ELEMENT = element ]
    [ , DELIMITER = delimiter ]
)

DESCRIPTION

CREATE TYPE registers a new data type for use in the current data base. The user who defines a type becomes its owner.

If a schema name is given then the type is created in the specified schema. Otherwise it is created in the current schema. The type name must be distinct from the name of any existing type or domain in the same schema. (Because tables have associated data types, the type name must also be distinct from the name of any existing table in the same schema.)  

COMPOSITE TYPES

The first form of CREATE TYPE creates a composite type. The composite type is specified by a list of attribute names and data types. This is essentially the same as the row type of a table, but using CREATE TYPE avoids the need to create an actual table when all that is wanted is to define a type. A stand-alone composite type is useful as the return type of a function.  

BASE TYPES

The second form of CREATE TYPE creates a new base type (scalar type). The parameters may appear in any order, not only that illustrated above, and most are optional. You must register two or more functions (using CREATE FUNCTION) before defining the type. The support functions input_function and output_function are required, while the functions receive_function and send_function are optional. Generally these functions have to be coded in C or another low-level language.

The input_function converts the type's external textual representation to the internal representation used by the operators and functions defined for the type. output_function performs the reverse transformation. The input function may be declared as taking one argument of type cstring, or as taking three arguments of types cstring, oid, integer. The first argument is the input text as a C string, the second argument is the element type in case this is an array type, and the third is the typmod of the destination column, if known. The input function should return a value of the data type itself. The output function may be declared as taking one argument of the new data type, or as taking two arguments of which the second is type oid. The second argument is again the array element type for array types. The output function should return type cstring.

The optional receive_function converts the type's external binary representation to the internal representation. If this function is not supplied, the type cannot participate in binary input. The binary representation should be chosen to be cheap to convert to internal form, while being reasonably portable. (For example, the standard integer data types use network byte order as the external binary representation, while the internal representation is in the machine's native byte order.) The receive function should perform adequate checking to ensure that the value is valid. The receive function may be declared as taking one argument of type internal, or two arguments of types internal and oid. It must return a value of the data type itself. (The first argument is a pointer to a StringInfo buffer holding the received byte string; the optional second argument is the element type in case this is an array type.) Similarly, the optional send_function converts from the internal representation to the external binary representation. If this function is not supplied, the type cannot participate in binary output. The send function may be declared as taking one argument of the new data type, or as taking two arguments of which the second is type oid. The second argument is again the array element type for array types. The send function must return type bytea.

You should at this point be wondering how the input and output functions can be declared to have results or arguments of the new type, when they have to be created before the new type can be created. The answer is that the input function must be created first, then the output function (and the binary I/O functions if wanted), and finally the data type. PostgreSQL will first see the name of the new data type as the return type of the input function. It will create a ``shell'' type, which is simply a placeholder entry in the system catalog, and link the input function definition to the shell type. Similarly the other functions will be linked to the (now already existing) shell type. Finally, CREATE TYPE replaces the shell entry with a complete type definition, and the new type can be used.

While the details of the new type's internal representation are only known to the I/O functions and other functions you create to work with the type, there are several properties of the internal representation that must be declared to PostgreSQL. Foremost of these is internallength. Base data types can be fixed-length, in which case internallength is a positive integer, or variable length, indicated by setting internallength to VARIABLE. (Internally, this is represented by setting typlen to -1.) The internal representation of all variable-length types must start with a 4-byte integer giving the total length of this value of the type.

The optional flag PASSEDBYVALUE indicates that values of this data type are passed by value, rather than by reference. You may not pass by value types whose internal representation is larger than the size of the Datum type (4 bytes on most machines, 8 bytes on a few).

The alignment parameter specifies the storage alignment required for the data type. The allowed values equate to alignment on 1, 2, 4, or 8 byte boundaries. Note that variable-length types must have an alignment of at least 4, since they necessarily contain an int4 as their first component.

The storage parameter allows selection of storage strategies for variable-length data types. (Only plain is allowed for fixed-length types.) plain specifies that data of the type will always be stored in-line and not compressed. extended specifies that the system will first try to compress a long data value, and will move the value out of the main table row if it's still too long. external allows the value to be moved out of the main table, but the system will not try to compress it. main allows compression, but discourages moving the value out of the main table. (Data items with this storage strategy may still be moved out of the main table if there is no other way to make a row fit, but they will be kept in the main table preferentially over extended and external items.)

A default value may be specified, in case a user wants columns of the data type to default to something other than the null value. Specify the default with the DEFAULT key word. (Such a default may be overridden by an explicit DEFAULT clause attached to a particular column.)

To indicate that a type is an array, specify the type of the array elements using the ELEMENT key word. For example, to define an array of 4-byte integers (int4), specify ELEMENT = int4. More details about array types appear below.

To indicate the delimiter to be used between values in the external representation of arrays of this type, delimiter can be set to a specific character. The default delimiter is the comma (,). Note that the delimiter is associated with the array element type, not the array type itself.  

ARRAY TYPES

Whenever a user-defined base data type is created, PostgreSQL automatically creates an associated array type, whose name consists of the base type's name prepended with an underscore. The parser understands this naming convention, and translates requests for columns of type foo[] into requests for type _foo. The implicitly-created array type is variable length and uses the built-in input and output functions array_in and array_out.

You might reasonably ask why there is an ELEMENT option, if the system makes the correct array type automatically. The only case where it's useful to use ELEMENT is when you are making a fixed-length type that happens to be internally an array of a number of identical things, and you want to allow these things to be accessed directly by subscripting, in addition to whatever operations you plan to provide for the type as a whole. For example, type name allows its constituent char elements to be accessed this way. A 2-D point type could allow its two component numbers to be accessed like point[0] and point[1]. Note that this facility only works for fixed-length types whose internal form is exactly a sequence of identical fixed-length fields. A subscriptable variable-length type must have the generalized internal representation used by array_in and array_out. For historical reasons (i.e., this is clearly wrong but it's far too late to change it), subscripting of fixed-length array types starts from zero, rather than from one as for variable-length arrays.  

PARAMETERS

name

The name (optionally schema-qualified) of a type to be created.

attribute_name

The name of an attribute (column) for the composite type.

data_type

The name of an existing data type to become a column of the composite type.

input_function

The name of a function that converts data from the type's external textual form to its internal form.

output_function

The name of a function that converts data from the type's internal form to its external textual form.

receive_function

The name of a function that converts data from the type's external binary form to its internal form.

send_function

The name of a function that converts data from the type's internal form to its external binary form.

internallength

A numeric constant that specifies the length in bytes of the new type's internal representation. The default assumption is that it is variable-length.

alignment

The storage alignment requirement of the data type. If specified, it must be char, int2, int4, or double; the default is int4.

storage

The storage strategy for the data type. If specified, must be plain, external, extended, or main; the default is plain.

default

The default value for the data type. If this is omitted, the default is null.

element

The type being created is an array; this specifies the type of the array elements.

delimiter

The delimiter character to be used between values in arrays made of this type.

NOTES

User-defined type names cannot begin with the underscore character (_) and can only be 62 characters long (or in general NAMEDATALEN - 2, rather than the NAMEDATALEN - 1 characters allowed for other names). Type names beginning with underscore are reserved for internally-created array type names.

In PostgreSQL versions before 7.3, it was customary to avoid creating a shell type by replacing the functions' forward references to the type name with the placeholder pseudotype opaque. The cstring arguments and results also had to be declared as opaque before 7.3. To support loading of old dump files, CREATE TYPE will accept functions declared using opaque, but it will issue a notice and change the function's declaration to use the correct types.  

EXAMPLES

This example creates a composite type and uses it in a function definition:

CREATE TYPE compfoo AS (f1 int, f2 text);
CREATE FUNCTION getfoo() RETURNS SETOF compfoo AS
  'SELECT fooid, fooname FROM foo' LANGUAGE SQL;

This example creates the base data type box and then uses the type in a table definition:

CREATE TYPE box (
    INTERNALLENGTH = 16,
    INPUT = my_box_in_function,
    OUTPUT = my_box_out_function
);

CREATE TABLE myboxes (
    id integer,
    description box
);

If the internal structure of box were an array of four float4 elements, we might instead use

CREATE TYPE box (
    INTERNALLENGTH = 16,
    INPUT = my_box_in_function,
    OUTPUT = my_box_out_function,
    ELEMENT = float4
);

which would allow a box value's component numbers to be accessed by subscripting. Otherwise the type behaves the same as before.

This example creates a large object type and uses it in a table definition:

CREATE TYPE bigobj (
    INPUT = lo_filein, OUTPUT = lo_fileout,
    INTERNALLENGTH = VARIABLE
);
CREATE TABLE big_objs (
    id integer,
    obj bigobj
);

More examples, including suitable input and output functions, are in the chapter called ``Extending SQL'' in the documentation.  

COMPATIBILITY

This CREATE TYPE command is a PostgreSQL extension. There is a CREATE TYPE statement in SQL99 that is rather different in detail.  

SEE ALSO

CREATE FUNCTION [create_function(7)], DROP TYPE [drop_type(l)]