Re: Sorting array field

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