NAME

CREATE TYPE - 定義一個(gè)新的數(shù)據(jù)類型

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 為當(dāng)前數(shù)據(jù)庫(kù)注冊(cè)一個(gè)新的數(shù)據(jù)類型。 定義該類型的用戶成為其所有者。

 如果給出模式名，那么該類型是在指定模式中創(chuàng)建。 否則它是在當(dāng)前模式中創(chuàng)建。類型名必需和同一模式中任何現(xiàn)有的類型或者域不同。 （因?yàn)楸砗蛿?shù)據(jù)類型有聯(lián)系，類型名不能和同模式中的表名字沖突。）  

COMPOSITE TYPES 復(fù)合類型

 ***種形式的 CREATE TYPE 創(chuàng)建一個(gè)復(fù)合類型。 復(fù)合類型是通過(guò)一列屬性名和數(shù)據(jù)類型聲明的。這樣實(shí)際上和一個(gè)表的行類型一樣， 但是如果我們只是想定義一個(gè)類型，那么使用 CREATE TYPE 避免了直接創(chuàng)建實(shí)際的表。 一個(gè)獨(dú)立的復(fù)合類型對(duì)于一個(gè)函數(shù)的返回類型非常有用。  

BASE TYPES 基本類型

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

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

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

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

 盡管新類型的內(nèi)部表現(xiàn)形式只有 I/O 函數(shù)和其它你創(chuàng)建來(lái)使用該類型的函數(shù)了解， 但內(nèi)部表現(xiàn)還是有幾個(gè)屬性必須為 PostgreSQL 聲明。 這些中最重要的是 internallength。 基本數(shù)據(jù)類型可定義成為定長(zhǎng)，這時(shí) internallength  是一個(gè)正整數(shù)，也可以是變長(zhǎng)的，通過(guò)把 internallength  設(shè)置為 VARIABLE 表示。（在內(nèi)部，這個(gè)狀態(tài) 是通過(guò)將typlen設(shè)置為 -1 實(shí)現(xiàn)的。）所有變長(zhǎng)類型的內(nèi)部形式都必須以一個(gè)四字節(jié)整數(shù)開(kāi)頭，這個(gè)整數(shù)給出此類型這個(gè)數(shù)值的全長(zhǎng)。

 可選的標(biāo)記 PASSEDBYVALUE 表明該類型的數(shù)值是用值傳遞的， 而不是用引用。你不能傳遞那些內(nèi)部形式大于 Datum （大多數(shù)機(jī)器上是 4 字節(jié)，有些是 8 字節(jié)）類型的尺寸的數(shù)據(jù)類型的值。

alignment 參數(shù)聲明該數(shù)據(jù)類型要求的對(duì)齊存儲(chǔ)方式。 允許的數(shù)值等效于按照 1，2，4，或者 8 字節(jié)邊界對(duì)齊。請(qǐng)注意變長(zhǎng)類型必須有至少 4 字節(jié)的對(duì)齊， 因?yàn)樗鼈儽仨毎粋€(gè) int4 作為它們的***個(gè)成份。

storage 參數(shù)允許為變長(zhǎng)數(shù)據(jù)類型選擇存儲(chǔ)策略。 （定長(zhǎng)類型只允許使用 plain）。 plain 聲明該數(shù)據(jù)類型總是用內(nèi)聯(lián)的方式而不是壓縮的方式存儲(chǔ)。 extended 聲明系統(tǒng)將首先試圖壓縮一個(gè)長(zhǎng)的數(shù)據(jù)值，然后如果它仍然太長(zhǎng)的話就將它的值移出主表的行， 但系統(tǒng)將不會(huì)壓縮它。 main 允許壓縮，但是不贊成把數(shù)值移動(dòng)出主表。 （用這種存儲(chǔ)策略的數(shù)據(jù)項(xiàng)可能仍將移動(dòng)出主表，如果不能放在一行里的話， 但是它們將比 extended 和 external 項(xiàng)更愿意呆在主表里。）

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

 要表示一個(gè)類型是數(shù)組，用 ELEMENT 關(guān)鍵字聲明數(shù)組元素的類型。 比如，要定義一個(gè) 4 字節(jié)整數(shù)(int4)的數(shù)組，聲明
  ELEMENT = int4

。 有關(guān)數(shù)組類型的更多細(xì)節(jié)在下面描述。

 要聲明用于這種類型數(shù)組的外部形式的數(shù)值之間的分隔符，可用 delimiter  聲明指定分隔符。缺省的分隔符是逗號(hào)（,）。 請(qǐng)注意分隔符是和數(shù)組元素類型相關(guān)聯(lián)，而不是數(shù)組類型本身。  

