使用pg_restore执行SQL脚本时遭遇'query'列引用歧义错误

你遇到的ERROR: column reference "query" is ambiguous错误，根源在于创建视图的SQL中，FROM子句里多余地为函数结果集指定了与原表列名完全一致的列列表。

当你调用返回SETOF pg_stat_activity类型的函数时，函数已经明确返回了与系统表pg_stat_activity结构完全一致的结果集，不需要额外指定列列表。而你在FROM schemaone.get_pg_stat_activity() get_pg_stat_activity(...)中添加的列列表，相当于给结果集的列重新赋予了和原列名完全相同的别名，这导致PostgreSQL解析器在解析get_pg_stat_activity.query时，无法区分是引用函数返回的原始列，还是你显式定义的别名列（尽管两者名称一致），从而触发歧义错误。

直接移除FROM子句中函数别名后的列列表即可，修改后的创建视图SQL如下：

CREATE VIEW schematwo.pg_stat_activity AS
SELECT 
    get_pg_stat_activity.datid,
    get_pg_stat_activity.datname,
    get_pg_stat_activity.pid,
    get_pg_stat_activity.usesysid,
    get_pg_stat_activity.usename,
    get_pg_stat_activity.application_name,
    get_pg_stat_activity.client_addr,
    get_pg_stat_activity.client_hostname,
    get_pg_stat_activity.client_port,
    get_pg_stat_activity.backend_start,
    get_pg_stat_activity.xact_start,
    get_pg_stat_activity.query_start,
    get_pg_stat_activity.state_change,
    get_pg_stat_activity.wait_event_type,
    get_pg_stat_activity.wait_event,
    get_pg_stat_activity.state,
    get_pg_stat_activity.backend_xid,
    get_pg_stat_activity.backend_xmin,
    get_pg_stat_activity.query,
    get_pg_stat_activity.backend_type
FROM schemaone.get_pg_stat_activity() get_pg_stat_activity;

额外说明 #

• 你的函数定义本身没有问题，SECURITY DEFINER的设置可以让普通用户通过该函数访问pg_catalog.pg_stat_activity（通常只有超级用户能直接访问）。
• 移除多余的列列表后，函数返回的结果集列名与系统表完全一致，视图的SELECT子句可以正常解析每个列，不会再出现歧义。

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