PostgreSQL自定义avg_or_percentile聚合函数实现咨询

我想要创建一个聚合函数avg_or_percentile(column, type::text)，根据参数type（取值如'avg'、'50'、'99'等）选择聚合方式：当type为'avg'时使用avg函数，其他值则计算对应分位数。

我尝试编写了如下过渡函数，但出现了“internal类型无法使用”的错误：

CREATE OR REPLACE FUNCTION avg_or_percentile_transition(state internal, value double precision)
RETURNS internal AS $$
BEGIN
    CASE agg_type
        WHEN 'avg' THEN
            return int8_avg_accum(state, value);
        ELSE
            return ordered_set_transition(state, value);
    END CASE;
END;
$$ LANGUAGE plpgsql;

我是否必须拆解avg和分位数的实现并合并到自定义函数中？有没有其他更简单的方法？

目前我通过包含Grafana宏的CASE语句实现了需求，但语句冗余，新增聚合列时会更繁琐，希望简化实现：

Select 
  $__timeGroup("Closed", $__interval, 0) as "time",
  CASE
    WHEN '${Func}'='avg' 
    THEN avg(duration)
    ELSE percentile_cont(try_cast_numeric('0.${Func}')) within group (order by duration asc)
  END as "${Func}"
from
  (SELECT
    *,
    extract(epoch from "Closed"-"Created")/3600 as duration
  FROM pr."PullRequests" pr
  WHERE $__timeFilter("Closed") and (pr."TeamName" = ${Team} or ${Team} = 'All')) withDuration
GROUP BY time
ORDER BY time

1. 自定义过渡函数报错原因 #

PostgreSQL的internal类型是内部专用类型，不允许在用户自定义函数中直接操作，所以你没法调用int8_avg_accum或ordered_set_transition这类内部聚合的过渡函数来实现分支逻辑。拆解底层实现确实可行，但开发成本高，完全没必要。

2. 最优简化方案：用SQL函数封装+动态SQL #

不需要写自定义聚合函数，直接创建标量函数封装分支判断逻辑，结合Grafana的动态SQL能力简化查询：

CREATE OR REPLACE FUNCTION get_agg_expr(type text)
RETURNS text AS $$
BEGIN
    IF type = 'avg' THEN
        RETURN 'avg(duration)';
    ELSE
        RETURN format('percentile_cont(0.%s) WITHIN GROUP (ORDER BY duration)', type);
    END IF;
END;
$$ LANGUAGE plpgsql IMMUTABLE;

在Grafana中可以这样调用（需确保${Func}的取值是可控白名单，避免SQL注入）：

WITH withDuration AS (
  SELECT
    $__timeGroup("Closed", $__interval, 0) as "time",
    extract(epoch from "Closed"-"Created")/3600 as duration
  FROM pr."PullRequests" pr
  WHERE $__timeFilter("Closed") and (pr."TeamName" = ${Team} or ${Team} = 'All')
)
SELECT
  "time",
  ${__fromSql(get_agg_expr('${Func}'))} as "${Func}"
FROM withDuration
GROUP BY "time"
ORDER BY "time";

3. 无动态SQL的优化方案 #

如果不想用动态SQL，可以优化现有CASE语句的结构，减少冗余：

WITH withDuration AS (
  SELECT
    $__timeGroup("Closed", $__interval, 0) as "time",
    extract(epoch from "Closed"-"Created")/3600 as duration
  FROM pr."PullRequests" pr
  WHERE $__timeFilter("Closed") and (pr."TeamName" = ${Team} or ${Team} = 'All')
)
SELECT
  "time",
  CASE '${Func}'
    WHEN 'avg' THEN avg(duration)
    WHEN '50' THEN percentile_cont(0.50) WITHIN GROUP (ORDER BY duration)
    WHEN '95' THEN percentile_cont(0.95) WITHIN GROUP (ORDER BY duration)
    WHEN '99' THEN percentile_cont(0.99) WITHIN GROUP (ORDER BY duration)
    -- 新增聚合类型直接添加WHEN分支即可
  END as "${Func}"
FROM withDuration
GROUP BY "time"
ORDER BY "time";

这种写法把重复的duration计算和过滤逻辑抽离到CTE中，新增聚合列时只需要补充对应的WHEN条件，比原写法更清晰简洁。

4. 注意事项 #

• 不要尝试直接操作PostgreSQL的internal类型，这类内部接口没有公开文档，版本迭代后可能失效。
• 使用动态SQL时，必须严格控制${Func}的取值范围（比如限制为'avg'、'50'、'95'、'99'等固定值），避免SQL注入风险。

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