【PostgreSQLのテーブル監視】データの処理を検知してCSVファイルに出力する方法

ホリ

常時テーブルを監視してデータをファイルに出力したいけど、どうやってやるの？

今回はこういった疑問に答えていきます。

システム間でデータを連携する場合、方法は大きく分けて２通りあります。

データベースのデータを共有（データ連携）する方法とデータをファイルに書き出して共有（ファイル連携）する方法です。

今回は、PostgreSQLでテーブルを常時監視して、データをCSVファイルに出力する方法を『A5:SQL Mk-2』を使って紹介します。

トリガとトリガプロシージャを定義することで実現することができます。

トリガとは

監視するデータベースのテーブルに対してデータの新規作成や更新、削除など何らかの処理が発生した場合に検知することができる機能です。

トリガプロシージャとは

データベースに対する命令文（SQL）を関数として定義しておくことができる機能です。

外部からクエリを発行するのと同じ手順で実行することができます。

実装方法

トリガで監視するテーブルのデータに対する処理の発生を検知して、ストアドプロシージャであらかじめプログラムしているCSV出力を実行します。

トリガの定義方法

指定したテーブル（facilities）に対してデータの新規登録（insert）が１行あったタイミングで関数（csv_output）が実行されるようにトリガ（test_trigger）を定義しています。

CREATE OR REPLACE TRIGGER test_trigger
AFTER INSERT 
ON facilities FOR EACH ROW
EXECUTE PROCEDURE csv_output()

新しいトリガを定義すると下の赤枠部分に表示されます。

トリガプロシージャの定義方法

新規登録のあった行を保有する新しいテーブル（csv_output）を作成して、記録していきます。

併せて、コピーコマンドを実行して、新規登録のあった行を任意のディレクトリにcsvファイルを出力します。

CREATE OR REPLACE FUNCTION csv_output() 
RETURNS trigger AS $$
BEGIN
    INSERT INTO csv_output VALUES (NEW.タイムスタンプ, NEW.センサー１, NEW.センサー２, NEW.センサー３);
	COPY (SELECT * FROM csv_output ORDER BY タイムスタンプ DESC LIMIT 1)
	TO 'C:\Users\ユーザー名\Documents\csv-output.csv'
	WITH CSV HEADER DELIMITER ',' FORCE QUOTE *;
	RETURN NEW;
END;
$$ LANGUAGE plpgsql;

新しいトリガプロシージャを定義すると下の赤枠部分に表示されます。

動作確認（テスト）

定義したトリガとトリガプロシージャが正常に動作するかの確認を行います。

正常に動作した場合は、以下のようなCSVファイルが出力されます。

まとめ

トリガプロシージャの書き方はpostgreSQLユーザー会の文書などを参考にしています。

PostgreSQL 8.1.9文書

『A5:SQL Mk-2』のインストール方法は以下の記事でまとめています。

PostgreSQLをインストールして、A5:SQL MK-2から接続する方法

postgreSQL SQL