PostgreSQL 組合類型

這里有兩個定義組合類型的簡單例子：

CREATE TYPE complex AS (
    r       double precision,
    i       double precision
);

CREATE TYPE inventory_item AS (
    name            text,
    supplier_id     integer,
    price           numeric
);

該語法堪比CREATE TABLE，不過只能指定域名和類型，當(dāng)前不能包括約束（例如NOT NULL）。注意AS關(guān)鍵詞是必不可少的，如果沒有它，系統(tǒng)將認(rèn)為用戶想要的是一種不同類型的CREATE TYPE命令，并且你將得到奇怪的語法錯誤。

定義了類型之后，我們可以用它們來創(chuàng)建表：

CREATE TABLE on_hand (
    item      inventory_item,
    count     integer
);

INSERT INTO on_hand VALUES (ROW('fuzzy dice', 42, 1.99), 1000);

或函數(shù):

CREATE FUNCTION price_extension(inventory_item, integer) RETURNS numeric
AS 'SELECT $1.price * $2' LANGUAGE SQL;

SELECT price_extension(item, 10) FROM on_hand;

只要你創(chuàng)建了一個表，也會自動創(chuàng)建一個組合類型來表示表的行類型，它具有和表一樣的名稱。例如，如果我們說：

CREATE TABLE inventory_item (
    name            text,
    supplier_id     integer REFERENCES suppliers,
    price           numeric CHECK (price > 0)
);

那么和上面所示相同的inventory_item組合類型將成為一種副產(chǎn)品，并且可以按上面所說的進行使用。不過要注意當(dāng)前實現(xiàn)的一個重要限制：因為沒有約束與一個組合類型相關(guān)，顯示在表定義中的約束不會應(yīng)用于表外組合類型的值（要解決這個問題，可以在該組合類型上創(chuàng)建一個域，并且把想要的約束應(yīng)用為這個域上的CHECK約束）。

要把一個組合值寫作一個文字常量，將該域值封閉在圓括號中并且用逗號分隔它們。你可以在任何域值周圍放上雙引號，并且如果該域值包含逗號或圓括號則必須這樣做（更多細(xì)節(jié)見下文）。這樣，一個組合常量的一般格式是下面這樣的：

'( val1 , val2 , ... )'

一個例子是：

'("fuzzy dice",42,1.99)'

這將是上文定義的inventory_item類型的一個合法值。要讓一個域為 NULL，在列表中它的位置上根本不寫字符。例如，這個常量指定其第三個域為 NULL：

'("fuzzy dice",42,)'

如果你寫一個空字符串而不是 NULL，寫上兩個引號：

'("",42,)'

這里第一個域是一個非 NULL 空字符串，第三個是 NULL。

（這些常量實際上只是第 4.1.2.7 節(jié)中討論的一般類型常量的特殊類型。該常量最初被當(dāng)做一個字符串并且被傳遞給組合類型輸入轉(zhuǎn)換例程。有必要用一次顯式類型說明來告知要把該常量轉(zhuǎn)換成何種類型。）。

ROW表達式也能被用來構(gòu)建組合值。在大部分情況下，比起使用字符串語法，這相當(dāng)簡單易用，因為你不必?fù)?dān)心多層引用。我們已經(jīng)在上文用過這種方法：

ROW('fuzzy dice', 42, 1.99)
ROW('', 42, NULL)

只要在表達式中有多于一個域，ROW 關(guān)鍵詞實際上就是可選的，因此這些可以被簡化成：

('fuzzy dice', 42, 1.99)
('', 42, NULL)

第 4.2.13 節(jié)中更加詳細(xì)地討論了ROW表達式語法。

要訪問一個組合列的一個域，可以寫成一個點和域的名稱，更像從一個表名中選擇一個域。事實上，它太像從一個表名中選擇，這樣我們不得不使用圓括號來避免讓解析器混淆。例如，你可能嘗試從例子表on_hand中選取一些子域：

SELECT item.name FROM on_hand WHERE item.price > 9.99;

這不會有用，因為名稱item會被當(dāng)成是一個表名，而不是on_hand的一個列名。你必須寫成這樣：

SELECT (item).name FROM on_hand WHERE (item).price > 9.99;

或者你還需要使用表名（例如在一個多表查詢中），像這樣：

