Sorting array elements

Question

I want to write a stored procedure that gets an array as input parameter and sort that array and return the sorted array.

Kindly help.

Answer 1

The best way to sort an array of integers is without a doubt to use the intarray extension, which will do it much, much, much faster than any SQL formulation:

CREATE EXTENSION intarray;  SELECT sort( ARRAY[4,3,2,1] ); 

A function that works for any array type is:

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

(I've replaced my version with Pavel's slightly faster one after discussion elsewhere).

Answer 2

In PostrgreSQL 8.4 and up you can use:

select array_agg(x) from (select unnest(ARRAY[1,5,3,7,2]) AS x order by x) as _; 

But it will not be very fast.

---

In older Postgres you can implement unnest like this

CREATE OR REPLACE FUNCTION unnest(anyarray)   RETURNS SETOF anyelement AS $BODY$ SELECT $1[i] FROM     generate_series(array_lower($1,1),                     array_upper($1,1)) i; $BODY$   LANGUAGE 'sql' IMMUTABLE 

And array_agg like this:

CREATE AGGREGATE array_agg (         sfunc = array_append,         basetype = anyelement,         stype = anyarray,         initcond = '{}' ); 

But it will be even slower.

---

You can also implement any sorting algorithm in pl/pgsql or any other language you can plug in to postgres.