[SOLVED] array_agg DISTINCT and ORDER

array_agg DISTINCT and ORDER

I'm trying to make a query in PostgreSQL to include results from multiple tables using left join lateral, with one record for each record for table entidad_a_ (main table) with all the records from table entidad_b_ included in one field generated by array_agg. In the array I have to delete duplicate elements and preserve main table order.

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

ERROR: function array_agg (integer, bigint) does not exist
SQL state: 42883 Hint: No function matches the given name and argument types.
You might need to add explicit type casts.
Character: 69

If the query instead has:

array_agg (DISTINCT entidadB._id order by ordinality)

ERROR: in an aggregate with DISTINCT, ORDER BY expressions must appear in argument list
SQL state: 42P10
Character: 110

My problem is the combination of array_agg, DISTINCT, and ORDER by.

How can I have these results?

Solution

I created a PostgreSQL extension with a custom aggregation.

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

I am creating functions and C code for this custom function.