🍆

PostgreSQLで（Supabase）新規タグが登録されたらトリガーしてタグ管理テーブルに自動挿入する

2022/07/31に公開

PostgreSQL

Supabase

tech

それでも内容を見たい方はクリック

PostgreSQLスーパー初心者です。
正しい書き方なのかは補償しません。

何がしたいのか？

ユーザーが任意のタグを自由に追加できる仕様。
タグをクリックすると、タグが付与された投稿を一覧で表示する。
Twitterのハッシュタグに近いやつを実現したい。

動きを動画で見てみる

profiles.fellows にjsonb形式で任意の文字列に制限する。追加されたタグは life_categories テーブルに新規追加される。もしも life_categories 内で重複する場合は追加処理をスキップする。

事前準備

life_categoriesテーブルをSQL文で確認

CREATE TABLE IF NOT EXISTS public.life_categories
(
    id uuid NOT NULL DEFAULT uuid_generate_v4(),
    label character varying COLLATE pg_catalog."default" NOT NULL,
    CONSTRAINT life_categories_pkey PRIMARY KEY (id),
    CONSTRAINT life_categories_label_key UNIQUE (label),
    CONSTRAINT valid_regex_life_categories_label CHECK (label::text ~ regex_japanese_exclusion_symbols(1::numeric, 16::numeric)::text)
)

挿入先になる life_categories の labelカラム（文字列が入る）は、ユニークインデックスとして作成しています。また、idカラムはuuidのv4にて自動生成されます。

（上に貼ったものはpgAdminにて現状のテーブルを元に自動生成されたものを貼っています。）

profiles.fellowについてはjsonb型にしています。（タグから一覧を参照するとき、インデックスを効かせるため。）

トリガー時に動くSQL文

処理の内容はコメント通り。
入力データが存在するかをチェックし、そのデータがルールに従っている形式であるかを検証。1つでも問題があれば失敗扱い。life_categories テーブルにて重複を確認しながら新規ならINSERT処理を実行しています。

create or replace function edit_fellows() returns trigger AS $$
declare
  fellows jsonb; -- DBから取得したfellowsデータ（元データ）
  the_fellow varchar; -- ループ処理用の単一fellowデータの定義
  counter numeric:=0; -- 入力データの問題性の有無をカウント

begin
    -- 元データを取得する（jsonbデータをfellows変数に格納）
  fellows := new.fellows;
  
  if (jsonb_array_length(fellows) > 0) then -- profiles.fellowsの項目が存在するかを確認
    -- データを個別化してループ処理で検証を開始
    for the_fellow in select jsonb_array_elements_text(fellows)
    loop
      -- 日本語もしくは半角英数字で構成された1文字以上16文字以下の文字列のみ受け付ける
      if (the_fellow ~ regex_japanese_exclusion_symbols(1, 16)) then
        counter := counter + 1; -- 問題なしをカウント
      end if;
    end loop;

    -- 問題なしデータと入力データの数が一致するかを検証
    if (jsonb_array_length(fellows) = counter) then
      for the_fellow in select jsonb_array_elements_text(fellows)
      loop
        -- 新しいタグの場合は追加処理を実行する
        if (not exists(select 1 from public.life_categories where label = the_fellow)) then
          insert into public.life_categories(label) values(trim(the_fellow));
        end if;
      end loop;
      -- 処理が成功した
      return new;
    else
      -- 一致しなかった（登録処理を中断）
      return false;
    end if;
    
    else -- profile.fellowsの値が存在しない（何も登録されていない状態）
      return new;
  end if;
end;
$$ language plpgsql;

select edit_fellows();

このSQL文に含まれる regex_japanese_exclusion_symbols() は独自の関数で、

半角英数字
日本語（ひらがな、全角カタカナ、漢字）
ー
最小文字数（min_lengthで引数で指定）
最大文字数（max_lengthで引数で指定）

これらの文字を許可する関数です。
今回は単語レベルの許可ですのでスペース等は許可していません。

< や > や [ や ] などのプログラム等に影響を及ぼす可能性のある文字を受け付けない形にしています。使えない文字を制限するのではなく、開発者が認知している文字を許可する方向にすることで予期せぬ文字が入力されることを防いでいます。

regex_japanese_exclusion_symbols()

begin
  return '^([a-zA-Z0-9]|[\u3041-\u3096]|[\u30A1-\u30FA]|[\u4e00-\u9faf]|[\uFF66-\uFF9D]|[ー]){'|| min_length ||','|| max_length ||'}$';
end;

Supabase側での設定

Database -> Trigger にてトリガーを追加。
TableはProfilesを指定。
Eventsは、Insert / Updateされたとき実行するのが妥当なため、InsertとUpdateを指定。
Trigger typeはAfterを指定。

CREATE TRIGGER edit_profile_fellows
    AFTER INSERT OR UPDATE 
    ON public.profiles
    FOR EACH ROW
    EXECUTE FUNCTION public.edit_profile_fellows();

この設定により、profiles テーブルに INSERTまたはUPDATEの処理があった場合、先の edit_fellows() 関数が自動的に実行されます。その結果、ユーザーが任意のタグを追加・更新した際に勝手に動いてくれます。

なお、new.fellows を受け取ることができなければ処理はほとんどスルーされて速攻newをreturnするはずなので特段重くなるということはないかと思います。

-- 元データを取得する（jsonbデータをfellows変数に格納）
fellows := new.fellows;

if (jsonb_array_length(fellows) > 0) then -- profiles.fellowsの項目が存在するかを確認
 
 ~~~
 
  else -- profile.fellowsの値が存在しない（何も登録されていない状態）
    return new;
end if;

おわりに

Supabaseを採用するとPostgreSQLの処理を使うことになるので、PHPなどの中間の処理を書く必要がなく、それはそれで苦しい部分もありますが、自分にはこっちの方が肌に合っているような気がしています。何よりデータベースと真剣に向き合う必要があるので、曖昧な理解でやっていたDB周りを知るきっかけになっています。果たしてセキュリティ的に問題ないものが書けているか不安ですが、指摘して頂けるように積極的に中身を晒しています。気になる箇所があれば気を遣わずに指摘して頂きたいです！