👏
Supabase での型キャスト対応と RPC 関数による柔軟な検索処理の実装例

2025/03/01に公開
この記事では、Supabase を利用したデータ検索処理において、クライアント側で直接 SQL のキャストができないという課題に対し、データベース側で RPC 関数を作成して解決する方法について説明します。

具体的には、数値型や日付型のカラムに対して部分一致検索を行う際のキャスト処理や、戻り値の型の不一致エラーに対処するための手順、そして TypeScript の型定義に合わせた実装例についてまとめています。

 背景Supabase のクエリビルダーでは、.or() などのフィルター構文内に直接 SQL のキャスト構文（例：amount::text）を埋め込むことがサポートされていません。

そのため、数値型や日付型のカラムに対して部分一致検索を行いたい場合、通常の方法ではエラーが発生してしまいます。

また、テーブルの各カラムの実際の型と関数の戻り値の型が一致しないと、型不一致のエラー（例えば、Returned type character varying(255) does not match expected type text）が発生します。

 課題の詳細
 クライアント側でのキャストができない問題Supabase の PostgREST の仕様上、クライアント側のフィルター文字列で ::text のようなキャスト構文はパースエラーとなってしまいます。

そのため、従来の方法（ビューの作成など）ではなく、より柔軟な方法として RPC（Remote Procedure Call）関数を利用するアプローチが検討されました。

 解決策：RPC 関数の利用
 PostgreSQL 側の実装以下は、Supabase の SQL エディタなどで実行する RPC 関数の例です。

この関数 fetch_filtered_invoices は、検索文字列、オフセット、リミットを引数に受け取り、invoices テーブルと customers テーブルを結合して、各カラムに対して部分一致検索を行います。

また、数値型や日付型のカラムはそれぞれ適切なキャスト（例：i.amount::numeric, i.date::text）を行い、戻り値の型を TypeScript 側の型定義に合わせています。
CREATE OR REPLACE FUNCTION fetch_filtered_invoices(
  search_query text,
  offset_value int,
  limit_value int
)
RETURNS TABLE(
  id text,
  amount numeric,
  date text,
  status text,
  customer_name text,
  customer_email text,
  customer_image_url text
) AS $$
BEGIN
  RETURN QUERY
  SELECT
    i.id::text,             -- uuid を文字列にキャスト
    i.amount::numeric,      -- 数値型を numeric にキャスト
    i.date::text,           -- 日付型を文字列にキャスト
    i.status::text,         -- character varying を text にキャスト
    c.name::text AS customer_name,       -- customers.name も text にキャスト
    c.email::text AS customer_email,      -- 同様にキャスト
    c.image_url::text AS customer_image_url
  FROM invoices i
  JOIN customers c ON i.customer_id = c.id
  WHERE
    c.name ILIKE '%' || search_query || '%'
    OR c.email ILIKE '%' || search_query || '%'
    OR i.amount::text ILIKE '%' || search_query || '%'
    OR i.date::text ILIKE '%' || search_query || '%'
    OR i.status::text ILIKE '%' || search_query || '%'
  ORDER BY i.date DESC
  LIMIT limit_value OFFSET offset_value;
END;
$$ LANGUAGE plpgsql;
ポイント:
キャストの統一:

i.id::text により、uuid 型を文字列に変換。

i.date::text により、日付型の値を文字列に。

i.status::text や c.name::text など、character varying として定義されているカラムも明示的に text 型にキャストしています。
検索条件:

各カラムに対して、ILIKE '%' || search_query || '%' の形で部分一致検索を実施しています。

※ 注意点として、検索文字列が NULL の場合、全件取得にならない可能性があるため、必要に応じて COALESCE(search_query, '') を利用する方法も検討してください。

 TypeScript 側での呼び出し作成した RPC 関数は、Supabase のクライアントライブラリの .rpc() メソッドを使って呼び出します。

以下は TypeScript 側の実装例です。
export async function fetchFilteredInvoices(
  query: string,
  currentPage: number,
) {
  const offset = (currentPage - 1) * ITEMS_PER_PAGE;
  try {
    const supabase = await createClient();
    const { data, error } = await supabase.rpc('fetch_filtered_invoices', {
      search_query: query,
      offset_value: offset,
      limit_value: ITEMS_PER_PAGE,
    });

    if (error) {
      console.error('Database Error:', error);
      throw error;
    }

    // 返り値は RPC 関数の戻り値に合わせたオブジェクトとなるため、整形して返す
    return data.map((invoice: any) => ({
      id: invoice.id,
      amount: invoice.amount,
      date: invoice.date,
      status: invoice.status,
      name: invoice.customer_name,
      email: invoice.customer_email,
      image_url: invoice.customer_image_url,
    }));
  } catch (error) {
    console.error('Failed to fetch invoices:', error);
    throw new Error('Failed to fetch invoices.');
  }
}
このコードでは、RPC 関数に対して search_query、offset_value、limit_value の各パラメータを渡し、返されたデータを必要に応じて整形しています。

TypeScript の型定義と一致するように、戻り値の id、amount、date などがそれぞれ string、number、string となるようにキャストしているため、型不一致のエラーは解消されます。

 まとめ課題認識:

クライアント側で SQL キャストが直接利用できないため、数値型や日付型の部分一致検索でエラーが発生する。
解決策の選択:

ビューの作成や計算カラムによる対処も考えられるが、より柔軟な方法として RPC 関数を利用するアプローチを採用。
実装手順:
PostgreSQL の SQL エディタ（または pgAdmin など）から、キャスト処理を含む RPC 関数 fetch_filtered_invoices を作成。
関数内で、各カラムに対して明示的なキャストを行い、TypeScript 側の型定義に合わせる。
Supabase のクライアントライブラリの .rpc() メソッドを用いて関数を呼び出し、取得したデータを整形して利用する。
この方法により、ビュー作成なしでクライアント側の制約を回避し、柔軟な検索処理を実現できます。
背景

課題の詳細

クライアント側でのキャストができない問題

解決策：RPC 関数の利用

PostgreSQL 側の実装

TypeScript 側での呼び出し

まとめ

Discussion
