PostgreSQL覚書 Triggerあれこれ

10年前にOracleで作成して以来、久しぶりにトリガーを作成してみた。
しかも、PostgreSQLで。

いろいろできなかったことがあったので調べてみたが、結局わからないところは代替案（要するに適当）で作った。
トリガー作成なんて、今後もあまりやらないだろうから、覚書は細かく書いておくことに。

まず、Functionを作ってからTriggerを作成。

--------------------------------------------------------
-- 走らせる処理はこっちに書く(言語はplpgsql)
--------------------------------------------------------
CREATE OR REPLACE FUNCTION SET_XX_FUNC()
  RETURNS trigger AS $$
    DECLARE
        -- 変数定義 --
    BEGIN
        -- 処理 --
    END;
$$ LANGUAGE ’plpgsql’;

--------------------------------------------------------
-- {table}に設定したテーブルに行が挿入、または行更新された後に処理を走らせる
--------------------------------------------------------
CREATE OR REPLACE TRIGGER SET_XX
 AFTER INSERT OR UPDATE ON {table} FOR EACH ROW
 EXECUTE PROCEDURE SET_XX_FUNC();

さて、ここまでは良い。
こっからだ。

--------------------------------------------------------
--★ 変数の設定
--------------------------------------------------------
DECLARE item_a int;
DECLARE item_b int ：=999 ;
item_a := 1;

＞＞1つ目は変数のみ定義。
＞＞2つ目は変数定義と同時に初期値を設定する方法。
＞＞3つ目は定義した変数に値を入れる方法。

--------------------------------------------------------
--★ 情報の取得方法その１（普通のSELECT文）
--------------------------------------------------------
DECLARE item_a int;
DECLARE item_b int;
DECLARE item_c int;
-- (中略) --
SELECT a,b,c INTO item_a,item_b,item_c 
FROM {table} WHERE key1=NEW.key1 ;

＞＞SELECT文で取得したデータを変数に入れる場合の書き方はこんな感じ。
　　SELECT a INTO item_a, b INTO item_b … ってな書き方はNG。
＞＞トリガーで更新・挿入したデータの情報を取得する場合は、NEW.{カラム名}で。

--------------------------------------------------------
--★ 情報の取得方法その２（Executeを利用したSELECT文）
--------------------------------------------------------
DECLARE add_months text = ’2months’;
DECLARE next_kigen text = ’’;
-- (中略) --
EXECUTE ’SELECT TO_CHAR(
　　　　　　　　　　　DATE_TRUNC(’’month’’
　　　　　　　　　　　　　　　　, CURRENT_TIMESTAMP 
	               || ’ + interval ’ || quote_literal(add_months) || ’)’ 
	               || ’ + interval ’’-1days’’,’’YYYYMMDD’’)’
 INTO next_kigen; 

＞＞普通のSELECT文ではどうしても無理なものは、文字列のSELECT文を実行することになる。
　　上の例は、現在日時から翌月末の日付(+2ヶ月-1日)を取得する方法。
　　add_monthsがころころ変わる場合（3か月後、4か月後と）、普通のSELECT文ではどうしてもできなかった。
　　あとは、サブクエリ―を使う場合もSELECT文では書けないと思っておいたほうがよい。
　　（テーブル別名＝エイリアスが使えないので。）
　　なので、文字列を生成して
　　　　　　　EXECUTE 文字列 INTO 変数名;
　　で検索結果をINTOの後ろの変数に格納させる。
　　文字列内のシングルクォーテーションは’’と、2連続にすること。
＞＞quote_literal関数はよくわからないが、変数の型にあったquateを設定すると思う。
　　文字列の間で変数を使いたいときは、必ずこの関数を使う。（使わんかったら動かんかったし）
　　ちなみに、DATE_TRUNC(‘month’,{date_type})は月初を求めるときの関数。

--------------------------------------------------------
--★ 更新後の{item1}が0以下なら処理をやめる（条件文の例）
--------------------------------------------------------
IF NEW.{item1} <= 0 THEN
　　RETURN NEW;
END IF;

＞＞途中で処理を抜ける処理の書き方がイマイチわからなかったので、とりあえずRETURN NEWしてる。
　　（正しい方法を教えて！！）

--------------------------------------------------------
--★ 検索結果がないときの判定
--------------------------------------------------------
IF NOT FOUND THEN
　　RETURN NEW;
END IF;

＞＞なのだが、これってカーソル処理やFOR文でしか作動しないように思う。

——————————————————–
–★ OLDとNEW
——————————————————–
OLDは更新前のレコードで、NEWは更新後のレコード。
NEW.{カラム名} という感じで使う。
なので、DELETEのトリガーの場合はOLDしかないし、INSERTの場合はNEWしかない。
今回は、AFTER INSERT OR UPDATE ～つまり更新・挿入後に行う～の処理なので、NEWしか使っていない。

衰えた脳みそをフル活用させて1日がかりで作ったのだが、うまく動かなかった　・・・・

で、デバッグ。
本当はログ出力して経過をみたかったのだけれど、ログ出力の仕方がよくわからないし、テスト環境と本番環境で異なる設定をしてもなぁ・・・と思って、動作途中で強制エラーを発生させることにした。

--------------------------------------------------------
--★ RAISE EXCEPTION（強制的に例外を発生させる）
--------------------------------------------------------
DECLARE add_months text = ’2months’;
-- (中略) 
RAISE EXCEPTION '% ここまで終わったぜ', add_months;

＞＞エラーメッセージに「2months ここまで終わったぜ」と表示される。
　　（%は可変値で、2つ目以降の引数で設定する。Cとかの%dとかと同じ）
　　なので、とりあえず、ここまでは走ってるよなぁ・・・ということで、
　　次はこの例外発生処理を後ろに移動させてから実行する。
　　これを何度か繰り返して、なんとか思い通りに動作するところまで行った。

とりあえず、直近で必要な情報はこんなところ。
今回はカーソルとかは使わなかったので、使うときはまた追記なり投稿なりしようと思う。
EXECUTEとかの使い方が難しいし、面倒だった。

まぁ、こんだけ書いておけば、次回はさほどオロオロすることもないだろう。