Zenn
Open1

論理削除をトリガーで代替する

PostgreSQL
#
論理削除
Nakano as a ServiceNakano as a Service

まずバックアップ用のテーブルを作成する。

CREATE TABLE public.deleted_record
(
    id uuid NOT NULL DEFAULT gen_random_uuid(),
    table_name varchar(63) NOT NULL,
    content jsonb NOT NULL,
    created_at timestamp with time zone NOT NULL DEFAULT CURRENT_TIMESTAMP,
    CONSTRAINT deleted_record_pkey PRIMARY KEY (id)
);

そして削除時に内容をバックアップするトリガー関数を作成する。

CREATE OR REPLACE FUNCTION public.backup_deleted_record()
    RETURNS TRIGGER
    LANGUAGE 'plpgsql'
    AS $BODY$
BEGIN
    INSERT INTO deleted_record(table_name, content)
        VALUES(TG_TABLE_NAME, row_to_json(OLD));
    RETURN OLD;
END;
$BODY$;

最後にバックアップしたいテーブルに対してトリガーを設定する。

CREATE TRIGGER backup_${テーブル名}
    BEFORE DELETE ON ${テーブル名}
    FOR EACH ROW
    EXECUTE FUNCTION public.backup_deleted_record();

ちなみにJSONに対してインデックスを作ることができる。

CREATE INDEX deleted_record_content_user_id_idx
    ON public.deleted_record USING GIN((content ->> 'user_id') jsonb_path_ops);

また以下のようにして復元できる。

INSERT INTO `user` (id, name)
SELECT
    (content ->> 'id')::int,
    (content ->> 'name')::text,
FROM
    deleted_record
WHERE
    table_name = 'user';