SELECT (on_hand.item).name FROM on_hand WHERE (on_hand.item).price > 9.99;

現(xiàn)在加上括號的對象就被正確地解釋為對item列的引用，然后可以從中選出子域。

只要你從一個組合值中選擇一個域，相似的語法問題就適用。例如，要從一個返回組合值的函數(shù)的結(jié)果中選取一個域，你需要這樣寫：

SELECT (my_func(...)).field FROM ...

如果沒有額外的圓括號，這將生成一個語法錯誤。

特殊的域名稱*表示“所有的域”，本文中的第 8.16.5 節(jié)中有進一步的解釋。

這里有一些插入和更新組合列的正確語法的例子。首先，插入或者更新一整個列：

INSERT INTO mytab (complex_col) VALUES((1.1,2.2));

UPDATE mytab SET complex_col = ROW(1.1,2.2) WHERE ...;

第一個例子忽略ROW，第二個例子使用它，我們可以用兩者之一完成。

我們能夠更新一個組合列的單個子域：

UPDATE mytab SET complex_col.r = (complex_col).r + 1 WHERE ...;

注意這里我們不需要（事實上也不能）把圓括號放在正好出現(xiàn)在SET之后的列名周圍，但是當(dāng)在等號右邊的表達式中引用同一列時確實需要圓括號。

并且我們也可以指定子域作為INSERT的目標(biāo)：

INSERT INTO mytab (complex_col.r, complex_col.i) VALUES(1.1, 2.2);

如果我們沒有為該列的所有子域提供值，剩下的子域?qū)⒂每罩堤畛洹?

對于查詢中的組合類型有各種特殊的語法規(guī)則和行為。這些規(guī)則提供了有用的捷徑，但是如果你不懂背后的邏輯就會被此困擾。

在PostgreSQL中，查詢中對一個表名（或別名）的引用實際上是對該表的當(dāng)前行的組合值的引用。例如，如果我們有一個如上所示的表inventory_item，我們可以寫：

SELECT c FROM inventory_item c;

這個查詢產(chǎn)生一個單一組合值列，所以我們會得到這樣的輸出：

           c
------------------------
 ("fuzzy dice",42,1.99)
(1 row)

不過要注意簡單的名稱會在表名之前先匹配到列名，因此這個例子可行的原因僅僅是因為在該查詢的表中沒有名為c的列。

普通的限定列名語法table_name.column_name可以理解為把字段選擇應(yīng)用在該表的當(dāng)前行的組合值上（由于效率的原因，實際上不是以這種方式實現(xiàn)）。

當(dāng)我們寫

SELECT c.* FROM inventory_item c;

時，根據(jù)SQL標(biāo)準(zhǔn)，我們應(yīng)該得到該表展開成列的內(nèi)容：

    name    | supplier_id | price
------------+-------------+-------
 fuzzy dice |          42 |  1.99
(1 row)

就好像查詢是

SELECT c.name, c.supplier_id, c.price FROM inventory_item c;

盡管如上所示，PostgreSQL將對任何組合值表達式應(yīng)用這種展開行為，但只要.*所應(yīng)用的值不是一個簡單的表名，你就需要把該值寫在圓括號內(nèi)。例如，如果myfunc()是一個返回組合類型的函數(shù)，該組合類型由列a、b和c組成，那么這兩個查詢有相同的結(jié)果：

SELECT (myfunc(x)).* FROM some_table;
SELECT (myfunc(x)).a, (myfunc(x)).b, (myfunc(x)).c FROM some_table;

SELECT m.* FROM some_table, LATERAL myfunc(x) AS m;

當(dāng)composite_value.*出現(xiàn)在一個SELECT輸出列表的頂層中、INSERT/UPDATE/DELETE中的一個RETURNING列表中、一個VALUES子句中或者一個行構(gòu)造器中時，該語法會導(dǎo)致這種類型的列展開。在所有其他上下文（包括被嵌入在那些結(jié)構(gòu)之一中時）中，把.*附加到一個組合值不會改變該值，因為它表示“所有的列”并且因此同一個組合值會被再次產(chǎn)生。例如，如果somefunc()接受一個組合值參數(shù)，這些查詢是相同的：

SELECT somefunc(c.*) FROM inventory_item c;
SELECT somefunc(c) FROM inventory_item c;

