如何在Supabase中编写PostgreSQL函数实现多表模糊查询？

一、创建跨表搜索的PostgreSQL函数 #

由于三个表结构差异较大，我们通过UNION ALL合并不同表的搜索结果，并添加type字段标识数据来源，方便前端后续处理。

基础模糊匹配（支持大小写不敏感） #

在Supabase的SQL编辑器中执行以下代码创建函数：

CREATE OR REPLACE FUNCTION search_all_content(search_keyword text)
RETURNS TABLE(
    type text,
    id uuid, -- 若你的id为整数类型，替换为int
    name text,
    singer text,
    monthly_listens bigint,
    bio text,
    listen_times bigint,
    link text,
    cover text
) AS $$
BEGIN
    RETURN QUERY
    -- 匹配歌曲表：名称、歌手字段
    SELECT
        'music'::text AS type,
        m.id,
        m.name,
        m.singer,
        NULL::bigint AS monthly_listens,
        NULL::text AS bio,
        m.listenTimes AS listen_times,
        m.link,
        NULL::text AS cover
    FROM musics m
    WHERE m.name ILIKE '%' || search_keyword || '%'
       OR m.singer ILIKE '%' || search_keyword || '%'
    
    UNION ALL
    
    -- 匹配艺术家表：名称、简介字段
    SELECT
        'artist'::text AS type,
        a.id,
        a.name,
        NULL::text AS singer,
        a.monthlyListens AS monthly_listens,
        a.bio,
        NULL::bigint AS listen_times,
        NULL::text AS link,
        NULL::text AS cover
    FROM artists a
    WHERE a.name ILIKE '%' || search_keyword || '%'
       OR a.bio ILIKE '%' || search_keyword || '%'
    
    UNION ALL
    
    -- 匹配播放列表表：名称字段
    SELECT
        'playlist'::text AS type,
        p.id,
        p.name,
        NULL::text AS singer,
        NULL::bigint AS monthly_listens,
        NULL::text AS bio,
        NULL::bigint AS listen_times,
        NULL::text AS link,
        p.cover
    FROM playlists p
    WHERE p.name ILIKE '%' || search_keyword || '%';
END;
$$ LANGUAGE plpgsql STABLE;

进阶近似匹配（支持拼写相近关键词，如'emvnem'匹配'eminem'） #

若需要支持近似拼写匹配，先启用PostgreSQL的pg_trgm扩展：

CREATE EXTENSION IF NOT EXISTS pg_trgm;

再修改函数中的WHERE条件，用相似度阈值替代模糊匹配（以歌曲表为例，其他表同理）：

WHERE similarity(m.name, search_keyword) > 0.3
   OR similarity(m.singer, search_keyword) > 0.3

阈值0.3可按需调整，数值越高，对相似度的要求越严格。

二、配置函数调用权限 #

为让前端能调用该函数，给匿名用户（或你的前端用户角色）赋予执行权限：

GRANT EXECUTE ON FUNCTION search_all_content(text) TO anon;

三、用Supabase JS库调用函数 #

前端通过Supabase的rpc方法调用自定义函数，再根据type字段分类处理结果：

import { createClient } from '@supabase/supabase-js';

// 初始化Supabase客户端
const supabase = createClient('你的Supabase项目URL', '你的公钥');

async function searchAllContent(keyword) {
  try {
    const { data, error } = await supabase.rpc('search_all_content', {
      search_keyword: keyword
    });

    if (error) throw error;

    // 按类型分类结果，方便前端渲染
    const result = {
      musics: data.filter(item => item.type === 'music'),
      artists: data.filter(item => item.type === 'artist'),
      playlists: data.filter(item => item.type === 'playlist')
    };

    return result;
  } catch (err) {
    console.error('搜索失败:', err);
    throw err;
  }
}

// 调用示例
searchAllContent('eminem').then(res => {
  console.log('搜索结果:', res);
  // 在此处渲染UI
});

内容的提问来源于stack exchange，提问作者Saman