8.10. 配列

8.10.1. 配列型の宣言

実際の配列の使いかたを説明するために、つぎのテーブルを作成します。

CREATE TABLE sal_emp (
    name            text,
    pay_by_quarter  integer[],
    schedule        text[][]
);

見てお解りのように配列データ型は配列要素のデータ型の名前に鈎カッコ（[]）をつけて指定します。 このコマンドは text型文字列 （name）、従業員の四半期の給与を保存する integer 型の一次元配列（pay_by_quarter）、そして従業員の週間スケジュールを保存する text 型の二次元配列（schedule）の列を持つ sal_emp という名前のテーブルを作成します。

CREATE TABLE の構文で指定する配列の正確な大きさを決めることができます。

CREATE TABLE tictactoe (
    squares   integer[3][3]
);

とは言っても現在の実装では配列の大きさの制限を強要しません。--- 長さの指定がない配列とおなじ振舞をします。

実際のところ現在の実装では次元数の宣言も強制していません。特定の要素型の配列はすべて大きさあるいは次元数とは無関係に同じ型と見なされます。ですから CREATE TABLE で次元数や大きさを宣言することは単なるコメントで、実行時の動作に影響を及ぼすものではありません。

別の方策として一次元配列に SQL99-標準の構文を使うこともできます。pay_by_quarter を次のように定義することができます。

    pay_by_quarter  integer ARRAY[4],

この構文は配列の大きさを示す整数の定数を必要とします。とは言っても前で触れたように PostgreSQL は大きさの制限を強制しません。

8.10.2. 配列の値の入力

リテラル定数として配列の値を書き込むにはその要素の値を丸カッコ（中カッコ）で囲みそれぞれの要素の値をカンマで区切ります。（ C 言語を知っているならば、構造体を初期化するための構文のようなものと考えてください。）要素の値を二重引用符で囲うこともでき、カンマもしくは丸カッコがある時は必ずそのように書かなければなりません。（詳細は以下にでてきます。）したがって配列定数の一般的書式は次のようになります。

'{ val1 delim val2 delim ... }'

ここで delim はその pg_type エントリに記録されている型の区切り文字です。（すべての組込み型ではカンマ"," 文字です。）それぞれの val は配列要素の型の定数か副配列です。配列定数の例を以下に示します。

'{{1,2,3},{4,5,6},{7,8,9}}'

この定数は整数の三つの副配列を持っている二次元 3 x 3 の配列です。

（この種の配列定数は実際 項4.1.2.4 で説明のある一般型定数の特別の場合にすぎません。この定数はもともと文字列として扱われていて配列入力ルーチンに渡されました。明示的な型の仕様が必要でしょう。）

では INSERT 文をいくつか見てみましょう。

INSERT INTO sal_emp
    VALUES ('Bill',
    '{10000, 10000, 10000, 10000}',
    '{{"meeting", "lunch"}, {}}');

INSERT INTO sal_emp
    VALUES ('Carol',
    '{20000, 25000, 25000, 25000}',
    '{{"talk", "consult"}, {"meeting"}}');

現在の配列の実装の限界はある配列の個々の要素が SQL の NULL 値となれないことです。配列全体を NULL に設定することは可能ですが、いくつかの要素が NULL であってほかの要素が NULL でないという配列を持つことができません。

このことは驚くべき結果をもたらすことがあります。例えば上記の二つの INSERT はこのようになります。

SELECT * FROM sal_emp;
 name  |      pay_by_quarter       |      schedule
-------+---------------------------+--------------------
 Bill  | {10000,10000,10000,10000} | {{meeting},{""}}
 Carol | {20000,25000,25000,25000} | {{talk},{meeting}}
(2 rows)

schedule の [2][2] 要素がそれぞれの INSERT 文に抜けているので [1][2] 要素が破棄されたのです。

ARRAY 演算構文も使えます。

INSERT INTO sal_emp
    VALUES ('Bill',
    ARRAY[10000, 10000, 10000, 10000],
    ARRAY[['meeting', 'lunch'], ['','']]);

INSERT INTO sal_emp
    VALUES ('Carol',
    ARRAY[20000, 25000, 25000, 25000],
    ARRAY[['talk', 'consult'], ['meeting', '']]);
SELECT * FROM sal_emp;
 name  |      pay_by_quarter       |           schedule
-------+---------------------------+-------------------------------
 Bill  | {10000,10000,10000,10000} | {{meeting,lunch},{"",""}}
 Carol | {20000,25000,25000,25000} | {{talk,consult},{meeting,""}}
