Thread: Why no "array_sort" function?

Why no "array_sort" function?

From
Fabien COELHO
Date:
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.



Re: Why no "array_sort" function?

From
Sergei Kornilov
Date:
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



Re: Why no "array_sort" function?

From
Fabien COELHO
Date:
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.