postgresql PGRST202使用传入参数调用RPC时出错

pwuypxnk  于 2023-08-04  发布在  PostgreSQL
关注(0)|答案(1)|浏览(132)

当调用一个传递了字符串的RPC时,我得到了一个400 Bad请求错误。该函数的目的是返回searchquery与Supabase中的suburb_name匹配的行。

  • 使用NextJS 13 App Router和Supabase作为Docker的后端。
  • 我已经尝试重新创建函数,但似乎不起作用,并已测试连接到Supabase使用一个通用函数返回的前十行我的表,成功地呈现。根据这个错误,我可能需要重置模式缓存,因为它试图在模式缓存中搜索没有参数的函数“get_suburb_data”。
  • 我还验证了正在传递的字符串是否有效。
    错误:

{“code”:“PGRST 202”,“details”:“已搜索不带参数的函数public.get_suburb_data,但在架构缓存中找不到匹配项。",“hint”:null,“message”:“在架构缓存中找不到不带参数的函数public.get_suburb_data”,“hint”:null“}

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;$$;

qnzebej0

qnzebej01#

如果您使用的是Supabase,Postgres中的模式缓存会自动更新。如果你想手动操作以确保你能运行

NOTIFY pgrst, 'reload schema'

字符串
让PostgREST重新加载它。
代码示例

supaClient.rpc("get_suburb_data", { searchquery: suburbName })


看起来你只传递了一个suburbName,这与接受18个参数的SQL函数不兼容。您需要为所有参数传递值,或者更新函数以提供默认值(默认值可以为null)。

相关问题