Sorting array elements

Question

Hi ,

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

Just use the function unnest():

SELECT 
    unnest(ARRAY[1,2]) AS x
ORDER BY 
    x DESC;

http://www.postgresql.org/docs/8.4/interactive/functions-array.html

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.