Supabase RPC调用无法传递全量8个参数问题求助

场景 #

开发React应用时，通过Supabase RPC调用PostgreSQL函数插入数据，传递8个参数时触发找不到函数的错误。但函数参数减少到2个时调用正常，增加到4-6个时开始报错，且报错的参数数量阈值无规律，替换为硬编码值也无法解决。

React端RPC调用代码 #

const confirmTeam = async (user, match, isHomeTeam) => {
    try {
        console.log("matchday", match);
        const { data, error: Error } = await supabase.rpc(
            "confirm_team_selection",
            {
                email: user.email,
                team: isHomeTeam ? match.homeTeamName : match.awayTeamName,
                game_week: match.gameWeek,
                match_id: match.matchId,
                userid: user.id,
                team_id: isHomeTeam ? match.homeTeamId : match.awayTeamId,
                team_location: isHomeTeam ? "HOME_TEAM" : "AWAY_TEAM",
                edition_id: activeEdition,
            }
        );

        if (Error) {
            console.error("Error updating existing active row:", Error);
            return;
        }

        console.log("Data", data);
    } catch (error) {
        console.log("error executing rpc", error);
    }
};

PostgreSQL函数代码 #

create
or replace function confirm_team_selection (email text, team text, game_week bigint, match_id bigint, user_id uuid, team_id bigint, team_location text, edition_id bigint) returns table (result text) as $$

  BEGIN  

  -- 更新现有活跃记录为非活跃
  UPDATE mapping
  SET choice_status = 'inactive'
  WHERE user_id = userid
  AND choice_status = 'active';

  -- 插入新的活跃记录
  INSERT INTO mapping( email, team, game_week, match_id, user_id, team_id, team_location, edition_id, choice_status)
  VALUES(email, team, game_week, match_id, user_id, team_id, team_location, edition_id, 'active');

END;

$$ language plpgsql;

报错信息 #

{
  "code": "PGRST202",
  "details": "Searched for the function public.confirm_team_selection with parameters edition_id, email, team_location, user_id or with a single unnamed json/jsonb parameter, but no matches were found in the schema cache.",
  "hint": "Perhaps you meant to call the function public.confirm_team_selection(edition_id, match_id, team, userid)",
  "message": "Could not find the function public.confirm_team_selection(edition_id, email, team_location, user_id) in the schema cache"
}

解决方法 #

1. 修正参数名不匹配问题 #

• React代码中传递的参数是userid，但PostgreSQL函数定义的参数是user_id，两者名称不一致，导致Supabase无法匹配函数。将React代码里的userid改为user_id。
• PostgreSQL函数的UPDATE语句中使用了未定义的userid变量，应该替换为函数参数user_id（或使用参数位置$5避免歧义）。

2. 修正函数返回值定义 #

函数声明返回table(result text)，但逻辑中没有任何返回语句，会导致函数执行异常。如果不需要返回结果，将返回类型改为returns void；如果需要返回结果，在END前添加RETURN QUERY SELECT 'success'::text as result;。

3. 刷新Supabase schema缓存 #

修改PostgreSQL函数后，Supabase的API缓存可能未及时更新，导致找不到正确的函数签名。可以在Supabase控制台的SQL编辑器中重新执行CREATE OR REPLACE FUNCTION语句，或者等待缓存自动刷新（通常几分钟内完成）。

修正后的代码 #

修正后的PostgreSQL函数

create or replace function confirm_team_selection (
    email text, 
    team text, 
    game_week bigint, 
    match_id bigint, 
    user_id uuid, 
    team_id bigint, 
    team_location text, 
    edition_id bigint
) returns void as $$
BEGIN  
    -- 更新现有活跃记录为非活跃
    UPDATE mapping
    SET choice_status = 'inactive'
    WHERE user_id = user_id
    AND choice_status = 'active';

    -- 插入新的活跃记录
    INSERT INTO mapping( email, team, game_week, match_id, user_id, team_id, team_location, edition_id, choice_status)
    VALUES(email, team, game_week, match_id, user_id, team_id, team_location, edition_id, 'active');
END;
$$ language plpgsql;

修正后的React代码

const confirmTeam = async (user, match, isHomeTeam) => {
    try {
        console.log("matchday", match);
        const { data, error: Error } = await supabase.rpc(
            "confirm_team_selection",
            {
                email: user.email,
                team: isHomeTeam ? match.homeTeamName : match.awayTeamName,
                game_week: match.gameWeek,
                match_id: match.matchId,
                user_id: user.id,  // 修正参数名与函数一致
                team_id: isHomeTeam ? match.homeTeamId : match.awayTeamId,
                team_location: isHomeTeam ? "HOME_TEAM" : "AWAY_TEAM",
                edition_id: activeEdition,
            }
        );

        if (Error) {
            console.error("Error updating existing active row:", Error);
            return;
        }

        console.log("Data", data);
    } catch (error) {
        console.log("error executing rpc", error);
    }
};

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