公式
やりたいこと
2 つやってみたいことがあります
- updated_at を項目として持っているテーブルは update トリガーで function を起動して updated_at に現在日時をセットする
- ユーザが更新可能なテーブルはすべて update と delete トリガーで function を起動して更新前のレコードを別テーブルに履歴として保存する
supabase 公式
updated_at に現在日時をセット
戦略
updated_at を持つテーブルに対して、update データベーストリガーを設定し、トリガーからトリガ関数を呼び出す。トリガ関数では、updated_at に現在日時をセットする
create or replace function
create or replace function set_updated_at()
returns trigger
language plpgsql
as $$
begin
new.updated_at := now();
return new;
end;
$$
- トリガ関数は
create functionコマンドを使ってreturns triggerという戻り値の型を持った引数のない関数として定義する - new は RECORD 型のデータ型で、update操作によって更新された新しい行を保持している
- 詳しくは PostgreSQL公式 を参照
create trigger
create trigger trg_cats_updated_at before update on cats for each row execute procedure set_updated_at();
動作確認
- function と trigger を SQL Editor から投入
- Table Editor で cats テーブルの適当なレコードの name を更新
- updated_at に日時 (UTC) がセットされる
履歴
戦略
ユーザが更新可能なテーブルに対して、update と delete データベーストリガーを設定し、トリガーからトリガ関数を呼び出す。トリガ関数では、update または delete 前のデータレコードイメージと created_log_at に現在日時をセットする
create or replace function
create or replace function add_profiles_log()
returns trigger
language plpgsql
as $$
declare
delete_col boolean := false;
begin
if (TG_OP = 'DELETE') then
delete_col := true;
end if;
insert into profiles_log (
id, created_log_at, delete, created_at, updated_at, username
, avatar_url, plan, contact_madd, year_of_birth, zip
, job, facebook, twitter, homepage, blog, gender, score
) values (
old.id, now(), delete_col, old.created_at, old.updated_at, old.username
, old.avatar_url, old.plan, old.contact_madd, old.year_of_birth
, old.zip, old.job, old.facebook, old.twitter, old.homepage
, old.blog, old.gender, old.score
);
return new;
end;
$$
- TG_OP は「textデータ型。 トリガを発行した操作を示す、INSERT、UPDATEまたはDELETEという文字列」ということなので、ここが DELETE の時は delete_col := true; にしています
- old には更新前のレコードイメージが保持されているので、これを profiles_log にセットしています
create trigger
create trigger trg_profiles_update before update or delete on profiles for each row execute procedure add_profiles_log();
動作確認
- function と trigger を SQL Editor から投入
- Table Editor で profiles テーブルの適当なレコードの username を更新
- 更新前の username で profiles_log に新規レコードがセットされる (delete は false)
- Table Editor で profiles テーブルの適当なレコードを削除する
- 削除前のレコードイメージで新規レコードがセットされる (delete は true)