Supabaseのpgroongaを使って全文検索を行う方法

はじめに

Supabaseのpgroongaを使って全文検索を行う方法について説明します。

Supabaseでの設定

pgroongaを有効にする

Database > Extensionsからpgroongaを検索し、有効にする。
pgroonga_databaseではなく、pgroongaの方を有効にすることに注意してください。
また、Schemaは使用したいテーブルのスキーマを選択してください（私はここの設定で結構ハマりました…）。

検索列の作成

pgroongaでは複数列を同時に検索することができません。 そのため、検索したい列を結合して新たな列を作成し、 その列を検索対象とすることで複数列を検索することにします。

前提となるbookmarksテーブルが以下のような構造になっているとします。

列名	型	説明
id	uuid	ブックマークのID
url	text	URL
title	text	タイトル
memo	text	メモ

-- ここではtitleとmemo列はnullの可能性を考慮して、COALESCEでnullを空文字に変換してから結合しています。
ALTER TABLE bookmarks
  ADD COLUMN url_title_memo text GENERATED ALWAYS AS
  (url || ' ' || COALESCE(title, '') || ' ' || COALESCE(memo, '')) STORED

インデックスの作成

上記で作成した列に対してインデックスを作成します。
normalizerとtokenizerは日本語の全文検索を行うための設定です。
詳細はCREATE INDEX USING pgroongaを参照してください。

CREATE INDEX pgroonga_content_index
  ON bookmarks
  USING pgroonga (url_title_memo)
  WITH (
    normalizer='NormalizerAuto',
    tokenizer='TokenMecab'
  )

pgroongaを実行するストアドファンクションの作成

pgroongaを実行するストアドファンクションを作成します。

CREATE OR REPLACE FUNCTION search_url_title_memo(keywords text)
RETURNS TABLE (
    id UUID,
    title TEXT,
    url TEXT,
    memo TEXT,
)
LANGUAGE plpgsql
AS $$
BEGIN
  RETURN QUERY
  SELECT b.id, b.title, b.url, b.memo
  FROM bookmarks b
  WHERE url_title_memo &@~ keywords;
END;
$$;

使い方

search_url_title_memoストアドファンクションを実行することで、 url_title_memo列を検索対象として検索することができます。

select search_url_title_memo('Supabase')

フロントエンドでの利用

以下のようにSupabaseのJavaScriptクライアントを使用して、 フロントエンドからストアドファンクションを利用することができます。
また、rangeやorderなども組み合わせて利用することができます。

async function searchBookmarks({keywords}) {
  const params = {
    keywords: keywords,
  };
  const { data, error } = await supabase
    .rpc("search_url_title_memo", params)
    .range(0, 10)
    .order("title", { ascending: false })