Re: 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(at)fetter(dot)org http://fetter.org/
phone: +1 415 235 3778

Remember to vote!