PostgreSQL：如何获取search_path中首个目标表的Schema并查询列信息

你可以通过以下方式修改查询，利用PostgreSQL的系统函数和系统表来定位search_path中第一个包含目标表的Schema，然后过滤该Schema下的列信息：

最终查询语句 #

SELECT
    column_name,
    data_type
FROM
    information_schema.columns
WHERE
    table_name = '<table_name>'
    AND table_schema = (
        -- 从search_path中找到第一个存在目标表的Schema
        SELECT trim(s.schema_name)
        FROM unnest(string_to_array(current_setting('search_path'), ',')) AS s(schema_name)
        JOIN pg_namespace ns ON ns.nspname = trim(s.schema_name)
        JOIN pg_class c ON c.relnamespace = ns.oid AND c.relname = '<table_name>'
        LIMIT 1
    )
ORDER BY ordinal_position;

代码说明 #

1. 获取并拆分Search Path：current_setting('search_path')获取当前的搜索路径字符串，通过string_to_array拆分成Schema数组，unnest将数组转为行记录。
2. 匹配存在目标表的Schema：关联pg_namespace（Schema系统表）和pg_class（表系统表），找到第一个包含目标表<table_name>的Schema。
3. 过滤列信息：将找到的Schema作为table_schema的过滤条件，确保只返回该Schema下的列名和数据类型。

如果目标表不存在于当前search_path的任何Schema中，子查询会返回NULL，此时主查询将不会返回任何结果，符合预期逻辑。

内容的提问来源于stack exchange，提问作者rob.loh