Postgres | function to sort a custom type array

Question

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

Jim Jones · Accepted Answer · 2022-07-19 13:49:49Z

1

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

answered Jul 19, 2022 at 13:49

Jim Jones

19.9k3 gold badges44 silver badges51 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

Postgres | function to sort a custom type array

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related