PolarDB：CREATE TYPE

簡介

CREATE TYPE用於在當前資料庫中註冊一個新的使用者定義資料類型。執行此命令的使用者自動成為新資料類型的擁有者。該命令允許使用者為資料庫建立具有特定屬性和行為的自訂資料結構。

如果在執行CREATE TYPE命令時指定了一個模式名稱，那麼新建立的資料類型將位於該指定模式內。如果沒有指定模式名稱，則該資料類型將預設被建立在當前啟用的模式下。

CREATE TYPE命令有五種不同的變體。這五種變體分別用於建立如下類型：組合類別型（composite type）、枚舉類型（enumerated type）、範圍類型（range type）、基礎類型（base type），以及shell類型。本文將詳細討論前四種類型的建立方法。至於shell類型，它實質上是一個預留位置，用於預留一個名稱供將來定義的類型使用。這個預留位置可以通過發布一個CREATE TYPE命令並僅帶有類型名稱來建立，不需要其他參數。當建立範圍類型和基礎類型時，shell類型用作一種前向引用，這樣可以在實際定義類型的細節之前就預先聲明其存在。

文法

   CREATE [ OR REPLACE ] TYPE name AS
    ( [ attribute_name data_type [ COLLATE collation ] [, ... ] ] );

CREATE TYPE name AS ENUM
    ( [ 'label' [, ... ] ] );

CREATE TYPE name AS RANGE (
    SUBTYPE = subtype
    [ , SUBTYPE_OPCLASS = subtype_operator_class ]
    [ , COLLATION = collation ]
    [ , CANONICAL = canonical_function ]
    [ , SUBTYPE_DIFF = subtype_diff_function ]
);

CREATE TYPE name (
    INPUT = input_function,
    OUTPUT = output_function
    [ , RECEIVE = receive_function ]
    [ , SEND = send_function ]
    [ , TYPMOD_IN = type_modifier_input_function ]
    [ , TYPMOD_OUT = type_modifier_output_function ]
    [ , ANALYZE = analyze_function ]
    [ , INTERNALLENGTH = { internallength | VARIABLE } ]
    [ , PASSEDBYVALUE ]
    [ , ALIGNMENT = alignment ]
    [ , STORAGE = storage ]
    [ , LIKE = like_type ]
    [ , CATEGORY = category ]
    [ , PREFERRED = preferred ]
    [ , DEFAULT = default ]
    [ , ELEMENT = element ]
    [ , DELIMITER = delimiter ]
    [ , COLLATABLE = collatable ]
);

CREATE TYPE name;

組合類別型

使用CREATE TYPE命令的第一種形式可以建立組合類別型（composite types）。這種類型由一組屬性名稱組成，每個屬性名稱都與一個特定的資料類型相關聯。如果屬性的資料類型支援排序操作，則還可以為每個屬性指定一個定序。組合類別型在本質上與表結構的行類型相似，不過使用CREATE TYPE建立組合類別型可以避免實際上建立一個完整的表。

在建立組合類別型時，可以選擇性地使用OR REPLACE子句。當包含這個子句時，如果資料庫中已經存在一個與你想要建立的組合類別型同名的類型，那麼現有的類型將會被新定義的類型替換。這樣的功能允許你更新一個已存在的組合類別型的定義而不需要事先手動刪除舊的類型，從而簡化了類型維護的過程。

組合類別型可以用作函數的參數類型或傳回型別，提供一種定義複雜結構的便捷方式，而不必為此建立一張新的表。組合類別型的這種特性使得它在資料庫設計中非常靈活且實用，尤其是在需要傳遞多個值作為一個整體時，或者當函數需要返回多個值而不僅僅是一個單一資料型別的值時。

為了成功建立一個組合類別型，建立者必須具備在該組合類別型的所有屬性資料類型上的USAGE許可權。

枚舉類型

