Why no "array_sort" function?
Hello devs,
although having arrays is an anathema in a relational world, pg has them,
and I find it useful for some queries, mostly in an aggregation to show
in a compact way what items were grouped together.
There are a few functions available to deal with arrays. Among these
functions, there is no "array_sort". It is easy enough to provide one that
seems to work, such as:
CREATE OR REPLACE FUNCTION array_sort(a ANYARRAY) RETURNS ANYARRAY
IMMUTABLE STRICT AS $$
SELECT ARRAY_AGG(i) FROM (SELECT i FROM UNNEST(a) AS i ORDER BY 1) AS i;
$$ LANGUAGE sql;
but I'm afraid that is is not particularly efficient, and I'm not even
sure that it is deterministic (ok, the subquery is sorted, but the outside
query could still decide to scan it out of order for some reason?).
Is there a reason *not* to provide an "array_sort" function?
--
Fabien.
Hello
mostly in an aggregation to show
in a compact way what items were grouped together.
Aggregate functions have syntax for ordering: just "select array_agg(i order by i) from ...."
Described here: https://www.postgresql.org/docs/current/sql-expressions.html#SYNTAX-AGGREGATES
regards, Sergei
Hello Sergei,
Aggregate functions have syntax for ordering: just "select array_agg(i order by i) from ...."
Described here: https://www.postgresql.org/docs/current/sql-expressions.html#SYNTAX-AGGREGATES
Great, that's indeed enough for my usage, thanks for the tip!
The questions remains, why not provide an "array_sort", which could be
useful in other contexts?
--
Fabien.