PostgreSQL

PostgreSQL-8.4 未満のバージョンで array_agg() 関数を使う

PostgreSQL の array_agg() 関数とは

PostgreSQL-8.4 以上であれば array_agg() という行を配列にしてくれる便利な 関数があるのですが、PostgreSQL-8.3 以下ではこの関数がありません。

しかしこれがないととても不便な場合が多々ありますので、ここでは PostgreSQL-8.3 以下で array_agg() 関数を使えるようにしてみます。

テストデータの準備

まず検証用として PostgreSQL-8.4 と PostgreSQL-8.2 の環境でテスト用のテーブルとデータを登録します。

=> CREATE TABLE colors (id serial, name text NOT NULL);
CREATE TABLE

=> INSERT INTO colors (name) VALUES ('red'),('blue'),('yellow');
INSERT 0 3

=> SELECT * FROM colors;
+----+--------+
| id |  name  |
+----+--------+
|  1 | red    |
|  2 | blue   |
|  3 | yellow |
+----+--------+
(3 rows)

PostgreSQL-8.4 で arrag_agg() 関数を使って行を配列に変換

ではまず PostgreSQL-8.4 で array_agg() 関数を使ってみます。何もせずとも最初から array_agg() 関数が使え、行が配列に変換されています。

=> SELECT name FROM colors;
+--------+
|  name  |
+--------+
| red    |
| blue   |
| yellow |
+--------+
(3 rows)

=> SELECT array_agg(name) FROM colors;
+-------------------+
|     array_agg     |
+-------------------+
| {red,blue,yellow} |
+-------------------+
(1 row)

PostgreSQL-8.4 の array_agg() 関数が存在するか確認してみると、以下のように確かに存在しますね。

[PostgreSQL-8.4]
=> \df array_agg
                            List of functions
+------------+-----------+------------------+---------------------+------+
|   Schema   |   Name    | Result data type | Argument data types | Type |
+------------+-----------+------------------+---------------------+------+
| pg_catalog | array_agg | anyarray         | anyelement          | agg  |
+------------+-----------+------------------+---------------------+------+
(1 row)

今度は PostgreSQL-8.2 で array_agg() 関数があるかどうか調べてみても存在しません・・・。

[PostgreSQL-8.2]
=> \df array_agg
                    List of functions
+--------+------+------------------+---------------------+
| Schema | Name | Result data type | Argument data types |
+--------+------+------------------+---------------------+
+--------+------+------------------+---------------------+
(0 rows)

しかし PostgreSQL では関数は簡単に作成できますので、実際に PostgreSQL-8.2 で array_agg() 関数を作成してみましょう。

PostgreSQL-8.2 で array_agg() 関数を作成

では実際に array_agg() 関数を利用したいデータベースに接続し、以下の CREATE AGGREGATE 文を実行すると array_agg() 関数を作成することが出来ます。

=> CREATE AGGREGATE array_agg(anyelement) (
SFUNC=array_append,
STYPE=anyarray,
INITCOND='{}'
);

特に問題なく関数は作成できます。次に実際に作成した array_agg() 関数を呼び出してみましょう。

[まず PostgreSQL のバージョンを表示]
=> SELECT version();
+-----------------------------------------------------------------------------------------------------+
|                                               version                                               |
+-----------------------------------------------------------------------------------------------------+
| PostgreSQL 8.2.19 on i686-pc-linux-gnu, compiled by GCC gcc (GCC) 4.1.2 20080704 (Red Hat 4.1.2-52) |
+-----------------------------------------------------------------------------------------------------+
(1 row)

[通常の SELECT で行データを表示]
=> SELECT name FROM colors;
+--------+
|  name  |
+--------+
| red    |
| blue   |
| yellow |
+--------+
(3 rows)

[行データを配列で表示]
=> SELECT array_agg(name) FROM colors;
+-------------------+
|     array_agg     |
+-------------------+
| {red,blue,yellow} |
+-------------------+
(1 row)

上記のように PostgreSQL-8.3 以下でも array_agg() 関数で行を配列に変換できました。