Re: Sorting array field

[Michael Fuhr <mike@xxxxxxxx>]

On Thu, Dec 22, 2005 at 08:38:46AM -0700, Pete Deffendol wrote:
> Can anyone point me toward an SQL function (whether built-in or an add-on)
> that will allow me to sort the contents of an array datatype in an SQL
> query?

For integer arrays see contrib/intarray.

SELECT sort('{5,2,3,1,9,7}'::int[]);
     sort      
---------------
 {1,2,3,5,7,9}
(1 row)

I don't recall if any of the contrib modules can sort arrays of
other types; if not then look for something at a site like pgfoundry
or GBorg.  If you have PL/Ruby then it couldn't get much easier:

CREATE FUNCTION sort(arg text[]) RETURNS text[] AS $$
arg.sort
$$ LANGUAGE plruby IMMUTABLE STRICT;

SELECT sort('{zz,"xx yy",cc,aa,bb}'::text[]);
         sort          
-----------------------
 {aa,bb,cc,"xx yy",zz}
(1 row)

Another way would be to write a set-returning function that returns
each item in the array as a separate row, and another function that
uses an array constructor to put the rows back together in order
(this example should work in 7.4 and later):

CREATE FUNCTION array2rows(anyarray) RETURNS SETOF anyelement AS '
BEGIN
    FOR i IN array_lower($1, 1) .. array_upper($1, 1) LOOP
        RETURN NEXT $1[i];
    END LOOP;
    RETURN;
END;
' LANGUAGE plpgsql IMMUTABLE STRICT;

CREATE FUNCTION sort(anyarray) RETURNS anyarray AS '
SELECT array(SELECT * FROM array2rows($1) ORDER BY 1)
' LANGUAGE sql IMMUTABLE STRICT;

SELECT data, sort(data) FROM foo;
         data          |         sort          
-----------------------+-----------------------
 {dd,cc,bb,aa}         | {aa,bb,cc,dd}
 {zz,"xx yy",cc,aa,bb} | {aa,bb,cc,"xx yy",zz}
(2 rows)

I'm not sure if there are easier ways; these are what first came
to mind.

-- 
Michael Fuhr