PostgreSQL アップデート時のタイムスタンプ自動更新

どうも、NIPPAです。

データベースを利用していると、最終アップデートの日時を保存しておきた場合があります。

MySQLでは、テーブル作成時に定義することで自動的に更新日時を格納してくれる機能がありますが、

PostgreSQLでは工夫が必要になります。

今回は、PostgreSQLでもデータ更新時に更新日時自動でアップデートする方法のご紹介になります。

環境

PostgreSQL10.12

PostgreSQL Functionについて

PostgreSQLでFunctionの機能を使って、自動更新する方法をご紹介します。

PostgreSQLのFunction機能は、テーブルが更新された際に指定されたカラムに対して処理を自動で行う機能になります。

この機能を利用して、格納データが更新された時に、時刻を更新するFunctionを作り、テーブルとFunctionを紐付けします。

Functionの設定

PostgreSQLの公式ドキュメントには、Functionの生成方法が以下のうように書かれています。

CREATE [ OR REPLACE ] FUNCTION
    name ( [ [ argmode ] [ argname ] argtype [ { DEFAULT | = } default_expr ] [, ...] ] )
    [ RETURNS rettype
      | RETURNS TABLE ( column_name column_type [, ...] ) ]
  { LANGUAGE lang_name
    | TRANSFORM { FOR TYPE type_name } [, ... ]
    | WINDOW
    | IMMUTABLE | STABLE | VOLATILE | [ NOT ] LEAKPROOF
    | CALLED ON NULL INPUT | RETURNS NULL ON NULL INPUT | STRICT
    | [ EXTERNAL ] SECURITY INVOKER | [ EXTERNAL ] SECURITY DEFINER
    | PARALLEL { UNSAFE | RESTRICTED | SAFE }
    | COST execution_cost
    | ROWS result_rows
    | SET configuration_parameter { TO value | = value | FROM CURRENT }
    | AS 'definition'
    | AS 'obj_file', 'link_symbol'
  } ...
    [ WITH ( attribute [, ...] ) ]

今回のFunctionの生成方法は、以下のうようになります。

CREATE FUNCTION [Function名]() RETURNS OPAQUE AS '
  begin
    new.[Column名] := ''now'';
    return new;
  end;
' LANGUAGE plpgsql;

[Function名]をターゲットにする[Column名]を決めます。

nowで現在時刻を取得します。OPAQUEは戻り値の型を示しており、具体的な型を指定しない時に使います。

ここでは、

Function名：set_update_time Column名：updated_at

とします。

CREATE FUNCTION set_update_time() RETURNS OPAQUE AS '
  begin
    new.updated_at := ''now'';
    return new;
  end;
' LANGUAGE plpgsql;

Functionにトリガーを設定

テスト用のテーブルを以下SQLで作成します。

CREATE TABLE public.sample (
  count INTEGER,
  created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
  updated_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP
);

テーブルにFunctionを設定します。

CREATE TRIGGER update_tri BEFORE UPDATE ON public.sample FOR EACH ROW EXECUTE PROCEDURE set_update_time();

データを格納します。

INSERT INTO public.sample (count) VALUES (1), (10);

データの確認。

SELECT * FROM sample;

/* 結果 */
 count |         created_at         |         updated_at
-------+----------------------------+----------------------------
     1 | 2020-08-27 20:16:09.601743 | 2020-08-29 20:16:09.601743
    10 | 2020-08-27 20:16:09.601743 | 2020-08-29 20:16:09.601743

データをアップデートしてみます。

UPDATE sample SET count = 2 WHERE count = 1;

データの確認。

SELECT * FROM sample;

/* 結果 */
 count |         created_at         |         updated_at
-------+----------------------------+----------------------------
     2 | 2020-08-27 20:16:09.601743 | 2020-08-27 20:16:09.601743
    10 | 2020-08-27 20:16:09.601743 | 2020-08-27 20:24:45.343233

updated_atが更新されていることがわかります。

これでMySQLと同様の機能をPostgreSQLでも可能になります。

Functionの削除

Functionを削除するには、テーブルのトリガーをまず削除し、Functionを削除する必要があります。

以下のSQLになります。

DROP TRIGGER IF EXISTS update_tri ON public.sample;

DROP FUNCTION IF EXISTS set_update_time();

感想

PostgreSQLでも便利な機能があるので、勉強して使えるようになっていきたですね。

参考にしてみてください。

ではでは、また次回。