5.12. 配列

PostgreSQL はテーブルの列を可変長多次元の配列として定義することができます。また、既存のデータ型やユーザー定義型の配列を作成することも可能です。実際の配列の使いかたを説明するために、つぎのテーブルを作成します。

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

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

それでは INSERT をいくつか実行してみましょう。 配列に要素の値を追加する際にはその要素の値を中カッコで囲み、それぞれの要素の値をカンマで区切っていることに注意してください。 C 言語を知っているならば、構造体を初期化するための構文のようなものと考えてください。

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"}}');

それでは、sal_emp に対して問い合わせを行ってみましょう。まず、一回にひとつの配列の要素にアクセスするしかたを示します。この問い合わせは第2四半期に給料の変更があった従業員の名前を抽出します。

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] で終わります。

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

SELECT pay_by_quarter[3] FROM sal_emp;

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

また、配列や副配列の任意の一部分を取りだすことも可能です。 1 次元以上の配列について配列の一部を書くには、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 つの値だけが指定される場合、任意の添字について下限を 1 と仮定します。

配列の値をすべて置き換えることも可能です。

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

または 1 つの要素を更新することも可能です。

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

配列は、すでに存在している配列要素の直後、もしくはデータがすでに入っている配列の一部分に上書き/挿入することによって配列の大きさを変更できます。たとえば、配列の要素が現在 4 つあるとし、array[5] を指定するとその配列の要素は 5 つになります。現在では、このような配列の拡張は 1 次元配列でのみ可能となっていて、多次元配列ではできません。

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

CREATE TABLE の構文で固定長の配列を定義できます。

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

とはいっても現在の実装では、配列の最大サイズを強要しません。 大きさを指定していない配列と同じ動作になります。

実際には、現在の実装では次元の宣言も強要しません。配列の大きさや次元数にかかわらず、ある特定の基本型の配列はすべて同じ型であるとみなされます。ですから次元とか大きさの数を CREATE TABLE 宣言したとしてもそれは単なる記述にすぎず、実行時の振舞に影響を与えません。

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

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

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

array_dims 関数は、text 型で結果を返します。 人間が結果を見るためには便利ですが、プログラムにとってはあまり有効ではないかもしれません

配列内である値を検索するには、配列の要素すべてを検索する必要があります。もし配列の大きさがわかっているならば手作業でも検索できます。

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;

とはいってもこの方法では大きい配列では大変な作業となりますし、配列の大きさが不明な場合この方法を実行できません。PostgreSQL の標準配布物に入っていないとはいっても、配列値に対して繰り返し処理を行う新しい関数や演算子を定義する拡張が使えます。これらを使用して、さきほどの問い合わせを以下のように書くことができます。

SELECT * FROM sal_emp WHERE pay_by_quarter[1:4] *= 10000;

配列の(特定の列のみだけではなく)すべての要素を検索するにはつぎのようにします。

SELECT * FROM sal_emp WHERE pay_by_quarter *= 10000;

さらに値が 10,000 と一致する配列の行も下記のようして検索できます。

SELECT * FROM sal_emp WHERE pay_by_quarter **= 10000;

このオプションモジュールをインストールするには PostgreSQL のソースディストリビューションの contrib/array ディレクトリを参照してください。

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