使用CREATE TYPE命令的第二種形式可以建立枚舉類型（enumerated type）。枚舉類型是由一系列預定義的標籤組成，這些標籤在建立時必須用引號包圍。每個標籤的長度不得超過NAMEDATALEN位元組的限制，通常是64位元組。

儘管可以建立一個不包含任何標籤的枚舉類型，但在對該類型使用ALTER TYPE命令並添加至少一個標籤之前，您將無法使用該枚舉類型儲存任何值。即一個空的枚舉類型在添加枚舉值之前，無法在實際資料庫操作中使用。該特性提供了枚舉類型的靈活擴充性，允許資料庫設計者在初始定義枚舉類型時不必立即確定所有可能的枚舉值，而是可以隨著應用需求的變化逐漸添加新的枚舉值。

範圍類型

使用CREATE TYPE命令的第三種形式可以建立範圍類型（range type）。範圍類型允許使用者表示一個連續區間，該區間內的值是基於某個子類型定義的。所謂的subtype是構成範圍的基礎資料型別 (Elementary Data Type)，它必須有對應的B樹操作符類來決定範圍值的順序。大多數情況下，子類型的預設B樹操作符類會被用作排序依據。若需使用非預設操作符類進行排序，可以通過subtype_opclass選項來指明其名稱。如果子類型是可排序的，並且需要應用非預設的定序於範圍類型值，可以用collation選項指定相應的定序。

建立範圍類型時，還可以定義一個可選的canonical函數，該函數接受一個範圍類型的參數並返回同類型的標準化數值。這個函數用來將範圍值轉換為其規範形式。然而，建立canonical函數有一定難度，因為它需要在範圍型別宣告前定義好。為了實現這一點，必須先建立一個所謂的shell類型，這是一種只有名稱和所有者而沒有具體屬性的預留位置類型，可以通過執行CREATE TYPE name命令（不帶其他參數）來建立。建立之後，可以使用該shell類型來聲明canonical函數的參數和傳回型別。最後，當聲明實際的範圍類型時用同樣的名稱，系統將自動將shell類型預留位置替換為完整定義的範圍類型。

另一個可選的功能是定義subtype_diff函數，它接受兩個子類型值作為參數，並返回一個double precision類型的值來表示兩個值之間的差異。雖然這不是必須的，但提供subtype_diff函數可以提高在該範圍類型的列上使用GiST索引的效率。

基礎類型

CREATE TYPE命令的第四種形式用於建立新的基礎類型，也稱為標量類型。建立一個新的基礎類型是一項高許可權操作，因此要求執行者必須是資料庫的超級使用者。這樣的許可權要求是為了安全考慮——如果類型定義不當，可能會導致伺服器出現錯誤行為乃至崩潰。

在定義新的基礎類型時，參數可以按任何順序出現，並不局限於在文法說明中展示的順序，而且其中大多數參數是可選的。但在定義這個類型之前，必須已經使用CREATE FUNCTION命令註冊了至少兩個函數。其中，input_function和output_function這兩個支援函數是建立新基礎類型時的必要條件。另外，receive_function、send_function、type_modifier_input_function、type_modifier_output_function和analyze_function這些函數則是可選的。

一般而言，這些函數需要使用C語言或其他低級語言編寫，因為他們必須能夠在底層與資料庫系統緊密配合，以處理資料類型的輸入、輸出、接收、發送和分析等操作。這些函數的編寫和註冊是建立新基礎類型過程中最為技術性的部分，他們確保了新類型能夠在資料庫系統中正常工作。

