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で次元数や大きさを宣言することは、単なるコメントで、実行時の動作に影響を及ぼすものではありません。

SQLに準拠したもう1つの構文を一次元配列に使うことができます。pay_by_quarterを次のように定義することもできます。

    pay_by_quarter  integer ARRAY[4],

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

8.10.2. 配列の値の入力

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

'{ val1 delim val2 delim ... }'

ここでdelimはそのpg_typeエントリに記録されている型の区切り文字です。PostgreSQL配布物で提供されている標準データ型のうち、box型はセミコロン（;）を使用しますが、残り全てはカンマ（,）を使います。それぞれのvalは配列要素の型の定数か副配列です。配列定数の例を以下に示します。

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

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

配列定数の要素をNULLとするためには、その要素値にNULLと記載してください。 （NULLを大文字で書いても小文字で書いても構いません。） "NULL"という文字列値を指定したければ、二重引用符で括って記載しなければなりません。

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

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

上に記載した2つの挿入文の結果は次のようになります。

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)

ARRAY生成子構文も使えます。

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

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

多次元配列では、各次元の範囲を合わせなければなりません。 以下のように、一致しないとエラーが報告されます。

INSERT INTO sal_emp
    VALUES ('Bill',
    '{10000, 10000, 10000, 10000}',
    '{{"meeting", "lunch"}, {"meeting"}}');
ERROR:  multidimensional arrays must have array expressions with matching dimensions

8.10.3. 配列へのアクセス

ではテーブルに対していくつかの問い合わせを行ってみましょう。初めに、1回ずつ配列の単一要素にアクセスする方法を示します。この問い合わせは第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)

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

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

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

もしどんな次元でも部分として記述されると、つまりコロンを含んだ形式ですが、全ての次元は部分として取り扱われます。次元が欠けている場合は [1:1] と想定されます。次元が（コロンなしの）単一数字の場合、次元は 1 から指定された数字までと扱われます。例えば、[2] は以下の例のように [1:2] と扱われます。

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

                 schedule
-------------------------------------------
 {{meeting,lunch},{training,presentation}}
(1 row)

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

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

                 schedule
-------------------------------------------
 {{meeting,lunch},{training,presentation}}
(1 row)

配列自体がNULLもしくはその添え字式がNULLとなる場合、配列添え字式はNULLを返します。 また、配列の範囲を超える添え字の場合もNULLが返されます（この場合はエラーになりません）。 たとえば、scheduleが現在[1:3][1:2]次元であれば、schedule[3][3]の参照はNULLとなります。 同様にして、添え字として間違った値を指定して配列を参照した場合もエラーではなく、NULLが返されます。

同様に、部分配列式も配列自体がNULLもしくはその添え字式がNULLとなる場合にNULLを返します。 しかし、現在の配列範囲を完全に超えた部分配列を選択するといった場合では、部分配列式はNULLではなく空の（0次元）の配列を返します。 要求された部分配列が配列の範囲に重なる場合、警告なく重なる部分だけに減少させます。

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

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

 array_dims
------------
 [1:2][1:2]
(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';

配列の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';

保存されている配列の値は、存在しない要素に代入することで拡張することができます。 過去に存在した位置と新しく代入された位置との間はNULLで埋められます。 たとえば、現在配列myarrayの要素数が4だとします。 myarray[6]に代入するという更新を行うと、6要素を持つことになり、myarray[5]にはNULLが割り当てられます。 現在、こうした方法での拡張は、1次元配列でのみ許されます。 多次元配列では行うことができません。

添え字指定の代入で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)

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

1つの要素が1次元配列の先頭や末尾に押し込まれた時、結果は配列演算項目と同じ下限添え字を持つ配列となります。 以下に例を示します。

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)

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

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
------------
 [1:3][1:2]
(1 row)

配列はarray_prepend、array_append、もしくはarray_catを使って構築することもできます。初めの2つは一次元配列にしか対応していませんが、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型はセミコロン（;）を使いますがその他全てはカンマを使っています）。多次元配列ではそれぞれの次元（列、面、立体など）はそれ自身の階層において中括弧、同じ階層の中括弧で括られた次の塊との間に区切り文字が書かれていなければなりません。

空の文字列や中括弧や区切り文字、二重引用符、バックスラッシュ、空白、NULLという単語が含まれていると、配列出力処理は要素の値を二重引用符で括ります。 要素の値に組み込まれている二重引用符とバックスラッシュはバックスラッシュでエスケープされます。 数値データ型に対しては二重引用符が出現しないと想定するのが安全ですが、テキストデータ型の場合引用がある場合とない場合に対処できるようにしておくべきです。

デフォルトでは配列の次元の下限インデックス値は1に設定されています。 他の下限値を持つ配列を表現したければ、配列定数を作成する前に明示的に配列添え字範囲を指定することで実現できます。 修飾項目はそれぞれの配列次元の上限と下限をコロン（:）で区切って前後を大括弧（[]）で括った形式になっています。 代入演算子（=）の後に配列次元修飾項目が続きます。例を示します。

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とは異なる下限を持つ場合にのみ、配列出力関数はその結果に明示的な次元を含めます。

要素に指定された値がNULL（またはその亜種）の場合、要素はNULLとして扱われます。 引用符やバックスラッシュがあると、これは無効となり、"NULL"という文字列リテラルを入力することができます。 また、8.2以前のPostgreSQLとの後方互換性のため、array_nulls設定パラメータをoffにして、NULLをNULLとして認識しないようにすることができます。

前に示したように配列に値を書き込む場合は独立した配列要素を二重引用符で括ります。 配列値パーサが配列要素値によって混乱を来さないように必ずこの形式を守ってください。 例えば、中括弧、カンマ（もしくはどんな区切り文字）、二重引用符、逆スラッシュもしくは前後に付いた空白を含む要素は必ず二重引用符で括らなければなりません。 空文字列やNULLという単語自体も同様に引用符で括らなければなりません。 二重引用符もしくは逆スラッシュを引用符付きの配列要素に付け加えたい場合、エスケープ文字列構文を使用し、そしてその直前に逆スラッシュを付けます。別の方法として配列構文とみなされるような全てのデータ文字を逆スラッシュでエスケープしても構いません。

括弧の右側もしくは左側それぞれの前と後に空白があっても構いません。同様に独立した項目の文字列の前後に空白があっても構いません。これら全ての場合において空白は無視されます。とは言っても二重引用符で囲まれた要素の中の空白、もしくは要素の空白文字以外により両側が括られているものは無視されません。