Postgresqlの自動更新カラムについて
PostgresqlのカラムはON UPDATEは無いのでFunctionsでトリガー関数を設定して
各テーブルの自動更新したいカラムに設定する必要があります。
自動更新カラムのトリガー関数の作成
全体のSQL
今回はINSERT時にcreated_atとupdated_atをCURRENT_TIMESTAMPに設定
(column_defaultで設定もできます)
UPDATE時であれば、updated_atをCURRENT_TIMESTAMPに設定をしています
--同名の関数があれば上書き、なければ新規作成
CREATE OR REPLACE FUNCTION public.set_timestamps()
RETURNS trigger --トリガー関数として宣言
LANGUAGE plpgsql --postgresql言語を使用
AS $function$ --関数の開始地点
BEGIN --関数開始
IF TG_OP = 'INSERT' THEN --INSERTされたら
NEW.created_at := COALESCE(NEW.created_at, CURRENT_TIMESTAMP); --新規レコードのcreated_atがnullならCURRENT_TIMESTAMPをcreated_atに返す
NEW.updated_at := COALESCE(NEW.updated_at, CURRENT_TIMESTAMP); --新規レコードのupdated_atがnullならCURRENT_TIMESTAMPをupdated_atに返す
ELSIF TG_OP = 'UPDATE' THEN --UPDATEされたら
NEW.updated_at := CURRENT_TIMESTAMP; --CURRENT_TIMESTAMPをupdated_atに入れる
END IF; --IF終了
RETURN NEW; --更新したレコードをリターン
END; --関数終了
$function$ --関数の終了地点
それぞれ解説
--同名の関数があれば上書き、なければ新規作成
CREATE OR REPLACE FUNCTION public.set_timestamps()
RETURNS trigger --トリガー関数として宣言
LANGUAGE plpgsql --postgresql言語を使用
コメントにも記載していますが、最初のCREATE OR REPLACE FUNCTIONで関数を作成 or 上書きをしています、次にRETURNSで関数をトリガー関数として宣言します。
最後にLANGUAGEでpostgresql言語を使用する事を設定して頭の部分は終了です。
AS $function$ --関数の開始地点
BEGIN --関数開始
IF TG_OP = 'INSERT' THEN --INSERTされたら
NEW.created_at := COALESCE(NEW.created_at, CURRENT_TIMESTAMP); --新規レコードのcreated_atがnullならCURRENT_TIMESTAMPをcreated_atに返す
NEW.updated_at := COALESCE(NEW.updated_at, CURRENT_TIMESTAMP); --新規レコードのupdated_atがnullならCURRENT_TIMESTAMPをupdated_atに返す
ELSIF TG_OP = 'UPDATE' THEN --UPDATEされたら
NEW.updated_at := CURRENT_TIMESTAMP; --CURRENT_TIMESTAMPをupdated_atに入れる
END IF; --IF終了
RETURN NEW; --更新したレコードをリターン
END; --関数終了
$function$ --関数の終了地点
次に関数の実装の中身ですがこちらは長いので1つずつ解説します。
1.AS $function$でラベルの開始を設定します(ここはmy_funcとかでもなんでもOKです)
2.関数の開始をBEGINで書いて開始します。
3.IF TG_OP = 'INSERT' THENでINSERT文が流れてきた時にIFの処理をします(NEWはSQLが実行されているレコードです)
3-1. NEW.created_at := COALESCE(NEW.created_at, CURRENT_TIMESTAMP);COALESCEを挟んでcreated_atがあれCURRENT_TIMESTAMPを入れて、あればそのままにして作成します。
3-2.NEW.updated_at := COALESCE(NEW.updated_at, CURRENT_TIMESTAMP);3-1と同じく、updated_atを更新をします。
4.ELSIF TG_OP = 'UPDATE' THENINSERT以外でUPDATEであればELSIFの処理をします。
4-1.NEW.updated_at := CURRENT_TIMESTAMP;更新した日付をレコードに上書きをします。
5.END IF;IFブロックの終了を書きます。
6.RETURN NEW;更新したレコードをリターンします。
7.関数の終了をEND;で書いて終了します。
8.$function$でラベルの終了を設定します。
あとがき
Postgresql以外に触れていなかったので、これが自動更新カラムの作り方なのかと思っていたらMySQLだとON UPDATEで出来るようで他のDBは便利だな〜と思いました。
(よくよく考えるとcolumn_defaultがあるのになんでcolumn_updateは無いのか...)
Discussion