input_function是將使用者定義型別的外部文本表示轉換為資料庫內部使用的格式，這種內部格式適用於該類型定義的所有操作符和函數。output_function則執行相反的轉換過程，把內部格式轉換回可讀的文本表示。輸入函數可以接受一個單一的cstring型別參數，或者接受三個參數，類型分別為cstring、oid（物件識別碼）和integer。第一個參數是輸入文本的C字串形式，第二個參數是該類型本身的OID，或者對於數群組類型來說，是其元素類型的 OID；第三個參數是目標列的類型修飾符typmod（如果此資訊未知，則會傳遞值-1） 。輸入函數必須返回對應的新資料類型值。通常，輸入函數應聲明為STRICT，這意味著如果輸入為NULL，函數將不會被調用。如果輸入函數不是STRICT，在接收到NULL輸入值時，其第一個參數會是NULL。在這種情況下，函數應返回NULL，除非發生錯誤（這種設計主要是為了支援領域輸入函數，它們可能需要拒絕NULL輸入）。

輸出函數需要接受一個新資料類型的參數，並將其轉換為cstring類型的傳回值。如果值是NULL，則不會調用輸出函數。

可選的receive_function是處理類型的外部二進位表示，並將其轉換為內部格式的函數。如果不提供該函數，則該類型將無法進行二進位輸入。二進位表示形式通常更加高效並易於在不同系統之間移植。例如，標準整數資料類型使用網路位元組序作為其外部二進位表示，而內部格式則採用機器的本地位元組序。接收函數應當進行足夠的校正確保輸入值是有效，並且可以接受一個internal類型的參數，或者三個參數（internal、oid、integer），其中internal參數指向一個StringInfo緩衝區，該緩衝區包含接收到的位元組串。其餘參數與文本輸入函數一致。通常，接收函數也應聲明為STRICT。如果不是，那麼在接收到NULL輸入時，其第一個參數將會是NULL，並且函數應該返回NULL，除非它遇到錯誤。

類似的，可選的send_function將內部格式轉換為外部二進位表示。如果沒有提供該函數，該類型將無法進行二進位輸出。發送函數必須接受一個新資料類型的參數，並返回bytea類型的值。對於NULL值，發送函數不會被調用。這些函數為使用者定義型別提供了與內部資料庫系統的互動方式，確保類型能夠適應資料庫的儲存和通訊機制。

您可能對如何能夠在建立新類型之前聲明輸入和輸出函數這一過程感到疑惑，因為這些函數需要引用尚未建立的新類型作為傳回值或參數。解決該問題的方法是先定義一個shell type，也就是一種僅具有名稱和擁有者但沒有其他屬性的預留位置類型。這可以通過執行不攜帶額外參數的命令CREATE TYPE name來完成。之後，就可以用C語言編寫的輸入輸出函數引用該shell type。最終，使用帶有完整定義的CREATE TYPE命令來替換這個shell type，使其變成一個完整且合法的類型定義，隨後新類型就可以正常使用了。

如果該類型支援類型修飾符——即在型別宣告上附加的可選約束，例如char(5)或numeric(30,2)——則您還需要定義可選的type_modifier_input_function和type_modifier_output_function。PolarDB允許使用者定義型別接受一個或多個簡單常量或標識符作為修飾符。為了在系統目錄中儲存這些資訊，修飾符必須能夠被封裝進一個非負整數值內。類型修飾符由type_modifier_input_function接受，該函數以cstring數組形式接收聲明的修飾符，並必須檢查其合法性。如果修飾符無效，函數應該拋出錯誤；如果有效，則返回一個非負整數，該整數將儲存在typmod列中。如果類型沒有type_modifier_input_function，則任何類型修飾符都會被拒絕。

type_modifier_output_function則是將系統目錄中儲存的整數typmod值轉換回使用者可理解的格式。它必須返回一個cstring值，這個值是拼接到類型名稱後面的字串。例如，對於numeric類型，該函數可能會返回(30,2)。如果預設的顯示格式就是只將儲存的typmod整數值放在圓括弧內，則可以省略type_modifier_output_function。這些函數為使用者定義的類型提供了一種方式，以實現對類型修飾符的解析和顯示，從而在型別宣告中允許額外的自訂約束。

