Sorting array elements

Solution 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).

Solution 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.

Solution 3:

Just use the function unnest():

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

See array functions in the Pg docs.

Solution 4:

This worked for me from http://www.pgsql.cz/index.php/PostgreSQL_SQL_Tricks_I#General_array_sort

CREATE OR REPLACE FUNCTION array_sort (ANYARRAY)
RETURNS ANYARRAY LANGUAGE SQL
AS $$
SELECT ARRAY(
    SELECT $1[s.i] AS "foo"
    FROM
        generate_series(array_lower($1,1), array_upper($1,1)) AS s(i)
    ORDER BY foo
);
$$;

Please see Craig's answer since he is far more more knowledgable on Postgres and has a better answer. Also if possible vote to delete my answer.

Solution 5:

Very nice exhibition of PostgreSQL's features is general procedure for sorting by David Fetter.

CREATE OR REPLACE FUNCTION array_sort (ANYARRAY)
RETURNS ANYARRAY LANGUAGE SQL
AS $$
SELECT ARRAY(
    SELECT $1[s.i] AS "foo"
    FROM
        generate_series(array_lower($1,1), array_upper($1,1)) AS s(i)
    ORDER BY foo
);
$$;