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.

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.

Answer 3

Just use the function unnest():

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

See array functions in the Pg docs.

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

Answer 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
);
$$;

Answer 6

If you're looking for a solution which will work across any data-type, I'd recommend taking the approach laid out at YouLikeProgramming.com.

Essentially, you can create a stored procedure (code below) which performs the sorting for you, and all you need to do is pass your array to that procedure for it to be sorted appropriately.

I have also included an implementation which does not require the use of a stored procedure, if you're looking for your query to be a little more transportable.

Creating the stored procedure

DROP FUNCTION IF EXISTS array_sort(anyarray);
CREATE FUNCTION
  array_sort(
    array_vals_to_sort anyarray
  )
  RETURNS TABLE (
    sorted_array anyarray
  )
  AS $BODY$
    BEGIN
      RETURN QUERY SELECT
        ARRAY_AGG(val) AS sorted_array
      FROM
        (
          SELECT
            UNNEST(array_vals_to_sort) AS val
          ORDER BY
            val
        ) AS sorted_vals
      ;
    END;
  $BODY$
LANGUAGE plpgsql;

Sorting array values (works with any array data-type)

-- The following will return: {1,2,3,4}
SELECT ARRAY_SORT(ARRAY[4,3,2,1]);

-- The following will return: {in,is,it,on,up}
SELECT ARRAY_SORT(ARRAY['up','on','it','is','in']);

Sorting array values without a stored procedure

In the following query, simply replace ARRAY[4,3,2,1] with your array or query which returns an array:

WITH
  sorted_vals AS (
    SELECT
      UNNEST(ARRAY[4,3,2,1]) AS val
    ORDER BY
      val
  )
SELECT
  ARRAY_AGG(val) AS sorted_array
FROM
  sorted_vals

... or ...

SELECT
  ARRAY_AGG(vals.val) AS sorted_arr
FROM (
  SELECT
    UNNEST(ARRAY[4,3,2,1]) AS val
  ORDER BY
    val
) AS vals

Answer 7

I'm surprised no-one has mentioned the containment operators:

select array[1,2,3] <@ array[2,1,3] and array[1,2,3] @> array[2,1,3];
 ?column?
══════════
 t
(1 row)

Notice that this requires that all elements of the arrays must be unique.

(If a contains b and b contains a, they must be the same if all elements are unique)