(2 rows)

この構文で多次元の配列はそれぞれの次元に対応する範囲を必要とすることを覚えておいてください。 対応していないと、前述の場合のように値が破棄されるのではなく、エラーになります。

INSERT INTO sal_emp
    VALUES ('Carol',
    ARRAY[20000, 25000, 25000, 25000],
    ARRAY[['talk', 'consult'], ['meeting']]);
ERROR:  multidimensional arrays must have array expressions with matching dimensions

更に配列要素は通常の SQL 定数若しくは演算式であることに注意してください。例えば文字列リテラルは配列リテラルにおけると同様二重引用符ではなく単一引用符で括られます。ARRAY 演算構文は項4.2.10 にてより詳しく説明されています。

8.10.3. 配列に対するアクセス

ではテーブルに対していくつかの問い合わせを行ってみましょう。初めに、一回である配列の単一要素にアクセスする方法を示します。この問い合わせは第二四半期に給与が更新された従業員の名前を抽出します。

SELECT name FROM sal_emp WHERE pay_by_quarter[1] <> pay_by_quarter[2];

 name
-------
 Carol
(1 row)

配列の添字番号は鈎カッコで囲んで書かれます。 デフォルトで PostgreSQL は配列に対し「1 始まり」の振り番規定を採用しています。つまり要素が n 個ある配列は array[1] で始まり、array[n] で終わります。

この問い合わせはすべての従業員の第三四半期の給与を抽出します。

SELECT pay_by_quarter[3] FROM sal_emp;

 pay_by_quarter
----------------
          10000
          25000
(2 rows)

また、配列や副配列の任意の縦方向の部分を切りだすこともできます。一次元以上の配列についてその一部を表現するには、lower-bound:upper-bound と記述します。たとえばこの問い合わせは Bill のその週の初めの 2 日 に最初なにが予定されているかを抽出します。

SELECT schedule[1:2][1:1] FROM sal_emp WHERE name = 'Bill';

      schedule
--------------------
 {{meeting},{""}}
(1 row)

次のように書くこともできます。

SELECT schedule[1:2][1] FROM sal_emp WHERE name = 'Bill';

結果は同じです。配列の添字に対する演算は、どれかひとつでも添字が lower:upper という形式で書かれていると、配列の一部を表していると想定します。一つの値だけが指定される場合この例のように任意の添字について下限を 1 と仮定します

SELECT schedule[1:2][2] FROM sal_emp WHERE name = 'Bill';
         schedule
---------------------------
 {{meeting,lunch},{"",""}}
(1 row)

array_dims 関数で任意の配列値の現在の次元を取り出せます。

SELECT array_dims(schedule) FROM sal_emp WHERE name = 'Carol';

 array_dims
------------
 [1:2][1:1]
(1 row)

array_dims 関数は、text 型で結果を返します。人間が結果を見るためには便利ですが、プログラムにとってはあまり都合がよいとは言えないかもしれません。次元は array_upper と array_lower でも抽出することができ、それぞれ特定の配列の次元の上限と下限を返します。

SELECT array_upper(schedule, 1) FROM sal_emp WHERE name = 'Carol';

 array_upper
-------------
           2
(1 row)

8.10.4. 配列の変更

配列の値をすべて置き換えることができます。

UPDATE sal_emp SET pay_by_quarter = '{25000,25000,27000,27000}'
    WHERE name = 'Carol';

もしくは ARRAY 演算構文を用いて次のように書きます。

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

保存されている配列の値は既にある配列要素の直後に要素を割り当てるか、すでにあるデータの後もしくは上書きするかたちで一部分を指定することにより拡張することができます。たとえば、myarray配列の要素が現在四つあるとし、array[5] を指定するとその配列の要素は五つになります。現在では、このような配列の拡張は一次元配列でのみ可能となっていて、多次元配列ではできません。

配列の一部の指定で 1 始まり以外の添字がある配列を作れます。例えば添字が -2 から 7 までの値をもつ配列を array[-2:7] で指定できます。

新規の配列の値は連結演算子||を用いて作成することもできます。

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)

連結演算子を使うと一次元配列の最初もしくは最後に一つの要素を押し込むことができます。更には二つの N-次元配列もしくは N-次元配列と N+1-次元配列にも対応しています。