ARRAY TYPES 數(shù)組類型

 在創(chuàng)建用戶定義數(shù)據(jù)類型的時(shí)候，PostgreSQL  自動(dòng)創(chuàng)建一個(gè)與之關(guān)聯(lián)的數(shù)組類型，其名字由該基本類型的名字前綴一個(gè)下劃線組成。 分析器理解這個(gè)命名傳統(tǒng)，并且把對(duì)類型為 foo[] 的字段的請(qǐng)求轉(zhuǎn)換成對(duì)類型為 _foo  的字段的請(qǐng)求。這個(gè)隱含創(chuàng)建的數(shù)組類型是變長(zhǎng)并且使用內(nèi)建的輸入和輸出函數(shù) array_in 和 array_out。

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

PARAMETERS 參數(shù)

name

 將要?jiǎng)?chuàng)建的類型名（可以有模式修飾）。

attribute_name

 復(fù)合類型的一個(gè)屬性（字段）的名字。

data_type

 一個(gè)要成為一個(gè)復(fù)合類型的字段的現(xiàn)有數(shù)據(jù)類型的名字。

input_function

 一個(gè)函數(shù)的名稱， 將數(shù)據(jù)從外部類型轉(zhuǎn)換成內(nèi)部類型。

output_function

 一個(gè)函數(shù)的名稱， 將數(shù)據(jù)從內(nèi)部格式轉(zhuǎn)換成適于顯示的形式。

receive_function

 把數(shù)據(jù)從類型的外部二進(jìn)制形式轉(zhuǎn)換成其內(nèi)部形式的函數(shù)的名字。

send_function

 把數(shù)據(jù)從類型的內(nèi)部形式轉(zhuǎn)換成其外部二進(jìn)制形式的函數(shù)名。

internallength

 一個(gè)數(shù)值常量，說(shuō)明新類型的內(nèi)部表現(xiàn)形式的長(zhǎng)度。缺省的假設(shè)是它是變長(zhǎng)的。

alignment

 該數(shù)據(jù)類型的存儲(chǔ)對(duì)齊要求。如果聲明了，必須是 char， int2， int4 或 double； 缺省是 int4。

storage

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

default

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

element

 被創(chuàng)建的類型是數(shù)組；這個(gè)聲明數(shù)組元素的類型。

delimiter

 將用做數(shù)組的數(shù)據(jù)元素之間分隔符的字符。

NOTES 注意

 用戶定義類型名不能以下劃線（_） 開(kāi)頭而且只能有 62 個(gè)字符長(zhǎng)。（或者通常是 NAMEDATALEN-2， 而不是其它名字那樣的可以有 NAMEDATALEN-1 個(gè)字符）。 以下劃線開(kāi)頭的類型名被解析成內(nèi)部創(chuàng)建的數(shù)組類型名。

 在 PostgreSQL 版本 7.3 以前，我們要通過(guò)使用占位偽類型 opaque 代替函數(shù)的前向引用來(lái)避免創(chuàng)建殼類型。 7.3 之前 cstring 參數(shù)和結(jié)果同樣需要聲明偽 opaque。 要支持裝載舊的轉(zhuǎn)儲(chǔ)外那間，CREATE TYPE 將接受那些用 opaque聲明的函數(shù)， 但是它回發(fā)出一條通知并且用正確的類型改變函數(shù)的聲明。  

EXAMPLES 例子

 這個(gè)例子創(chuàng)建一個(gè)復(fù)合類型并且在一個(gè)函數(shù)定義中使用它：

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

 這個(gè)命令創(chuàng)建box數(shù)據(jù)類型，并且將這種類型用于一個(gè)表定義：

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

CREATE TABLE myboxes (
    id integer,
    description box
);

 如果 box 的內(nèi)部結(jié)構(gòu)是一個(gè)四個(gè) float4 的數(shù)組，我們可以說(shuō)

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

 它允許一個(gè) box 的數(shù)值成分成員可以用腳標(biāo)訪問(wèn)。 否則該類型和前面的行為一樣。

 這條命令創(chuàng)建一個(gè)大對(duì)象類型并將其用于一個(gè)表定義：

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

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

COMPATIBILITY 兼容性

CREATE TYPE 命令是 PostgreSQL 擴(kuò)展。在 SQL99 里有一個(gè) CREATE TYPE 語(yǔ)句，但是細(xì)節(jié)上和 PostgreSQL 的有比較大區(qū)別。  

SEE ALSO 參見(jiàn)

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