Thread: Sorting array field

Hi,

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?

Something like this:

select sort(my_array_field) from my_table;

Thanks!

Pete

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

On Thu, Dec 22, 2005 at 08:38:46AM -0700, Pete Deffendol wrote:
> Hi,
>
> 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?
>
> Something like this:
>
> select sort(my_array_field) from my_table;

Here's one way using only SQL.  I do not make any guarantees about its
performance, though ;)

CREATE TABLE my_table (my_array text[]);
INSERT INTO my_table VALUES('{r,e,d,q}');
INSERT INTO my_table VALUES('{c,b,a}');
INSERT INTO my_table VALUES('{one,two,three,four}');

SELECT
    ARRAY(
        SELECT t.my_array[s.i]
        FROM generate_series(
            array_lower(my_array,1), /* usually 1 */
            array_upper(my_array,1)
        ) AS s(i)
        ORDER BY t.my_array[s.i]
    ) AS "sorted_array"
FROM my_table t
ORDER BY "sorted_array" DESC;

HTH :)

Cheers,
D
--
David Fetter david@fetter.org http://fetter.org/
phone: +1 415 235 3778

Remember to vote!