PostgreSQL调用自定义函数报mbrmonths列不存在问题求助

错误原因 #

报错的核心原因是PostgreSQL的查询执行顺序规则：同一条SELECT子句中，无法直接引用当前子句内刚刚定义的列别名。你在SELECT中先将计算出的会员月数命名为mbrmonths，紧接着调用函数时直接使用这个别名，此时数据库还未完成别名的绑定，会将mbrmonths识别为elan.elig表的原生字段查找，因此触发字段不存在的报错。

解决方法 #

你可以通过两种方式规避这个问题：

方法1：子查询封装计算逻辑 #

先在子查询中完成mbrmonths的计算，外层查询调用函数时直接引用子查询返回的字段即可：

select 
    t.mbrmonths,
    t.effectivedate,
    public."UpdatePMPM"(t.mbrmonths, t.effectivedate)
from (
    select  
        cast(
            (extract(year from age(case when terminationdate is null then CURRENT_DATE else terminationdate end, effectivedate))) * 12 
            + (extract(month from age(case when terminationdate is null then CURRENT_DATE else terminationdate end, effectivedate)) + 1) 
        as integer) as mbrmonths,
        effectivedate
    from elan.elig
) t
order by t.mbrmonths;

方法2：CTE封装计算逻辑 #

也可以用公用表表达式（CTE）提前完成数值计算，可读性更高：

with elig_calc as (
    select  
        cast(
            (extract(year from age(case when terminationdate is null then CURRENT_DATE else terminationdate end, effectivedate))) * 12 
            + (extract(month from age(case when terminationdate is null then CURRENT_DATE else terminationdate end, effectivedate)) + 1) 
        as integer) as mbrmonths,
        effectivedate
    from elan.elig
)
select 
    mbrmonths,
    effectivedate,
    public."UpdatePMPM"(mbrmonths, effectivedate)
from elig_calc
order by mbrmonths;

注意事项 #

你的UpdatePMPM函数包含UPDATE写操作，如果elan.elig表数据量较大，该查询会逐行触发更新，执行效率较低，建议先限定查询范围测试无误后再全量执行。

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