🚀

postgresでon update current_timestampする

2022/08/18に公開

はじめに

MySQLにはデータ変更時のタイムスタンプを記録するカラム（_updated_atなど）に対してON UPDATE CURRENT_TIMETAMP句を設定しておくことで、タイムスタンプが自動更新される。また、この時明示的に_updated_atカラムの値を指定して行を更新した場合、その値に置換される。

PostgreSQLでは、このON UPDATE CURRENT_TIMESTAMPをサポートしていないので、自分でストアドプロシージャ（関数）・トリガーを設置して機能させる必要がある。
それでは任意のテーブルにON UPDATE CURRENT_TIMESTAMPもどきを実装してみる。

実装

今回の実装では、usersテーブルの_updated_atカラムをデータ更新の度にタイムスタンプの自動更新を行い、明示的に値が挿入された際にはその値に更新することを期待する。

create_users.sql

-- usersテーブル定義
CREATE TABLE users (
    user_id uuid NOT NULL DEFAULT gen_random_uuid(),
    login_id varchar(40) NOT NULL,
    login_password varchar(30) NOT NULL CHECK (length(login_password) > 8),
    _created_at timestamp with time zone NOT NULL DEFAULT current_timestamp,
    _updated_at timestamp with time zone NOT NULL DEFAULT current_timestamp,
    PRIMARY KEY(user_id)
);

create_function_trigger.sql

/*
  関数定義（3つ）
*/
-- _update_atカラムが変更されなかった時、NULLを代入する
CREATE FUNCTION trg_update_timestamp_none() RETURNS trigger AS
$$
BEGIN
  IF NEW._updated_at = OLD._updated_at THEN
    NEW._updated_at := NULL;
  END IF;
  RETURN NEW;
END;
$$ LANGUAGE plpgsql;

-- _updated_atカラムがNULLの時、UPDATE文実行前のタイムスタンプを代入する
CREATE FUNCTION trg_update_timestamp_same() RETURNS trigger AS
$$
BEGIN
  IF NEW._updated_at IS NULL THEN
    NEW._updated_at := OLD._updated_at;
  END IF;
  RETURN NEW;
END;
$$ LANGUAGE plpgsql;

-- _updated_atカラムがNULLの時、トランザクション開始時のタイムスタンプを代入する
CREATE FUNCTION trg_update_timestamp_current() RETURNS trigger AS
$$
BEGIN
  IF NEW._updated_at IS NULL THEN
    NEW._updated_at := current_timestamp;
  END IF;
  RETURN NEW;
END;
$$ LANGUAGE plpgsql;


/*
  トリガ定義（3つ）
*/
-- まず１つ目の関数を実行
CREATE TRIGGER update_users_updated_at_step1
  BEFORE UPDATE ON users FOR EACH ROW
  EXECUTE PROCEDURE trg_update_timestamp_none();

-- _updated_atカラムが更新された時、２つ目の関数を実行
CREATE TRIGGER update_users_updated_at_step2
  BEFORE UPDATE OF _updated_at ON users FOR EACH ROW
  EXECUTE PROCEDURE trg_update_timestamp_same();

-- 最後に３つ目の関数を実行
CREATE TRIGGER update_users_updated_at_step3
  BEFORE UPDATE ON users FOR EACH ROW
  EXECUTE PROCEDURE trg_update_timestamp_current();

GitHubリポジトリ

動作確認

３つのユースケースで挙動を確かめる。

_updated_atカラム以外のUPDATE
_updated_atカラムに更新前と同じ時刻を指定したUPDATE
_updated_atカラムに更新前と別の時刻を指定したUPDATE

console

-- 初期値の確認 ※予め１行だけ入れておいた
test=# SELECT _updated_at FROM users;
          _updated_at          
-------------------------------
 2022-08-17 17:33:46.347999+00

-- 1.'_updated_at'カラム以外の更新
test=# UPDATE users SET login_id = 'testUserId';
test=# SELECT _updated_at FROM users;
          _updated_at          
-------------------------------
 2022-08-17 17:42:31.335547+00

-- 2.'_updated_at'カラムに更新前と同じ時刻を指定した更新
test=# UPDATE users SET _updated_at = users._updated_at;
test=# SELECT _updated_at FROM users;
          _updated_at          
-------------------------------
 2022-08-17 17:33:46.347999+00

-- 3.'_updated_at'カラムに更新前と別の時刻を指定した更新
test=# UPDATE users SET _updated_at = current_timestamp;
test=# SELECT _updated_at FROM users;
          _updated_at          
-------------------------------
 2022-08-17 17:48:31.852999+00

期待通りの挙動が確認できた。
今後はこういった具合にON UPDATE CURRENT_TIMESTAMPもどきをpostgresで利用する機会があれば使ってみようと思う。

参考ページ

以下ページをかなり参考にさせていただきました。

はじめに

実装

動作確認

参考ページ

Discussion