如何在PostgREST中实现表与表值函数的关联查询？

这个需求完全可以实现，你遇到的PGRST200错误是因为PostgREST默认不会将普通函数识别为表的关联资源，直接在select参数里写fn_test(id)不符合它的语法规则，以下是两种可行的实现方式：

方式一：用视图封装关联逻辑 #

直接创建一个包含关联结果的视图，将employees表和fn_test的返回结果整合，之后通过访问视图来获取数据：

CREATE VIEW employees_with_expanded_skills AS
SELECT 
  e.id, 
  e.name, 
  e.skills, 
  t.skill
FROM employees e
LEFT JOIN fn_test(e.id) t ON true;

创建完成后，直接访问v1/employees_with_expanded_skills即可拿到关联后的完整数据，也可以通过select参数筛选所需字段，比如v1/employees_with_expanded_skills?select=id,name,skill。

方式二：给函数添加关联注解，让PostgREST识别为子资源 #

如果不想创建视图，可以通过给fn_test函数添加PostgREST专属的注释，将其与employees表建立关联关系：

1. 先确保你的函数是集合返回类型且为STABLE/IMMUTABLE（PostgREST不支持调用VOLATILE类型的函数作为关联），示例函数定义：

CREATE OR REPLACE FUNCTION fn_test(p_employee_id INT)
RETURNS TABLE(skill skill) AS $$
SELECT unnest(e.skills) AS skill
FROM employees e WHERE e.id = p_employee_id;
$$ LANGUAGE sql STABLE;

2. 添加关联注释，指定函数参数与employees表字段的关联关系：

COMMENT ON FUNCTION fn_test(INT) IS '{"foreign_keys": [{"table": "employees", "columns": ["id"], "foreign_columns": ["p_employee_id"]}]}';

3. 之后就可以通过嵌套查询的语法访问，示例URL：
   v1/employees?select=id,name,skills,fn_test(*)
   或者针对单个员工查询：
   v1/employees?id=eq.1&select=*,fn_test(*)

注意事项 #

• 确保PostgREST配置中的db_functions_schema包含函数所在的schema（默认是public，若函数在其他schema需手动配置）。
• 保证PostgREST使用的数据库用户拥有该函数的调用权限。

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