可選的analyze_function用於執行與特定資料類型相關的統計資訊收集。這適用於那些列的資料類型。預設情況下，如果該資料類型具有預設的B-樹操作符類，ANALYZE將嘗試通過使用類型的equals和less-than操作符來收集統計資訊。然而，這種方式對於非標量類型並不合適，因此可以通過指定自訂的分析函數來替代預設行為。自訂分析函數需聲明單個類型為internal的參數，並返回boolean結果。

儘管只有針對新類型建立的I/O函數和其他函數才瞭解該類型內部表示的詳細資料，但某些關於內部表達的屬性必須聲明給PolarDB。其中最重要的屬性是internallength。基礎資料型別 (Elementary Data Type)可以是固定長度的（這種情況下internallength是一個正整數），也可以是可變長度的（將internallength設定為VARIABLE，在內部將typlen設定為-1表示）。所有可變長度類型的內部表示都必須以一個4位元組整數開頭，該整數表示該值的總長度。

PASSEDBYVALUE這一可選的標誌，表示新建立的資料類型應該通過值傳遞而不是通過引用傳遞。要使用PASSEDBYVALUE，該類型必須是定長的，且其內部表示的大小不能超過Datum類型所能容納的大小限制，這在某些系統上是4位元組，在其他系統上可能是8位元組。

alignment參數定義了資料類型的記憶體對齊要求。可能的值包括按1、2、4或8位元組邊界對齊。

storage參數允許為變長資料類型選擇合適的儲存策略。對於定長類型，唯一允許的策略是plain，意味著該類型的資料總是儲存在行內，並且不會進行壓縮。extended策略表示系統將嘗試對過長的資料值進行壓縮，並在必要時將資料移到主錶行之外。external策略允許將資料移出行，但系統不會嘗試壓縮。main策略雖然允許壓縮，但不鼓勵將資料移出主錶行，只有在沒有其他方式能使行大小合適的情況下，帶有這種儲存策略的資料才會被移出，而它會優先於extended和external策略的資料保留在行內。

除了plain之外，所有的storage選項都隱含該資料類型的函數能夠處理TOAST（The Oversized-Attribute Storage Technique，超大屬性儲存區技術）過的值。這裡指定的值只是決定一種可TOAST資料類型列的預設TOAST儲存策略，使用者仍可以使用ALTER TABLE SET STORAGE來為列選擇其他策略。

like_type參數提供了一種基於現有類型來定義新類型基本屬性的方法：直接複製一個已存在的類型的屬性。internallength、passedbyvalue、alignment和storage這些屬性的值將從指定的類型中複製過來（雖然這些屬性的值可以在LIKE子句中被覆蓋，但這通常是不必要的）。當新類型的底層實現實際上是以某個現有類型作為承載體的時候，這種方法來指定類型的屬性特別有用。

category和preferred參數提供了助力，用於在存在模稜兩可的情形下決定應用哪種隱式類型轉換。每種資料類型都被分配到以單個ASCII字元命名的類別中，同時每種類型都有可能成為其所屬類別中的首選類型。當需要消除重載函數或操作符的歧義時，解析器將優先考慮轉換為首選類型（但僅限於同一類別內的類型轉換）。對於那些既不隱式轉換為任何其他類型，也不接受從任何其他類型隱式轉換過來的類型，可以保持這些設定的預設值。然而，對於一組具有隱式轉換關係的相互關聯類型，標記它們屬於同一類別並選擇一種或兩種最常用的類型作為該類別的首選通常是非常有協助的。特別是在將一種使用者定義的類型加入到一個現有的內建類別中（例如數字或字串類別）時，category參數非常有用。自然也可以建立一個全新的、完全由使用者定義的類型所組成的類別。對於這類新類別，可以選擇任何非大寫字母的ASCII字元作為標識。

若使用者希望資料類型的列在預設情況下具有某個非空值，可以指定預設值。預設值可通過DEFAULT關鍵詞來指定（這可以被附加到特定列上的顯式DEFAULT子句覆蓋）。

