Chapter 6. 配列

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

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

上記のSQL文はsal_emp という名のテーブルを作成し、 text 型の (name)、 従業員の四半期の給料を入力するためのinteger型の一次元配列の (pay_by_quarter)、 そして従業員の週間スケジュールを入力するためのtext型の二次元配列の (schedule)を持っています。

それでは、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に対して問い合わせを 行ってみましょう。まず、1度に配列の要素1つをアクセスしてみましょう。 このSQL文は第2四半期に給料の変更があった従業員の名前を検索し、答えます。

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

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

Postgresの要素番号は"1始まり"となっています。 つまり、要素が n 個ある配列はarray[1]から始まり、 array[n]で終ります。

このSQL文はすべての従業員の第3四半期の給料を検索し、答えます。

SELECT pay_by_quarter[3] FROM sal_emp;

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

また、配列やサブ配列の任意の一部分を取りだすことも可能です。 1次元以上の配列の一部分はlower subscript : upper subscriptのように表示します。 このSQL文は Billの週間スケジュールの最初の2日のそれぞれの日の 最初の予定を検索し、答えます。

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

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

もしくはこのようにもSQL文を書くことができます。

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

こちらでも同じ実行結果を得ることができます。配列のsubscripting作業は 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つあるとし、その配列の要素番号[5]に 値を挿入したら、その配列の要素は5つとなります。現在では、このような 配列の拡張は一次元配列でのみ可能となっていて、多次元配列ではできません。

CREATE TABLE のシンタックスでは固定長の配列を 定義することができます。

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

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

実際には、現在の実装では、 次元の宣言も強要しません。配列の大きさや 次元の数に拘らず、ある1つの型の配列はすべて同じ型であると見なされます。

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 の標準ディストリビューションには 入っていませんが、contributionsディレクトリにはPostgreSQLの 拡張関数などが収録されています。これらを使用することにより先程のSQL文は 下記のように書くことができます。

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

配列の特定のカラムのみだけではなく、すべての要素を検索するには 下記のように書くことができます。

SELECT * FROM sal_emp WHERE pay_by_quarter *= 10000;

また、例えば値が10000と一致する配列の列も下記のように検索することができます。

SELECT * FROM sal_emp WHERE pay_by_quarter **= 10000;

この拡張関数をインストールするにはPostgreSQLの ソースディストリビューションのcontrib/arrayディレクトリを 参照して下さい。