PostgreSQL で 配列をテーブルのように扱う
メモです。
（使用している PostgreSQL は PostgreSQL 9.3.12 です）
PostgreSQL で 配列をテーブルのように扱う方法です。 データの一括登録や、関数のテストなどに便利です。
まず、配列の定義は次のようになります。
'{value1,value2,value3}'
ARRAY 演算構文を使うと次のようになります。
ARRAY['value1','value2','value3']
今回はまず ARRAY 演算構文の方を SQL で使用します。
SELECT field1::VARCHAR
FROM UNNEST(ARRAY['value1','value2','value3']) AS field1;
使いまわしが良いように FROM 句で使用しています。
ポイントは UNNEST 関数です。 UNNEST 関数を使用すると 配列を行集合に展開することができます。
[参考]
9.18. 配列関数と演算子 - PostgreSQL 9.3.2文書
SQL の実行結果は次のようになります。
db=# SELECT field1::VARCHAR db-# FROM UNNEST(ARRAY['value1','value2','value3']) AS field1; field1 -------- value1 value2 value3 (3 rows)
これを使うと 次ように SQL で使用する関数のテスト用 SQL が簡単に作れます。
SELECT field1::VARCHAR, test_function(field1::VARCHAR) AS result
FROM UNNEST(ARRAY['value1','value2','value3']) AS field1;
ここまでは一次元配列でした。
配列をテーブルのように扱うのに 列が 1 つでは困りますね。
ただ、2 次元配列にすると扱いが面倒になるため 配列の文字列定義と ARRAY 演算構文を組み合わせて 次のように定義します。
ARRAY['{100,value1,2018/10/01}','{200,value2,2018/10/02}']
これを SQL で使います。
SELECT (fields::VARCHAR[])[1]::NUMERIC AS field1 , (fields::VARCHAR[])[2]::VARCHAR AS field2 , (fields::VARCHAR[])[3]::DATE AS field3 FROM UNNEST(ARRAY['{100,value1,2018/10/01}' ,'{200,value2,2018/10/02}']) AS fields;
ポイントは "fields" に配列の文字列定義が入るので それを "VARCHAR[]" でキャストします。
SQL の実行結果は次のようになります。
db=# SELECT (fields::VARCHAR[])[1]::NUMERIC AS field1 db-# , (fields::VARCHAR[])[2]::VARCHAR AS field2 db-# , (fields::VARCHAR[])[3]::DATE AS field3 db-# FROM UNNEST(ARRAY['{100,value1,2018/10/01}' db-# ,'{200,value2,2018/10/02}']) AS fields; field1 | field2 | field3 --------+--------+------------ 100 | value1 | 2018-10-01 200 | value2 | 2018-10-02 (2 rows)
値にカンマを使用する場合は 次のようにエスケープします。
ARRAY['{100,val\,ue1,2018/10/01}','{200,value2,2018/10/02}']
