?
本文檔使用 PHP中文網(wǎng)手冊 發(fā)布
8.14. 數(shù)組
PostgreSQL允許將字段定義成定長或變長的一維或多維數(shù)組。 數(shù)組類型可以是任何基本類型或用戶定義類型,枚舉類型或者組合類型。 不支持復合類型和域的數(shù)組。
8.14.1. 數(shù)組類型的聲明
為說明這些用法，我們先創(chuàng)建一個由基本類型數(shù)組構(gòu)成的表：
CREATE TABLE sal_emp (
name text,
pay_by_quarter integer[],
schedule text[][]
);
如上所示，一個數(shù)組類型是通過在數(shù)組元素類型名后面附加方括弧([])來命名的 上面的命令將創(chuàng)建一個叫sal_emp的表， 表示雇員名字的name字段是一個text類型字符串， 表示雇員季度薪水的pay_by_quarter字段是一個一維integer數(shù)組， 表示雇員周計劃的schedule字段是一個兩維text數(shù)組。
CREATE TABLE的語法允許聲明數(shù)組的確切大小，比如：
CREATE TABLE tictactoe (
squares integer[3][3]
);
不過，目前的實現(xiàn)并不強制數(shù)組尺寸限制(等價于未聲明長度的數(shù)組)。
實際上，目前的實現(xiàn)也不強制數(shù)組維數(shù)。 特定元素類型的數(shù)組都被認為是相同的類型，不管他們的大小或者維數(shù)。 因此，在CREATE TABLE里定義數(shù)字或者維數(shù)都不影響運行時的行為。
另外還有一種語法，它遵循SQL標準，運用主鍵ARRAY，可以用于聲明一維數(shù)組。 pay_by_quarter可以定義為：
pay_by_quarter integer ARRAY[4],
或者，如果沒有指定數(shù)組大?。?
pay_by_quarter integer ARRAY,
不過，如前所述，PostgreSQL并不強制這個尺寸限制。
8.14.2. 數(shù)組值輸入
將數(shù)組寫成文本的時候， 用花括弧把數(shù)組元素括起來并且用逗號將它們分開(如果你懂C，那么這與初始化一個結(jié)構(gòu)很像)。 你可以在數(shù)組元素值周圍放置雙引號，但如果這個值包含逗號或者花括弧， 那么就必須加上雙引號(下面有更多細節(jié))。因此，一個數(shù)組常量的常見格式如下：
'{ val1 delim val2 delim ... }'
這里的delim是該類型的分隔符， 就是在該類型的pg_type記錄中指定的那個。 在PostgreSQL發(fā)布提供的標準數(shù)據(jù)類型里，除了box類型使用分號(;)之外， 其它所有類型都使用逗號(,)。每個val要么是一個數(shù)組元素類型的常量， 要么是一個子數(shù)組。一個數(shù)組常量的例子如下：
'{{1,2,3},{4,5,6},{7,8,9}}'
這個常量是一個3乘3的兩維數(shù)組，由三個整數(shù)子數(shù)組組成。
要將一個數(shù)組元素的值設為NULL，直接寫上NULL即可(大小寫無關(guān))。 要將一個數(shù)組元素的值設為字符串"NULL"，那么你必須加上雙引號。
這種數(shù)組常量實際上只是我們在Section 4.1.2.7里 討論過的一般類型常量的一種特例。 常量最初是當作字符串看待并且傳遞給數(shù)組輸入轉(zhuǎn)換器的， 可能需要使用明確的類型聲明。
現(xiàn)在我們可以展示一些INSERT語句:
INSERT INTO sal_emp
VALUES ('Bill',
'{10000, 10000, 10000, 10000}',
'{{"meeting", "lunch"}, {"training", "presentation"}}');
INSERT INTO sal_emp
VALUES ('Carol',
'{20000, 25000, 25000, 25000}',
'{{"breakfast", "consulting"}, {"meeting", "lunch"}}');
前面的兩個插入的結(jié)果看起來像這樣：
SELECT * FROM sal_emp;
name | pay_by_quarter | schedule
-------+---------------------------+-------------------------------------------
Bill | {10000,10000,10000,10000} | {{meeting,lunch},{training,presentation}}
Carol | {20000,25000,25000,25000} | {{breakfast,consulting},{meeting,lunch}}
(2 rows)
多維數(shù)組必須匹配每個維的元素數(shù)。如果不匹配將導致錯誤,如：
INSERT INTO sal_emp
VALUES ('Bill',
'{10000, 10000, 10000, 10000}',
'{{"meeting", "lunch"}, {"meeting"}}');
ERROR: multidimensional arrays must have array expressions with matching dimensions
我們還可以使用ARRAY構(gòu)造器語法:
INSERT INTO sal_emp
VALUES ('Bill',
ARRAY[10000, 10000, 10000, 10000],
ARRAY[['meeting', 'lunch'], ['training', 'presentation']]);
INSERT INTO sal_emp
VALUES ('Carol',
ARRAY[20000, 25000, 25000, 25000],
ARRAY[['breakfast', 'consulting'], ['meeting', 'lunch']]);
請注意數(shù)組元素是普通的SQL常量或者表達式； 比如，字符串文本是用單引號包圍的，而不是像數(shù)組文本那樣用雙引號。 ARRAY構(gòu)造器語法在Section 4.2.11里有更詳細的討論。
8.14.3. 訪問數(shù)組
現(xiàn)在我們可以在這個表上運行一些查詢。 首先，我們演示如何一次訪問數(shù)組的一個元素。 這個查詢檢索在第二季度薪水變化的雇員名:
SELECT name FROM sal_emp WHERE pay_by_quarter[1] <> pay_by_quarter[2]; name ------- Carol (1 row)
數(shù)組的下標數(shù)字是寫在方括弧內(nèi)的。 PostgreSQL缺省使用以1為基的數(shù)組習慣， 也就是說，一個n元素的數(shù)組從array[1]開始，到array[n]結(jié)束。
這個查詢檢索所有雇員第三季度的薪水:
SELECT pay_by_quarter[3] FROM sal_emp;
pay_by_quarter
----------------
10000
25000
(2 rows)
我們還可以訪問一個數(shù)組的任意矩形片段，或稱子數(shù)組。 對于一維或更多維數(shù)組，可以用(lower-bound(下標下界)): (upper-bound(上界上標)) 表示一個數(shù)組的某個片段。 比如，下面查詢檢索Bill該周前兩天的計劃中的第一件事情：
SELECT schedule[1:2][1:1] FROM sal_emp WHERE name = 'Bill';
schedule
------------------------
{{meeting},{training}}
(1 row)
如果任意維數(shù)寫成片段，即包含一個冒號，那么所有的維數(shù)都可以看成片段。 對于沒有冒號且僅有單一數(shù)字的任意維數(shù)，可以看成是從1到該指定數(shù)字。 例如，[2]可以看成[1:2]，正如在這樣的例子中：
SELECT schedule[1:2][2] FROM sal_emp WHERE name = 'Bill';
schedule
-------------------------------------------
{{meeting,lunch},{training,presentation}}
(1 row)
為了避免和非片段實例混淆，最好對所有維數(shù)使用片段語法，例如，[1:2][1:1]，而不是[2][1:1]。
如果數(shù)組本身或者任何一個下標表達式是NULL，那么，該數(shù)組的下標表達式會返回NULL。同樣的， 從一個數(shù)組的當前范圍之外抓取數(shù)據(jù)時，不會產(chǎn)生錯誤，而是也返回一個NULL。 比如，如果schedule目前的維是[1:3][1:2]， 那么，當我們抓取schedule[3][3]時會生成NULL 。 類似的還有，引用一個下標錯誤的數(shù)組時也會生成 NULL，而不是錯誤。
如果數(shù)組本身或任何一個下標表達式是NULL，那么，該數(shù)組的片段表達式也將生成NULL 。 但在其它其它情況下，比如當完全在數(shù)組的當前范圍之外抓取一個數(shù)組片斷時， 將生成一個空數(shù)組(零維)而不是NULL 。 (這與非片段形式不匹配，并且有這樣做的歷史原因。) 如果抓取的片斷部分覆蓋數(shù)組的范圍，那么它會自動縮減為抓取覆蓋的范圍,而不是NULL。
可以通過array_dims函數(shù)來檢索任何一個數(shù)組的當前維數(shù)：
SELECT array_dims(schedule) FROM sal_emp WHERE name = 'Carol'; array_dims ------------ [1:2][1:2] (1 row)
array_dims函數(shù)返回一個text類型的結(jié)果，從而便于人們閱讀和理解。
同樣的，我們也可以用array_upper和array_lower函數(shù)來
分別返回一個特定維的上界和下界：
SELECT array_upper(schedule, 1) FROM sal_emp WHERE name = 'Carol';
array_upper
-------------
2
(1 row)
而array_length可以用來查看指定維的長度：
SELECT array_length(schedule, 1) FROM sal_emp WHERE name = 'Carol';
array_length
--------------
2
(1 row)
8.14.4. 修改數(shù)組
數(shù)組值是可以完全被代替的，如：
UPDATE sal_emp SET pay_by_quarter = '{25000,25000,27000,27000}'
WHERE name = 'Carol';
或者使用ARRAY構(gòu)造器語法：
UPDATE sal_emp SET pay_by_quarter = ARRAY[25000,25000,27000,27000]
WHERE name = 'Carol';
同樣，也可以只更新某一個元素：
UPDATE sal_emp SET pay_by_quarter[4] = 15000
WHERE name = 'Bill';
或者更新某個片斷
UPDATE sal_emp SET pay_by_quarter[1:2] = '{27000,27000}'
WHERE name = 'Carol';
可以通過給尚不存在的數(shù)組元素賦值的辦法來擴大數(shù)組， 所有位于原數(shù)組最后一個元素和這個新元素之間的未賦值元素都將設為NULL。 例如，如果myarray數(shù)組當前有4個元素，在對myarray[6]賦值之后它將擁有6個元素， 其中myarray[5]的值將為NULL。 目前，只允許對一維數(shù)組使用這種方法擴大，而不是多維數(shù)組。
下標賦值允許創(chuàng)建下標不從1開始的數(shù)組。 比如，我們可以通過給myarray[-2:7]賦值，來創(chuàng)建一個下標值在-2到7之間的數(shù)組。
新的數(shù)組值也可以用連接操作符||構(gòu)造。
SELECT ARRAY[1,2] || ARRAY[3,4];
?column?
-----------
{1,2,3,4}
(1 row)
SELECT ARRAY[5,6] || ARRAY[[1,2],[3,4]];
?column?
---------------------
{{5,6},{1,2},{3,4}}
(1 row)
連接操作符允許把一個元素壓入一維數(shù)組的開頭或者結(jié)尾，當然，也接受兩個N維的數(shù)組， 或者一個N維和一個N+1維的數(shù)組。
當向一維數(shù)組的頭部或尾部壓入單獨一個元素后，數(shù)組的下標下界保持不變。 比如：
SELECT array_dims(1 || '[0:1]={2,3}'::int[]);
array_dims
------------
[0:2]
(1 row)
SELECT array_dims(ARRAY[1,2] || 3);
array_dims
------------
[1:3]
(1 row)
如果將兩個相同維數(shù)的數(shù)組連接在一起，結(jié)果數(shù)組將保留左操作數(shù)的外層維數(shù)的下標下界值，即 結(jié)果會是這樣一個數(shù)組：包含左操作數(shù)的每個元素，后面跟著右操作數(shù)的每個元素。比如：
SELECT array_dims(ARRAY[1,2] || ARRAY[3,4,5]); array_dims ------------ [1:5] (1 row) SELECT array_dims(ARRAY[[1,2],[3,4]] || ARRAY[[5,6],[7,8],[9,0]]); array_dims ------------ [1:5][1:2] (1 row)
如果將一個N維的數(shù)組壓到一個N+1維數(shù)組的開頭或者結(jié)尾，結(jié)果和上面數(shù)組元素的情況類似。 每個N維子數(shù)組實際上都是N+1維數(shù)組的最外層元素。比如：
SELECT array_dims(ARRAY[1,2] || ARRAY[[3,4],[5,6]]); array_dims ------------ [1:3][1:2] (1 row)
也可以用 array_prepend, array_append,
array_cat函數(shù)來構(gòu)造函數(shù)。
前兩個只支持一維數(shù)組，而array_cat支持多維數(shù)組。
需要注意的是使用連接操作符要比直接使用這些函數(shù)好。實際上，這些函數(shù)主要是用于實現(xiàn)連接操作符。
不過，在創(chuàng)建用戶定義函數(shù)時，直接使用這些函數(shù)可能會更直接有效。比如：
SELECT array_prepend(1, ARRAY[2,3]);
array_prepend
---------------
{1,2,3}
(1 row)
SELECT array_append(ARRAY[1,2], 3);
array_append
--------------
{1,2,3}
(1 row)
SELECT array_cat(ARRAY[1,2], ARRAY[3,4]);
array_cat
-----------
{1,2,3,4}
(1 row)
SELECT array_cat(ARRAY[[1,2],[3,4]], ARRAY[5,6]);
array_cat
---------------------
{{1,2},{3,4},{5,6}}
(1 row)
SELECT array_cat(ARRAY[5,6], ARRAY[[1,2],[3,4]]);
array_cat
---------------------
{{5,6},{1,2},{3,4}}
8.14.5. 在數(shù)組中檢索
為了查找一個數(shù)組中的某個數(shù)值，必須檢查該數(shù)組的每一個值。 而如果你知道這個數(shù)組的尺寸，那么你完全可以進行手工處理。比如：
SELECT * FROM sal_emp WHERE pay_by_quarter[1] = 10000 OR
pay_by_quarter[2] = 10000 OR
pay_by_quarter[3] = 10000 OR
pay_by_quarter[4] = 10000;
不過，對于大數(shù)組而言，這個方法會讓人覺得很無聊，并且，如果你不知道數(shù)組的尺寸，也是沒什么用的。 在Section 9.21里為大家描述了另外一個方法。 上面的查詢可以用下面的代替：
SELECT * FROM sal_emp WHERE 10000 = ANY (pay_by_quarter);
此外，你可以找到數(shù)組的中所有等于10000的值的行：
SELECT * FROM sal_emp WHERE 10000 = ALL (pay_by_quarter);
另外，也可以使用generate_subscripts函數(shù)，如：
SELECT * FROM
(SELECT pay_by_quarter,
generate_subscripts(pay_by_quarter, 1) AS s
FROM sal_emp) AS foo
WHERE pay_by_quarter[s] = 10000;
Table 9-46中有對該函數(shù)的說明。
Tip: 數(shù)組不是集合；搜索數(shù)組中的特定元素通常表明你的數(shù)據(jù)庫設計有問題。 數(shù)組字段通常是可以分裂成獨立的表。 很明顯表要容易搜索得多，并且在元素數(shù)目非常龐大的時候也可以更好地伸展。
8.14.6. 數(shù)組的輸入和輸出語法
一個數(shù)組值的外部表現(xiàn)形式由一些根據(jù)該數(shù)組元素類型的I/O轉(zhuǎn)換規(guī)則分析的項組成， 再加上一些標明該數(shù)組結(jié)構(gòu)的修飾 這些修飾由圍繞在數(shù)組值周圍的花括弧({ and })加上相鄰項之間的分隔字符組成。 分隔字符通常是一個逗號(,)，但也可以是其它的東西：它由該數(shù)組元素類型的typdelim設置決定。 在PostgreSQL提供的標準數(shù)據(jù)類型里，除了box類型使用分號(;)外,所有其它類型都使用逗號。 在多維數(shù)組里，每個維都有自己級別的花括弧，并且在同級相鄰的花括弧項之間必須寫上分隔符。
如果數(shù)組元素值是空字符串或者包含花括弧、分隔符、雙引號、反斜杠、空白， 或者匹配NULL關(guān)鍵字，那么數(shù)組輸出過程將在這些值周圍包圍雙引號。 在元素值里包含的雙引號和反斜杠將被反斜杠逃逸。 對于數(shù)值類型，你可以安全地假設數(shù)值沒有雙引號包圍， 但是對于文本類型，我們就需要準備好面對有雙引號包圍和沒有雙引號包圍兩種情況了。
缺省時，一個數(shù)組維數(shù)的下標索引設置為1。 如果一個數(shù)組維數(shù)下標不等于1，那么就會在數(shù)組結(jié)構(gòu)修飾域里面放置一個實際的維數(shù)。 這個修飾由方括弧(([])圍繞在每個數(shù)組維的下界和上界索引，中間有一個冒號(:)分隔的字符串組成。數(shù)組維數(shù)修飾后面跟著一個等號操作符(=)。比如：
SELECT f1[1][-2][3] AS e1, f1[1][-1][5] AS e2
FROM (SELECT '[1:1][-2:-1][3:5]={{{1,2,3},{4,5,6}}}'::int[] AS f1) AS ss;
e1 | e2
----+----
1 | 6
(1 row)
僅當一個或多個下界不等于1時，數(shù)組輸出程序才在結(jié)果中包含明確的尺寸。
如果一個數(shù)組元素的值寫成NULL(不區(qū)分大小寫)，那么該元素的值就是NULL。 而引號和反斜杠可以表示輸入文本字符串"NULL"值。 另外，為了兼容8.2之前的版本， 可以將array_nulls配置參數(shù)設為off以禁止將NULL識別為NULL。
如前所示，當書寫一個數(shù)組值的時候，可以在任何元素值周圍加上雙引號。 當元素值可能讓數(shù)組值解析器產(chǎn)生歧義時，就必須在元素周圍加上雙引號，例如：元素值包含花括號、逗號(或者是其它分割符)、雙引號、反斜杠、在開頭/結(jié)尾處有空白符、匹配NULL的字符串。 要在元素值中包含雙引號或反斜杠，可以加一個前導反斜杠。 當然，你也可以使用反斜杠逃逸來保護任何可能引起語法混淆的字符。
你可以在左花括弧前面或者右花括弧后面寫空白，也可以在任意獨立的項字符串前面或者后面寫空白。 所有這些情況下，空白都會被忽略。 不過，在雙引號包圍的元素里面的空白，或者是元素里被兩邊非空白字符包圍的空白，不會被忽略。
Note: 請記住你在SQL命令里寫的任何東西都將首先解釋成一個字符串文本，然后才是一個數(shù)組。 這樣就造成你所需要的反斜杠數(shù)量翻了翻。 比如，要插入一個包含反斜杠和雙引號的text數(shù)組，你需要這么寫:
INSERT ... VALUES (E'{"\\\\","\\""}');字符串文本處理器去掉第一層反斜杠，然后省下的東西到了數(shù)組數(shù)值分析器的時候?qū)⒆兂?tt class="LITERAL">{"\\","\""}。 接著，該字符串傳遞給text數(shù)據(jù)類型的輸入過程，分別變成\和"。 如果我們使用的數(shù)據(jù)類型對反斜杠也有特殊待遇，比如bytea， 那么我們可能需要在命令里放多達八個反斜杠才能在存儲態(tài)的數(shù)組元素中得到一個反斜杠。 也可以用美元符界定(參閱Section 4.1.2.4)來避免雙份的反斜杠。
Tip: ARRAY構(gòu)造器語法(參閱xref linkend="sql-syntax-array-constructors">) 通常比數(shù)組文本語法好用些，尤其是在SQL命令里寫數(shù)組值的時候。 在ARRAY里，獨立的元素值的寫法和數(shù)組里沒有元素時的寫法一樣。