postgresql - array_agg DISTINCT 和 ORDER

Question

我正在尝试在 PostgreSQL 中使用左连接横向查询包含来自 2 个(或更多)表的结果，我需要为表 entidad_a_(主表)的每条记录和来自表 entidad_b_ 必须包含在 array_agg 生成的一个字段中。在这个数组中，我必须删除重复的元素，并且必须在主表中保留顺序数组。我需要执行这个 SQL 查询:

SELECT entidad_a_._id_ AS "_id", CASE WHEN count(entidadB) > 0 THEN array_agg(DISTINCT entidadB._id,ordinality order by ordinality)
ELSE NULL END AS "entidadB"
FROM entidad_a_ as entidad_a_, unnest(entidad_a_.entidad_b_) WITH ORDINALITY AS u(entidadb_id, ordinality)
LEFT JOIN LATERAL (
SELECT entidad_b_3._id_ AS "_id", entidad_b_3.label_ AS "label"
FROM entidad_b_ as entidad_b_3
WHERE entidad_b_3._id_ = entidadb_id
GROUP BY entidad_b_3._id_
LIMIT 1000 OFFSET 0
) entidadB ON TRUE
GROUP BY entidad_a_._id_
LIMIT 1000 OFFSET 0

但是我有错误....我怎样才能得到这些结果？

已编辑:我的错误是:错误:函数 array_agg (integer, bigint) 不存在SQL 状态:42883提示:没有函数匹配给定的名称和参数类型。您可能需要添加显式类型转换。字符数:69

如果查询是:......array_agg (DISTINCT entidadB._id order by ordinality)......错误是:错误:在具有 DISTINCT 的聚合中，ORDER BY 表达式必须出现在参数列表中SQL状态:42P10字符:110

我的问题是 array_agg、DISTINCT 和 ORDER by 的组合

Answer 1

解决了!!我创建了一个带有自定义聚合的 postgres 扩展。

    CREATE AGGREGATE array_agg_dist (anyelement)
(
    sfunc = array_agg_transfn_dist,
    stype = internal,
    finalfunc = array_agg_finalfn_dist,
    finalfunc_extra
);

为此自定义函数创建函数和 C 代码。