PostgreSQLで複合型のカラムを使ってみる

DB

前回の配列型に続き、今度は複合型を使ってみる。
この複合型というのは複数のフィールドの組を一つのデータ型として扱える、ちょうどC言語の構造体のような機能となる。


前回の記事：PostgreSQLで配列型のカラムを使ってみる - ぱせらんメモ

何はともあれデータ型を作成する

SQLはこんな感じ。

CREATE TYPE shop_item AS (
    name     varchar(100),  -- 商品名
    category varchar(20),   -- カテゴリ名
    price    numeric        -- 価格
);

CREATE TABLEと似たような感じだが、型名と定義の後にASが必要なことと、現在のところ制約（NOT NULLなど）がつけられないという点が異なる。

作成したデータ型を利用したテーブルを作成する

CREATE TABLE stocks (
    item     shop_item,     -- 商品
    count    integer        -- 在庫数
);

プライマリキー制約をつけようと思ったら次のようなエラーが出てしまった。

ERROR:  data type shop_item has no default operator class for access method "btree"
HINT:  You must specify an operator class for the index or define a default operator class for the data type.

どうやらshop_itemに対する演算子を定義してやらなければならないらしい。
難しそうなので今回は断念。

データを入れてみる

テーブルが作成できたので早速データを投入してみる。

INSERT INTO stocks (item, count)
    VALUES ('(オレンジ,果物,100)', 10);

配列の時と同じように括弧の中に項目をカンマ区切りで並べて指定する。やはり全体をシングルクォートで括ってあげる必要がある。カンマや括弧などが含まれる場合はさらに個々の要素をダブルクォートで括る必要がある。
配列のときのARRAY構文のようにROW構文を使った書き方も出来る。こちらのほうが個々の要素をクォートで括れるので、感覚的にはわかりやすい。

INSERT INTO stocks (item, count)
    VALUES (ROW('りんご', '果物', 120), 20);

ROWの中に二つ以上のフィールドがある場合はROWキーワードを省略して('りんご', '果物', 120)だけを書くことも出来る（例は割愛）。

データを抽出してみる

データも作成できたので、今度はそれを取り出してみる。

SELECT item, count FROM stocks;

        item         | count
                                                        • -
(オレンジ,果物,100) | 10 (りんご,果物,120) | 20 (2 rows)

このような感じで出てくる。
複合型の特定のフィールドにアクセスする場合はドットとフィールド名で指定する。但し、この構文はテーブル.フィールドと同じであるため、括弧を使用しなければならない場合がある。

[括弧をつけなかった場合]

SELECT item.name, count FROM stocks;

ERROR:  missing FROM-clause entry for table "item"
LINE 1: SELECT item.name, count FROM stocks;

[括弧をつけた場合]

SELECT (item).name, count FROM stocks;

   name   | count
                                  • -
オレンジ | 10 りんご | 20 (2 rows)

検索してみる

WHERE句を指定して検索してみる。

SELECT item, count FROM stocks
    WHERE (item).price > 100;

       item        | count
                                                    • -
(りんご,果物,120) | 20 (1 row)

配列とは異なり複合型のフィールドをまとめて扱うようなことは出来ない。

SELECT item, count FROM stocks
    WHERE (item).name LIKE 'オ%';

        item         | count
                                                        • -
(オレンジ,果物,100) | 10 (1 row)

配列の時とは違いLIKEも正常に動作する。
まぁ集合(?)に対する操作ではないので当然だが……。

更新してみる

複合型のデータを更新してみる。

UPDATE stocks SET item = '(パイナップル,果物,260)'
    WHERE (item).name = 'りんご';

[更新後]
          item           | count
                                                                • -
(オレンジ,果物,100) | 10 (パイナップル,果物,260) | 20 (2 rows)

上の例はitemを丸ごと変更する例。INSERTの時と同様にROW構文を使うことも出来る（例は省略）。
次のように配列の特定の要素だけ更新することも出来る。

UPDATE stocks SET item.price = 240
    WHERE (item).name = 'パイナップル';

[更新後]
          item           | count
                                                                • -
(オレンジ,果物,100) | 10 (パイナップル,果物,240) | 20 (2 rows)

ここで注意したいのはSETで指定するフィールドの部分で、ここでは括弧で括る必要はない。というか括るとエラーになる。

ホスト言語からの利用

複合型をホスト言語で扱った場合に果たしてどうなるのか。
今回は調べてもいないのだが、恐らく配列の時と同様に(オレンジ,果物,100)という文字列で返ってくるのではないかと思われる。


Javaの場合はjava.sql.ResultSet#getMetaData()で調べてみたところ、java.sql.Types.OTHER型として認識されていた。

結局複合型はどうなのか

やはり配列の時と同様に別テーブルに括り出すのが素直だと思うので、特別な理由でもない限りは必要ないと思われる。
ホスト言語から見ても扱いにくそうなのは明らかだし。


■参考
http://www.postgresql.jp/document/pg823doc/html/rowtypes.html（日本PostgreSQLユーザ会 | 日本PostgreSQLユーザ会）