Postgres | function to sort a custom type array-CodePudding

I have (after group by clause) the next table:

country | tuple
--------------------------------
England | {"(5,666)","(3,333)"} 

USA     | {"(3,222)","(2,777)"}

India   | {"(2,444)","(4,555)"}

tuple is defined with:

CREATE TYPE tuple AS (id bigint, op text);

I want to write a funcion that will sort each array of tuple by the op field, so the result will be:

country | tuple
--------------------------------
England | {"(5,666)","(3,333)"} 

USA     | {"(2,777)","(3,222)"}

India   | {"(4,555)","(2,444)"}

I tried with:

CREATE OR REPLACE FUNCTION array_sort (ANYARRAY)
RETURNS ANYARRAY LANGUAGE SQL
AS $$
      SELECT ARRAY(SELECT unnest($1) ORDER BY 1 desc)
$$;

But it didn't do the trick, any help?

Thanks

CodePudding user response：

You can use a CTE to unnest and order the tuple records and in the outer query regroup them, e.g.

CREATE OR REPLACE FUNCTION array_sort (ANYARRAY)
RETURNS ANYARRAY LANGUAGE SQL
AS $$
  WITH j AS (SELECT unnest($1) AS op ORDER BY (unnest($1)).op DESC)
  SELECT array_agg(op) FROM j
$$;

Demo: db<>fiddle