PostgreSQL函数间除法运算报错，请求技术支持

问题分析 #

你碰到的语法错误，核心原因是在SELECT列表的列表达式里，直接用了带FROM子句的聚合查询（比如avg(pel_area) from getarea(...)）。这种写法属于完整的SELECT语句结构，不能直接作为单个列的运算项，PostgreSQL会把FROM识别成语法错误。

解决方案 #

要把所有这类聚合查询包装成标量子查询——用括号把整个SELECT ... FROM ...语句包起来，让PostgreSQL把它当成一个返回单个值的表达式，这样就能正常参与除法等算术运算了。

修正后的完整函数代码 #

create or replace function getLotacao(fazenda bigint[], retiro bigint[], dataLot date, intervalo bigint) 
returns table ( 
    totalCabecas integer, 
    pesoTotal decimal(18, 6), 
    UA decimal(15, 6), 
    pesoMedio decimal(18, 6), 
    valorMedio decimal(18, 6), 
    total decimal(18, 6), 
    areaHec decimal(18, 6), 
    cabHec decimal(18, 6), 
    UAHA decimal(18, 6), 
    areaAql decimal(18, 6), 
    cabAlq decimal(18, 6), 
    UAAlq decimal(18, 6) 
) as $$ 
declare
begin
    for i in 0..$4 -1 loop
        return query 
        select 
            getestoque($1, $2, $3::date + 1 * i), 
            getpesolotacao($1, $2, $3::date + 1 * i), 
            getpesolotacao($1, $2, $3::date + 1 * i) / 450, 
            getpesolotacao($1, $2, $3::date + 1 * i) / getestoque($1, $2, $3::date + 1 * i), 
            getvalorlotacao($1, $3::date + 1 * i) / getestoque($1, $2, $3::date + 1 * i), 
            getpesolotacao($1, $2, $3::date + 1 * i) * (getvalorlotacao($1, $3::date + 1 * i) / getestoque($1, $2, $3::date + 1 * i)), 
            (select avg(pel_area) from getarea($1, $2, $3::date + 1 * i)), 
            getestoque($1, $2, $3::date + 1 * i) / (select avg(pel_area) from getarea($1, $2, $3::date + 1 * i)), 
            (getpesolotacao($1, $2, $3::date + 1 * i) / 450) / (select avg(pel_area) from getarea($1, $2, $3::date + 1 * i)), 
            (select avg(pel_area) from getarea($1, $2, $3::date + 1 * i)) / 2.4, 
            getestoque($1, $2, $3::date + 1 * i) / ((select avg(pel_area) from getarea($1, $2, $3::date + 1 * i)) / 2.4), 
            (getpesolotacao($1, $2, $3::date + 1 * i) / 450) / ((select avg(pel_area) from getarea($1, $2, $3::date + 1 * i)) / 2.4);
    end loop;
end; 
$$ language plpgsql;

性能优化建议 #

为了避免重复调用getarea函数（减少不必要的计算开销），你可以在循环内先把avg(pel_area)的结果存入一个变量，再在SELECT中复用这个变量，示例如下：

create or replace function getLotacao(fazenda bigint[], retiro bigint[], dataLot date, intervalo bigint) 
returns table ( 
    totalCabecas integer, 
    pesoTotal decimal(18, 6), 
    UA decimal(15, 6), 
    pesoMedio decimal(18, 6), 
    valorMedio decimal(18, 6), 
    total decimal(18, 6), 
    areaHec decimal(18, 6), 
    cabHec decimal(18, 6), 
    UAHA decimal(18, 6), 
    areaAql decimal(18, 6), 
    cabAlq decimal(18, 6), 
    UAAlq decimal(18, 6) 
) as $$ 
declare
    avg_area decimal(18,6);
begin
    for i in 0..$4 -1 loop
        avg_area := (select avg(pel_area) from getarea($1, $2, $3::date + 1 * i));
        return query 
        select 
            getestoque($1, $2, $3::date + 1 * i), 
            getpesolotacao($1, $2, $3::date + 1 * i), 
            getpesolotacao($1, $2, $3::date + 1 * i) / 450, 
            getpesolotacao($1, $2, $3::date + 1 * i) / getestoque($1, $2, $3::date + 1 * i), 
            getvalorlotacao($1, $3::date + 1 * i) / getestoque($1, $2, $3::date + 1 * i), 
            getpesolotacao($1, $2, $3::date + 1 * i) * (getvalorlotacao($1, $3::date + 1 * i) / getestoque($1, $2, $3::date + 1 * i)), 
            avg_area, 
            getestoque($1, $2, $3::date + 1 * i) / avg_area, 
            (getpesolotacao($1, $2, $3::date + 1 * i) / 450) / avg_area, 
            avg_area / 2.4, 
            getestoque($1, $2, $3::date + 1 * i) / (avg_area / 2.4), 
            (getpesolotacao($1, $2, $3::date + 1 * i) / 450) / (avg_area / 2.4);
    end loop;
end; 
$$ language plpgsql;

这样每次循环只调用一次getarea，能有效提升函数执行效率，尤其是当getarea的计算成本较高时。

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