Supabase RPC调用PostgreSQL函数返回记录数少于直接查询问题

我有一个PostgreSQL（v15）函数get_similar，接收用户ID并返回其他相似用户的列表。直接在数据库执行select * from get_similar('eca567ed-ddf7-4d13-916b-e91c90192869');能得到预期的5条结果，但在Supabase Edge函数中调用时仅返回2条记录。测试数据库共有6个用户，查询任意用户都应返回其余5条记录。

PostgreSQL函数代码 #

CREATE OR REPLACE FUNCTION public.get_similar(player_id uuid)
    RETURNS TABLE (user_id uuid)
AS
$$
BEGIN
    RETURN QUERY
        WITH player_stats AS (
        SELECT AVG(rs.total_distance_yards) AS total_distance,
               (SUM(rs.total_strokes) - SUM(rs.total_par))::NUMERIC(6, 1) AS astp,
                ... -- 其他统计字段
          FROM (SELECT r.id, r2.*
                FROM rounds r,
                     round_stats r2
                WHERE r.id = r2.round_id
                  AND r.user_id = player_id
                  AND r.score > 0
                ORDER BY r.date DESC
                LIMIT 5) AS rs),
             similar_rounds AS (
             SELECT ro.user_id,
                       base.dastp AS total_score
                FROM (SELECT rs.round_id,
                      rs.total_distance_yards AS total_distance,
                      SQRT(POWER(((rs.total_strokes - rs.total_par) - player_stats.astp), 2))::NUMERIC(6, 1) AS dastp
                      ... -- 其他距离计算字段
                      FROM round_stats rs,
                           rounds r,
                           player_stats
                      WHERE r.id = rs.round_id
                        AND r.holes_played = '18'
                        AND r.user_id <> player_id
                      ORDER BY ABS(rs.total_distance_yards - player_stats.total_distance)
                      LIMIT 100) AS base
                         JOIN rounds ro ON ro.id = base.round_id
                ORDER BY (base.dtd + base.dastp))
        SELECT f.user_id
        FROM (SELECT sr.user_id, AVG(sr.total_score)::NUMERIC(6, 1) AS final_score
              FROM similar_rounds sr
              GROUP BY sr.user_id
              ORDER BY AVG(sr.total_score)
              LIMIT 20) AS f;

    IF FOUND THEN
        RETURN;
    ELSE
        RETURN QUERY
            SELECT id::TEXT
            FROM users
            ORDER BY last_visit DESC
            LIMIT 20;
    END IF;
END;
$$ LANGUAGE plpgsql;

Supabase Edge函数调用代码 #

const compareQuery = supabase.rpc('get_similar', {'player_id': 'eca567ed-ddf7-4d13-916b-e91c90192869'});
type Compare = QueryData<typeof compareQuery>;
const {data, error} = await compareQuery;

问题分析与解决方案 #

可能的原因 #

1. 参数类型隐式转换问题：Edge函数传入的是字符串类型UUID，虽然后端会自动转换，但可能导致过滤条件异常，比如某些用户的rounds数据未被匹配到。
2. 角色权限差异：直接查询使用的数据库角色（比如owner）和Supabase Edge函数使用的角色（比如authenticated）权限不同，导致部分rounds或round_stats数据无法访问，最终影响相似用户计算。
3. 排序不稳定：查询中的ORDER BY字段存在重复值，LIMIT截取的中间结果在不同执行环境中不一致，导致最终分组后的用户数量不同。

解决方案 #

1. 明确参数类型
   在Edge函数中确保传入的UUID类型正确，或在PostgreSQL函数中添加参数校验：

   // 显式指定类型（如果使用TypeScript）
   const playerId: string = 'eca567ed-ddf7-4d13-916b-e91c90192869';
   const compareQuery = supabase.rpc('get_similar', { player_id: playerId });
   

   或在函数中添加校验：

   BEGIN
       IF player_id IS NULL THEN
           RAISE EXCEPTION 'player_id cannot be null';
       END IF;
       -- 原函数逻辑...
   

2. 检查并调整角色权限
   确保Edge函数使用的角色拥有相关表的SELECT权限：

   GRANT SELECT ON rounds, round_stats, users TO authenticated; -- 根据实际使用角色调整
   

3. 添加稳定排序字段
   在所有ORDER BY语句后添加唯一字段（如ID），保证排序结果稳定：

   • player_stats子查询：

     ORDER BY r.date DESC, r.id LIMIT 5
     

   • similar_rounds的base子查询：

     ORDER BY ABS(rs.total_distance_yards - player_stats.total_distance), r.id LIMIT 100
     

   • similar_rounds外层：

     ORDER BY (base.dtd + base.dastp), ro.id
     

4. 调试排查
   在Edge函数中打印error和data，确认是否有错误：

   console.log('Error:', error);
   console.log('Data:', data);
   

   或在PostgreSQL函数中添加日志，查看中间结果：

   RAISE NOTICE 'player_id: %', player_id;
   RAISE NOTICE 'player_stats: %', (SELECT row_to_json(ps) FROM player_stats ps);
   

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