PostgreSQL：多模式下同名表查询时所属模式的确定方法

嘿，这个问题问到点子上了！你要找的这个magic_function其实可以通过PostgreSQL的系统表和内置功能来实现——本质上就是模拟PostgreSQL解析无模式限定表名时的逻辑：按照search_path的先后顺序遍历模式，找到第一个存在目标表的模式。

核心原理 #

PostgreSQL执行select * from t时，会严格按照search_path的先后顺序检查每个模式里是否存在名为t的普通表，找到第一个匹配的就停止。我们要做的就是把这个过程用SQL实现出来。

直接查询实现 #

不需要自定义函数的话，你可以直接用下面的SQL语句得到结果：

SELECT n.nspname AS matched_schema
FROM unnest(current_setting('search_path')::text[]) AS search_schema(schema_name)
JOIN pg_namespace n ON n.nspname = search_schema.schema_name
JOIN pg_class c 
  ON c.relnamespace = n.oid 
  AND c.relname = 't' 
  AND c.relkind = 'r' -- 限定为普通表，排除视图、序列等
ORDER BY array_position(current_setting('search_path')::text[], search_schema.schema_name)
LIMIT 1;

这段代码的逻辑是：

• 把search_path转换成数组并拆分成行
• 关联系统表pg_namespace（存储模式信息）和pg_class（存储表等对象信息）
• 按照search_path里的顺序排序，取第一个匹配的模式名

封装成自定义函数（即你的magic_function） #

如果需要像示例里那样方便调用，可以把上面的逻辑封装成一个函数：

CREATE OR REPLACE FUNCTION magic_function(table_name text) 
RETURNS text 
LANGUAGE plpgsql STABLE
AS $$
BEGIN
  RETURN (
    SELECT n.nspname
    FROM unnest(current_setting('search_path')::text[]) AS s(schema)
    JOIN pg_namespace n ON n.nspname = s.schema
    JOIN pg_class c 
      ON c.relnamespace = n.oid 
      AND c.relname = table_name 
      AND c.relkind = 'r'
    ORDER BY array_position(current_setting('search_path')::text[], s.schema)
    LIMIT 1
  );
END;
$$;

这样就完全符合你示例里的用法了：

set search_path to one,two;
select magic_function('t'); -- 返回 'one'

set search_path to two,one;
select magic_function('t'); -- 返回 'two'

set search_path to three,two,one;
select magic_function('t'); -- 返回 'two'

补充说明 #

• 如果目标表在search_path的所有模式里都不存在，函数会返回NULL，你可以根据需求添加错误处理（比如用RAISE NOTICE或者抛出异常）
• relkind = 'r'是为了确保只匹配普通表，如果你需要包含视图、物化视图等，可以调整这个条件（比如relkind IN ('r', 'v', 'm')）

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