PostgreSQL返回表的简单函数如何规避列名歧义报错

错误原因 #

你遇到的报错是PostgreSQL的PL/pgSQL特性导致的：使用RETURNS TABLE (a INTEGER, b INTEGER)定义函数返回结构时，括号内的a、b会被默认注册为函数级别的变量。当函数内的查询语句出现同名标识符时，PL/pgSQL无法判断你要取的是表字段还是函数变量，就会抛出列名歧义错误。

替代解决方案 #

方案1：将返回值类型改为SETOF 表名（最适配你的场景） #

因为你的返回结构和查询表mytable完全一致，直接用SETOF指定表名作为返回类型即可，无需重复定义列名，自然不会出现变量和列名重名的问题。
示例代码：

create or replace function test(q INTEGER)
returns SETOF mytable
as
$body$
begin
    return query select a,b from mytable;
    -- 甚至可以直接写 return query select * from mytable; 也完全兼容
end;
$body$
language plpgsql STABLE;

这个方案改动最小，只需要修改函数的返回类型定义，不需要调整内部的查询语句，完全符合你不需要修改查询逻辑的需求。

方案2：修改函数级别的变量冲突处理规则 #

如果你返回的结构没有对应已有的表/类型，没法用SETOF 表名的方式，可以在函数定义时指定plpgsql.variable_conflict参数，告诉PL/pgSQL遇到歧义时优先使用查询列的取值，不需要改动任何查询语句。
示例代码：

create or replace function test(q INTEGER)
returns table(a INTEGER, b INTEGER)
as
$body$
begin
    return query select a,b from mytable;
end;
$body$
language plpgsql STABLE
SET plpgsql.variable_conflict = use_column; -- 新增这一行即可

这个参数只会在当前函数内生效，不会影响全局配置，也不需要调整你的现有查询逻辑。

方案3：给返回参数加统一前缀区分（可选） #

如果前两种方案都不适用，你也可以在定义返回表的列名时加统一前缀，从根源上避免和查询表的列名重名，查询语句不需要加表别名限定：
示例代码：

create or replace function test(q INTEGER)
returns table(out_a INTEGER, out_b INTEGER)
as
$body$
begin
    return query select a,b from mytable;
end;
$body$
language plpgsql STABLE;

如果需要保留原输出列名，只需要在调用函数时给列加别名即可，不需要修改函数内部逻辑。

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