Re: Sorting array field - Mailing list pgsql-general

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!