【PostgreSQL】履歴データ追加時にトリガーで最新データをinsertする

2021年5月19日2024年1月13日

履歴データ追加時にトリガーで最新データをinsert

最新価格を保持するテーブルpriceと価格履歴テーブルprice_historyを考えます。

価格履歴を保持するprice_historyテーブルにはinsertのみ行うようにし、最新データを保持するpriceテーブルにはトリガーを使ってinsertします。既に価格データが存在する場合はdeleteしてからinsertします。

トリガーはprice_historyテーブルへinsertを行ったときに実行するようにします。

priceテーブルにinsertするデータはprice_historyテーブルに存在する必要があるためpriceてビルに外部キー制約を付けます。

テーブルとトリガーの作成

以下のようにテーブルとトリガーを作成します。

-- 最新価格を保持するテーブル
create table price (
    id serial,
    product_id int not null,
    price numeric not null,
    price_history_id int not null,
    updated_at timestamp with time zone not null,
    constraint pk_price primary key(id),
    constraint fk_price_history foreign key(price_history_id) references price_history(id)
);

-- 価格履歴を保持するテーブル
create table price_history (
    id serial,
    product_id int not null,
    price numeric not null,
    updated_at timestamp with time zone not null,
    constraint pk_price_history primary key(id)
);

-- 更新日時をセットする関数
create function set_updated_at() returns trigger as $set_updated_at$
begin
    NEW.updated_at = now();
    return NEW;
end;
$set_updated_at$
language plpgsql;

-- 最新価格をセットする関数
create function insert_price() returns trigger as $insert_price$
begin
    if (select exists (select 1 from price where product_id = NEW.product_id)) then
        delete from price where product_id = NEW.product_id;
    end if;
    insert into price (product_id, price, price_history_id) values (NEW.product_id, NEW.price, NEW.id);
    return NEW;
end;
$insert_price$
language plpgsql;

-- 更新日時をセットするトリガーを作成
create trigger set_updated_at_trigger before insert or update on price for each row execute function set_updated_at();
create trigger set_updated_at_trigger before insert or update on price_history for each row execute function set_updated_at();

-- 最新価格をセットするトリガーを作成
create trigger insert_price_trigger after insert on price_history for each row execute function insert_price();

動作確認

price_historyテーブルに対してinsertを行い動作確認を行います。

-- price_historyテーブルへのinsert
$ insert into price_history (product_id, price) values (1, 100);
INSERT 0 1

-- price_historyテーブル
$ select * from price_history;
 id | product_id | price |          updated_at
----+------------+-------+-------------------------------
  1 |          1 |   100 | 2021-05-19 12:55:37.915352+00

-- priceテーブルにデータがinsertされている
$ select * from price;
 id | product_id | price | price_history_id |          updated_at
----+------------+-------+------------------+-------------------------------
  2 |          1 |   100 |                1 | 2021-05-19 12:55:37.915352+00

-- price_historyテーブルへのinsert
$ insert into price_history (product_id, price) values (2, 200);
INSERT 0 1

-- price_historyテーブルへのinsert
$ insert into price_history (product_id, price) values (1, 300);
INSERT 0 1

-- price_historyテーブル
$  select * from price_history;
 id | product_id | price |          updated_at
----+------------+-------+-------------------------------
  1 |          1 |   100 | 2021-05-19 12:55:37.915352+00
  2 |          2 |   200 | 2021-05-19 12:56:19.887407+00
  3 |          1 |   300 | 2021-05-19 12:57:05.06216+00

-- product_id=2のデータが追加されている。
-- product_id=1のデータはdelete後にinsertされている。idが1から4に変化している
$ select * from price;
 id | product_id | price | price_history_id |          updated_at
----+------------+-------+------------------+-------------------------------
  3 |          2 |   200 |                2 | 2021-05-19 12:56:19.887407+00
  4 |          1 |   300 |                3 | 2021-05-19 12:57:05.06216+00

以下の書籍を参考にしました。