PostgreSQL GROUP BY中Case表达式引发聚合报错求助

问题场景 #

编写PL/pgSQL函数时，尝试用CASE语句根据传入的_period参数决定是否对profit列做聚合，但触发分组规则错误。以下是简化后的错误函数：

CREATE OR REPLACE FUNCTION retrieve_test(
  _period interval
)
returns table(
  profit double precision,
  bid double precision,
  ask double precision
) as $$
begin
    raise notice 'Value: %', _period;
    return query
        SELECT
            (CASE WHEN _period IS NOT NULL THEN AVG(o.profit) ELSE o.profit END)::double precision,
            o.bid, o.ask
    FROM opportunities o
    GROUP by 
    case WHEN _period is NULL then 1 end,
    2,3;
END;
$$ LANGUAGE PLPGSQL;

调用任一语句都会报错：

select * from retrieve_test(null);
-- 或
select * from retrieve_test('1 minute'::interval);

错误信息：

SQL Error [42803]: ERROR: column "o.profit" must appear in the GROUP BY clause or be used in an aggregate function
Where: PL/pgSQL function retrieve_test(interval) line 4 at RETURN QUERY

错误原因 #

1. 当_period为NULL时，SELECT中的profit是原始列o.profit，但GROUP BY子句未包含该列，也未对其使用聚合函数，违反PostgreSQL的分组规则：非聚合列必须出现在GROUP BY中。
2. 原写法试图将两种完全不同的查询逻辑（聚合分组/返回原始数据）合并到一个SELECT中，导致分组逻辑冲突。当_period不为NULL时需要按bid、ask分组求平均；当_period为NULL时不需要分组，直接返回每条记录的原始值。

修复方案 #

通过IF语句拆分两种逻辑，分别执行对应的查询：

CREATE OR REPLACE FUNCTION retrieve_test(
  _period interval
)
returns table(
  profit double precision,
  bid double precision,
  ask double precision
) as $$
begin
    raise notice 'Value: %', _period;
    
    IF _period IS NOT NULL THEN
        -- 需要聚合分组的逻辑
        return query
            SELECT
                AVG(o.profit)::double precision,
                o.bid,
                o.ask
            FROM opportunities o
            GROUP BY o.bid, o.ask;
    ELSE
        -- 返回原始数据，无需分组
        return query
            SELECT
                o.profit::double precision,
                o.bid,
                o.ask
            FROM opportunities o;
    END IF;
END;
$$ LANGUAGE PLPGSQL;

改动说明 #

• 移除了原来的CASE分支判断，改用PL/pgSQL的IF语句分离两种业务逻辑，避免分组规则冲突。
• 当_period不为NULL时，仅执行聚合查询，按bid和ask分组计算平均利润。
• 当_period为NULL时，直接查询原始数据，无需GROUP BY，返回每条记录的利润值。

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