要定義一種資料類型為數群組類型，可使用ELEMENT關鍵詞來指明該數組的基本元素類型。例如，要定義一種基於4位元組整數int4的數群組類型，應指定ELEMENT = int4。

為了指定分隔這種類型數組在外部表格示中的值的定界符，delimiter可設定為特定字元。預設的定界符是逗號（,）。

如果collatable這一可選的布爾參數被設定為真，該種類型的列定義和運算式就可以通過使用COLLATE子句來攜帶定序資訊。實施該類型操作的函數需要負責實際利用這些資訊。僅僅將類型標記為可排序，並不意味著它們會自動使用這類資訊。

數群組類型

在PolarDB中，一旦使用者定義了一種新的資料類型，系統會自動建立相應的數群組類型。這個自動產生的數群組類型的名稱是由原始元素類型的名稱前加一個底線來構成的。如果這樣組成的名稱長度超出了NAMEDATALEN位元組的限制，則名稱會被自動截斷。如果截斷後的名稱與現有類型的名稱發生衝突，系統會嘗試其他的名稱，直至找到一個不會造成衝突的名字。

這種隱式建立的數群組類型是變長的，它使用內建的輸入和輸出函數array_in以及array_out。這個數群組類型將會跟隨其元素類型的所有權變動或模式變動而相應地變更。如果其元素類型被刪除，那麼這個數群組類型也會隨之被刪除。這樣的設計確保了類型系統的一致性和簡潔性，同時避免了使用者手動建立和管理數群組類型所需的額外工作。

儘管在PolarDB中系統會自動建立與使用者定義型別對應的數群組類型，ELEMENT選項的實際用途出現在一個特定情境中：當您建立的是一種定長類型，並且這種類型內部本質上是多個相同元素的數組時。假設您不僅希望為這種類型提供整體的操作，還想允許通過下標來直接存取數組內的各個元素。例如，point類型內部包含兩個浮點數，通過point[0]和point[1]可以直接存取這兩個座標值。

參數

說明

建議在命名類型和表時避免使用以底線開頭的名稱。雖然資料庫具有機制來修改自動產生的數群組類型的名稱，以防止與使用者定義的名稱產生衝突，但仍然存在潛在的混淆風險。以底線開頭的名稱通常被保留用於資料庫系統自身產生的名稱，如數群組類型名稱的約定，這些名稱是由基礎類型名稱加上前置底線構成的。因此，為了減少不必要的混淆並保持命名清晰一致，最好避免這種命名做法。

樣本

建立一個組合類別型，然後在隨後的函數定義中將其作為傳回型別使用：

CREATE TYPE compfoo AS (f1 int, f2 text);

CREATE FUNCTION getfoo() RETURNS SETOF compfoo AS $$
    SELECT fooid, fooname FROM foo
$$ LANGUAGE SQL;

建立一個新的組合類別型的過程，並在建立後對這個類型的定義進行了更新：

CREATE TYPE compfoo AS (f1 int, f2 text);

CREATE OR REPLACE TYPE compfoo AS (f2 text, f1 int);

定義一個枚舉類型，並將其應用於建立表的定義中：

CREATE TYPE bug_status AS ENUM ('new', 'open', 'closed');

CREATE TABLE bug (
    id serial,
    description text,
    status bug_status
);

建立一個範圍類型：

CREATE TYPE float8_range AS RANGE (subtype = float8, subtype_diff = float8mi);

建立一個名為box的使用者定義的基礎資料型別 (Elementary Data Type)，並將其用作新表定義中的一列資料類型：

CREATE TYPE box;

CREATE FUNCTION my_box_in_function(cstring) RETURNS box AS ... ;
CREATE FUNCTION my_box_out_function(box) RETURNS cstring AS ... ;

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

建立一個大物件類型並且將它用在了一個表定義中：

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