一つの要素が一次元配列の初めに押し込まれた場合、結果は右側演算項目の下限添字から 1 を差し引いた数に等しい下限添字を持った配列となります。一つの要素が一次元配列の最後に押し込まれた場合、結果は左側演算項目の下限を引き継いだ配列となります。次に例をあげます。

SELECT array_dims(1 || ARRAY[2,3]);
 array_dims
------------
 [0:2]
(1 row)

SELECT array_dims(ARRAY[1,2] || 3);
 array_dims
------------
 [1:3]
(1 row)

等しい次元を持った二つの配列が連結された場合、結果は左側演算項目の外側の次元の下限添字を引き継ぎます。結果は右側被演算子のすべての要素に左側被演算子が続いた配列となります。例をあげます。

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-次元配列が N+1-次元配列の最初または最後に押し込まれると結果は上記と似通った要素配列になります。それぞれの N-次元副配列は本質的に N+1-次元配列の外側の次元の要素となります。例をあげます。

SELECT array_dims(ARRAY[1,2] || ARRAY[[3,4],[5,6]]);
 array_dims
------------
 [0:2][1:2]
(1 row)

配列は array_prepend、array_append、もしくは array_cat を使って構築することもできます。初めの二つは一次元配列のみに対応していますが、array_cat は多次元配列でも使えます。ここで説明した結合演算子はそれぞれの関数を直接叩くのが望ましいことを覚えておいてください。事実それらの関数の主な目的は連結演算子の実装に使用することです。とは言ってもユーザ定義の集約関数を作る時そのまま使えます。例を上げます。

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.10.5. 配列内の検索

配列内のある値を検索するには配列のそれぞれの値を検証しなければなりません。もし配列の大きさがわかっているならば手作業でも検索できます。例をあげます。

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;

とは言ってもこの方法では大きい配列では大変な作業となりますし、配列の大きさが不明な場合この方法は使えません。代わりになる方法が 項9.17 で説明されています。上の問い合わせは以下のように書くことができます。

SELECT * FROM sal_emp WHERE 10000 = ANY (pay_by_quarter);

さらに配列で行の値がすべて 10000 に等しいものを見つけることもできます。

SELECT * FROM sal_emp WHERE 10000 = ALL (pay_by_quarter);

8.10.6. 配列の入出力構文

配列の値の外部表現は配列の要素の型に対する I/O 変換ルールに基づいて翻訳された項目と配列の構造を示す装飾項目で構成されています。装飾は配列の値を中カッコ ({ と }) で囲んだものとつぎの項目との間を区切り文字で区切ったものです。区切り文字は通常カンマ (,) ですがほかの文字でもかまいません。配列の要素の型 typdelim を設定することで決まります。（PostgreSQL 配布物における標準のデータ型の中で box 型はセミコロン（;）を使いますがそのほかすべてはカンマを使っています。）多次元配列ではそれぞれの次元（列、面、立体など）はそれ自身の階層において中カッコと、同じ階層の中カッコで括られたつぎの塊との間に区切り文字が書かれていなくてはいけません。カッコの左側や右側の前後、あるいは独立したどんな文字列の項目の前に空白を入れてもかまいません。 項目の後に挿入された空白は無視されませんが、空白を読み飛ばした後ではつぎの右カッコか区切り文字が来るまでのすべては項目の値と見なされます。

上で示したように配列の値を記載する時はすべての個別の配列要素を二重引用符で括らなければなりません。もしも配列の値が配列の値の構文解析に混乱を与えるようであれば 必ずそうしなければなりません。例えば中カッコやカンマ（あるいはいかなる区切り文字でも）、二重引用符、バックスラッシュあるいは空白が前に付いている要素は二重引用符で括られなければなりません。配列要素の中に二重引用符もしくはバックスラッシュを挿入する場合はその前にバックスラッシュを付けます。別の方策として配列構文ととらえられそうなすべてのデータ文字を保護したい場合や無視してもよい空白のためにはバックスラッシュでエスケープします。

空の文字列や中カッコや区切り文字、二重引用符、バックスラッシュあるいは空白が含まれていると要素の値は配列出力処理で二重引用符で括られます。要素の値に組み込まれている二重引用符とバックスラッシュはバックスラッシュでエスケープされます。数値データ型に対しては二重引用符が出現しないと想定するのが安全ですが、テキストデータ型の場合引用がある場合とない場合に対処できるようにしておくべきです。（これは PostgreSQL リリース 7.2 以前からの振舞の変更です。）