PostgREST调用PostgreSQL批量更新函数：匹配与重载问题

问题现象 #

• 仅创建json/jsonb类型的单函数时，PostgREST报错：No function matches the given name and argument types. You might need to add explicit type casts.
• 创建json/jsonb/text重载函数时，触发PGRST203错误：无法选择最优候选函数

解决方案 #

1. 简化函数定义，避免重载冲突 #

PostgREST会自动将Content-Type: application/json的请求体解析为jsonb类型，因此只需要保留一个jsonb参数的函数即可，无需重载。如果需要兼容文本格式的JSON输入，可以在函数内部处理转换，而非创建重载函数。

修正后的函数代码：

-- TABLE --
CREATE TABLE IF NOT EXISTS vehicle_database.test_table(
    test_table_id BIGSERIAL PRIMARY KEY,
    test_data INTEGER,
    test_type VARCHAR(8)
);

-- 仅保留jsonb类型的批量更新函数
CREATE OR REPLACE FUNCTION update_test_table(p_json jsonb)
RETURNS void AS $$
DECLARE
    json_item jsonb;
BEGIN
    FOR json_item IN SELECT jsonb_array_elements(p_json) LOOP
        PERFORM update_test_table_row(
            (json_item->>'test_data')::integer, -- 修正原代码中键名错误（原用data，实际传入的是test_data）
            (json_item->>'test_type')::text
        );
    END LOOP;
END;
$$ LANGUAGE plpgsql;

-- 单行更新函数
CREATE OR REPLACE FUNCTION update_test_table_row(p_data integer, p_type text)
RETURNS void AS $$
BEGIN
    UPDATE vehicle_database.test_table SET test_data = p_data WHERE test_type = p_type;

    IF NOT FOUND THEN
        RAISE NOTICE 'No rows updated for data: %, type: %', p_data, p_type;
    END IF;
END;
$$ LANGUAGE plpgsql;

-- 权限设置
GRANT EXECUTE ON FUNCTION update_test_table(p_json jsonb) TO web_insert;
GRANT EXECUTE ON FUNCTION update_test_table_row(p_data integer, p_type text) TO web_insert;

2. 修复原函数的逻辑错误（如需兼容text输入） #

原text类型重载函数存在未定义变量json_data的问题，修正后：

CREATE OR REPLACE FUNCTION update_test_table(p_json text)
RETURNS void AS $$
DECLARE
    json_data jsonb; -- 需先声明变量
BEGIN
    json_data = p_json::jsonb;
    PERFORM update_test_table(json_data);
END;
$$ LANGUAGE plpgsql;

-- 补充权限
GRANT EXECUTE ON FUNCTION update_test_table(p_json text) TO web_insert;

注意：如果同时保留jsonb和text版本，调用时需通过PostgREST的?argtype参数显式指定类型，比如：

curl -X POST -H "Authorization: Bearer $TOKEN" -H "Content-Type: text/plain" -d '[{"test_data":"4","test_type":"RED"}]' http://127.0.0.1:3000/rpc/update_test_table?argtype=p_json:text

3. 日志排查方法 #

• PostgREST日志：修改配置文件（如postgrest.conf），设置log-level = debug，重启服务后即可看到详细的请求解析和函数匹配日志。
• PostgreSQL日志：修改postgresql.conf，设置log_statement = 'all'并重启数据库，可查看所有执行的SQL语句及传入的参数值。

验证调用 #

使用原curl命令即可正常调用：

curl -X POST -H "Authorization: Bearer $TOKEN" -H "Content-Type: application/json" -d '[{"test_data":"4","test_type":"RED"}]' http://127.0.0.1:3000/rpc/update_test_table

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