调用带参数Supabase RPC时遇PGRST202错误（NextJS 13环境）

问题概述 #

使用NextJS 13 App Router + Docker部署的Supabase后端时，调用带字符串参数的RPC函数get_suburb_data收到400 Bad Request错误，错误码为PGRST202。该函数预期返回Supabase中suburb_name与传入searchquery匹配的行。

环境说明 #

• NextJS 13 App Router
• Docker部署的Supabase后端

已排查操作 #

• 重新创建RPC函数，问题未解决
• 通过返回表前10行的通用函数测试Supabase连接，可正常返回数据
• 验证了传入的字符串参数有效性

错误信息 #

{"code":"PGRST202","details":"Searched for the function public.get_suburb_data without parameters, but no matches were found in the schema cache.","hint":null,"message":"Could not find the function public.get_suburb_data without parameters in the schema cache"}

相关代码 #

page.tsx #

"use client";
import { GetSummarySuburbData } from "@/app/database.types";
import { supaClient } from "@/app/supa-client";
import { useEffect, useState } from "react";

// Get Suburb Name from URL
function getSuburbNameFromURL() {
    const url = new URL(window.location.href);
    const pathname = url.pathname;
    const stringInURL = pathname.replace("/suburb/", "");
    const suburbInURL = stringInURL.replace(/&/g, " ");
    return suburbInURL;
}

export default function GetSummaryData() {
    const [suburbName, setSuburbName] = useState("");
    const [summaryData, setSummaryData] = useState<GetSummarySuburbData[]>([]);

    useEffect(() => {
        const searchQuery = getSuburbNameFromURL();
        console.log(searchQuery);
        setSuburbName(String(searchQuery));
    }, []);

    useEffect(() => {
        if (suburbName) {
            supaClient.rpc("get_suburb_data", { searchquery: suburbName }).then(({ data }) => {
                setSummaryData(data as GetSummarySuburbData[]);
            });
        }
    }, [suburbName]);

    return (
        <>
            Test
            <div>
                {summaryData?.map((data) => (
                    <p key={data.id}>{data.suburb_name}</p>
                ))}
            </div>
        </>
    );
}

PostgreSQL函数定义 #

-- Return row where suburb_name matches searchQuery
CREATE FUNCTION get_suburb_data("searchquery" text)
RETURNS table (
id uuid,
suburb_name text,
state_name text,
post_code numeric,
people INT,
male REAL,
female REAL,
median_age INT,
families INT,
average_number_of_children_per_family text,
for_families_with_children REAL,
for_all_households REAL,
all_private_dwellings INT,
average_number_of_people_per_household REAL,
median_weekly_household_income INT,
median_monthly_mortgage_repayments INT,
median_weekly_rent_b INT,
average_number_of_motor_vehicles_per_dwelling REAL
)
LANGUAGE plpgsql
AS $$
begin
return query
select id, suburb_name, state_name, post_code, people, male, female, median_age, families,
average_number_of_children_per_family, for_families_with_children, for_all_households,
all_private_dwellings, average_number_of_people_per_household, median_weekly_household_income,
median_monthly_mortgage_repayments, median_weekly_rent_b, average_number_of_motor_vehicles_per_dwelling
from summary_data
WHERE suburb_name ILIKE '%' || searchquery || '%'
AND summary_data.path ~ "root";
end;$$;

解决方案 #

1. 重置PostgREST Schema缓存 #

错误提示明确指向Schema缓存未找到带参数的函数，优先刷新缓存：

• SQL命令刷新：在Supabase SQL编辑器或psql中执行以下命令，无需重启服务：

  NOTIFY pgrst, 'reload schema';
  

• Docker容器重启：如果SQL命令无效，重启PostgREST服务容器：

  # 若使用docker-compose
  docker-compose restart postgrest
  
  # 若使用单个容器，先查容器ID再重启
  docker ps | grep postgrest
  docker restart <postgrest_container_id>
  

2. 检查函数权限 #

确保客户端使用的角色（默认是anon）有执行该函数的权限：

GRANT EXECUTE ON FUNCTION public.get_suburb_data(text) TO anon;
-- 若使用authenticated角色，补充授权
GRANT EXECUTE ON FUNCTION public.get_suburb_data(text) TO authenticated;

3. 修复函数定义潜在错误 #

注意函数中summary_data.path ~ "root"这一行，双引号在PostgreSQL中表示标识符（列名/表名），若"root"是正则表达式字符串，应改为单引号：

AND summary_data.path ~ 'root';

如果root是变量或列名，需确认其存在性，否则会导致函数执行失败，间接影响PostgREST对函数的识别。

4. 验证函数参数匹配 #

在psql中执行\df public.get_suburb_data，查看函数的参数名和类型，确保调用时传递的参数名（searchquery）与定义完全一致（注意大小写，若定义时用双引号包裹，需严格匹配）。

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