2007年07月01日 - mitc

▼ 2007/07/01(日) PostgreSQLのトリガで更新日時を自動挿入してみた

2007/07/01 12:56 【データベース】miff

環境
ServerOS	CentOS5
ClientOS	Windows Vista
PostgreSQL	8.1.9
PgAdmin3	1.6.3

■トリガ

ストアドプロシージャって聞いたことがありますか？
イメージ的には，データベースに内蔵する関数って感じです．
普通データベースに対する処理は，外部からSQLなんかでクエリを発行してDBMSに解釈させ，結果を取得する流れになります．
ストアドプロシージャを使うと，データベース内部に命令を書いておいて外部からそれを起動することができます．
これによるメリットは，Wikipediaを見てください．

トリガは，イメージとしては,UPDATEやINSERTなど何らかの操作が行われた時にストアドプロシージャを起動するようにする仕組みです*1．
これが何の役に立つかということなのですが，例えば20070521のように8桁の文字列*2)で日付を表しているカラムにINSERTやUPDATEで値を入れる時にその値をチェックして日付として有効でないならエラーを出すとか*3，今回の日記タイトルのように行に対する更新があった場合，その更新日時も記録しておくような仕組みを作る時などに便利です*4．

*1 : 厳密に言うと違うようですが，ここではイメージ重視です．

*2 : CHAR(8

*3 : 日付型ならエラーが出ても，普通は文字型ならエラーが出ません

*4 : 何しろどのようなことでもできてしまうので，私の貧しい発想力ではこの程度しか思いつきませんが，しかし日付の自動更新だけでもかなり便利でした．

■トリガ記述言語

実際は，トリガ記述言語という言語があるわけではありません．
ここでは，PostgreSQLでトリガを使うのに必要な言語という意味でこの言葉を使っています．
PostgreSQLでは，主に次の言語を使ってストアドプロシージャを書けます．

PL/pgSQL	SQL手続き言語
PL/Tcl	Tcl手続き型言語
PL/Perl	Perl手続き言語
PL/Python	Python手続き言語

どれを使えばいいのかですが，とりあえずPL/pgSQLが無難だと思います．
少なくとも，本日の日記ではPL/pgSQLを採用しました．

■PgAdmin3によるトリガの設定

更新日時自動挿入トリガの作成を例に挙げて説明します．

テーブル

sample

カラム名	データ型
id	integer primary key
comment	text
update_date	timestamp
register_date	timestamp

このテーブルにデータをINSERTした時，register_dateにその時点の日時を入れます．
そして，UPDATEするたびにupdate_dateにその時点の日時を入れます．
INSERT時は初回の更新処理とも取れるため，INSERT時にもupdate_dateも埋めることとします．

トリガ関数作成用言語の登録

初期状態では，C言語とSQLしか選択できないので，PL/PgSQLを使えるようにします．

手続き言語を右クリックして「新しい手続き言語」を選択．

ダイアログボックスで「plpgsql」を選択してOKを選択．

追加されました．

トリガー関数の作成

トリガー関数は，トリガから呼び出す関数です．
ここが，トリガに関する処理のメインの部分になります．

(デフォルトだと)[スキーマ→public→トリガー関数]を右クリックして「新しいトリガー関数」を選択．

トリガー関数名とオーナー，手続き言語を選択．手続き言語は，ここではplpgsqlを選択．

パラメータタブを選択し，定義欄にソースコードを書く．
今回は，次のように書く．(解説は次の項で)

    BEGIN
	IF TG_OP = 'INSERT' THEN
		NEW.update_date := current_timestamp;
		NEW.register_date := current_timestamp;
	ELSE
		IF TG_OP = 'UPDATE' THEN
			NEW.update_date := current_timestamp;
		END IF ;
	END IF ;
	RETURN NEW;
    END ;

注意すべきは，BEGINからENDまでしか記述しないこと．
他の部分は，pgAdmin3が勝手に生成して付け足すので書いてはいけません．
これでOKを選択して確定しましょう．

トリガの追加

トリガを設定したいテーブルのトリガーを右クリックして「新しいトリガー」を選択．

任意のトリガ名を付け，トリガー関数を選択します．
発動タイミングは，BEFOREを選択，イベントは今回はINSERTとUPDATEの時に自動更新を行いたいので，その二つにチェックを入れます．
OKを選択して確定．

登録できました．

試してみる

データビュー辺りでupdate_dateとregister_dateは空白のままで直接値を入れてみて，更新時に自動的に更新されるかを確認します．
INSERTについては，更新を確定した時点で値が入っているのがわかりますが，UPDATEについては(データビューで見るときは)いったん更新しないと更新されているのがわからないので注意です．

■簡単な解説

pgAdmin3を使わないでトリガ関数をデータベースに追加するには，次のようなスクリプトを書きます*5．

-- コメントの書き方はこう
-- Function: trigger_function_date_updater()

-- DROP FUNCTION trigger_function_date_updater();

-- CREATE OR REPLACEで既に同名のトリガー関数が存在していた場合に対処
CREATE OR REPLACE FUNCTION trigger_function_date_updater()
-- 関数の中身がどこからどこまでか明示する方法は何通りかある
-- この場合はASを使って$BODY$～$BODY$であると定義している．Perlのヒアドキュメントみたいな指定方法
  RETURNS "trigger" AS
$BODY$    BEGIN
	-- TG_OPは，スクリプト起動時に自動的にセットされる定数の一つ
	-- トリガがどのような文脈で起動されたかを格納している
	IF TG_OP = 'INSERT' THEN
		-- NEWオブジェクトを操作して値をDBに反映させる
		NEW.update_date := current_timestamp;
		NEW.register_date := current_timestamp;
	ELSE
		IF TG_OP = 'UPDATE' THEN
			NEW.update_date := current_timestamp;
		END IF ;
	END IF ;
	-- 操作済みNEWオブジェクトを返す．DBはこれを使ってデータを更新するのでデータベースに反映される
	RETURN NEW;
    END ;
$BODY$
  LANGUAGE 'plpgsql' VOLATILE;
ALTER FUNCTION trigger_function_date_updater() OWNER TO test;

大体はソースコード中にコメントで記述した通りです．
NEWオブジェクトだけはわかりにくいですが，
これは，INSERTやUPDATEをかけた時にその対象になったレコードを表しています．
スクリプト起動時に，INSERTならINSERTで新しく値を埋め込むために用意された行，UPDATEなら，WHERE句の指定により対象になった行がNEWオブジェクトにセットされます．

列の指定は次のように行います．