如何简化PostgreSQL中distinct+json_agg+coalesce的聚合查询？

我多次编写如下查询语句：

select coalesce(json_agg(distinct id), '[]'::json) as id_list from table

希望创建一个distinct_list(id)函数将其简化为：

select distinct_list(id) as id_list from table

避免重复编写。查阅PostgreSQL用户自定义聚合文档后，发现似乎需要重写coalesce/json_agg/distinct的全部功能，我不想这么做，是否有更简便的方法？

不用重写整套聚合逻辑，直接用标量函数封装现有原生逻辑即可，步骤如下：

创建封装函数 #

CREATE OR REPLACE FUNCTION distinct_list(p_id anyelement)
RETURNS json
LANGUAGE sql
IMMUTABLE
AS $$
  SELECT coalesce(json_agg(DISTINCT p_id), '[]'::json)
$$;

使用方式 #

直接按你期望的语法调用：

SELECT distinct_list(id) as id_list FROM your_table;

补充说明 #

• 参数类型用anyelement，让函数支持任意可转换为JSON的字段类型（如整数、文本等）
• 标记IMMUTABLE能让PostgreSQL进行查询优化，适合输入确定则输出确定的场景
• 函数完全复用PostgreSQL原生的json_agg和DISTINCT逻辑，无需自行实现聚合核心功能

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