在兩種情況中，inventory_item的當(dāng)前行被傳遞給該函數(shù)作為一個單一的組合值參數(shù)。即使.*在這類情況中什么也不做，使用它也是一種好的風(fēng)格，因為它說清了一個組合值的目的是什么。特別地，解析器將會認(rèn)為c.*中的c是引用一個表名或別名，而不是一個列名，這樣就不會出現(xiàn)混淆。而如果沒有.*，就弄不清楚c到底是表示一個表名還是一個列名，并且在有一個名為c的列時會優(yōu)先選擇按列名來解釋。

另一個演示這些概念的例子是下面這些查詢，它們表示相同的東西：

SELECT * FROM inventory_item c ORDER BY c;
SELECT * FROM inventory_item c ORDER BY c.*;
SELECT * FROM inventory_item c ORDER BY ROW(c.*);

所有這些ORDER BY子句指定該行的組合值，導(dǎo)致根據(jù)第 9.24.6 節(jié)中介紹的規(guī)則對行進行排序。不過，如果inventory_item包含一個名為c的列，第一種情況會不同于其他情況，因為它表示僅按那一列排序。給定之前所示的列名，下面這些查詢也等效于上面的那些查詢：

SELECT * FROM inventory_item c ORDER BY ROW(c.name, c.supplier_id, c.price);
SELECT * FROM inventory_item c ORDER BY (c.name, c.supplier_id, c.price);

（最后一種情況使用了一個省略關(guān)鍵字ROW的行構(gòu)造器）。

另一種與組合值相關(guān)的特殊語法行為是，我們可以使用函數(shù)記法來抽取一個組合值的字段。解釋這種行為的簡單方式是記法field(table)和table.field是可以互換的。例如，這些查詢是等效的：

SELECT c.name FROM inventory_item c WHERE c.price > 1000;
SELECT name(c) FROM inventory_item c WHERE price(c) > 1000;

此外，如果我們有一個函數(shù)接受單一的組合類型參數(shù)，我們可以以任意一種記法來調(diào)用它。這些查詢?nèi)际堑刃У模?

SELECT somefunc(c) FROM inventory_item c;
SELECT somefunc(c.*) FROM inventory_item c;
SELECT c.somefunc FROM inventory_item c;

這種函數(shù)記法和字段記法之間的等效性使得我們可以在組合類型上使用函數(shù)來實現(xiàn)“計算字段”。 一個使用上述最后一種查詢的應(yīng)用不會直接意識到somefunc不是一個真實的表列。

一個組合值的外部文本表達由根據(jù)域類型的 I/O 轉(zhuǎn)換規(guī)則解釋的項，外加指示組合結(jié)構(gòu)的裝飾組成。裝飾由整個值周圍的圓括號（(和)），外加相鄰項之間的逗號（,）組成。圓括號之外的空格會被忽略，但是在圓括號之內(nèi)空格會被當(dāng)成域值的一部分，并且根據(jù)域數(shù)據(jù)類型的輸入轉(zhuǎn)換規(guī)則可能有意義，也可能沒有意義。例如，在

'(  42)'

中，如果域類型是整數(shù)則空格會被忽略，而如果是文本則空格不會被忽略。

如前所示，在寫一個組合值時，你可以在任意域值周圍寫上雙引號。如果不這樣做會讓域值迷惑組合值解析器，你就必須這么做。特別地，包含圓括號、逗號、雙引號或反斜線的域必須用雙引號引用。要把一個雙引號或者反斜線放在一個被引用的組合域值中，需要在它前面放上一個反斜線（還有，一個雙引號引用的域值中的一對雙引號被認(rèn)為是表示一個雙引號字符，這和 SQL 字符串中單引號的規(guī)則類似）。另一種辦法是，你可以避免引用以及使用反斜線轉(zhuǎn)義來保護所有可能被當(dāng)作組合語法的數(shù)據(jù)字符。

一個全空的域值（在逗號或圓括號之間完全沒有字符）表示一個 NULL。要寫一個空字符串值而不是 NULL，可以寫成""。

如果域值是空串或者包含圓括號、逗號、雙引號、反斜線或空格，組合輸出例程將在域值周圍放上雙引號（對空格這樣處理并不是不可缺少的，但是可以提高可讀性）。嵌入在域值中的雙引號及反斜線將被雙寫。

INSERT ... VALUES ('("\"\\")');