From: | David Fetter <david(at)fetter(dot)org> |
---|---|
To: | Pete Deffendol <peter(dot)deffendol(at)gmail(dot)com> |
Cc: | pgsql-general(at)postgresql(dot)org |
Subject: | Re: Sorting array field |
Date: | 2005-12-22 20:09:55 |
Message-ID: | 20051222200955.GB7772@fetter.org |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | 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(at)fetter(dot)org http://fetter.org/
phone: +1 415 235 3778
Remember to vote!
From | Date | Subject | |
---|---|---|---|
Next Message | Marko Kreen | 2005-12-22 21:09:37 | Re: Why is create function bringing down the Backend server? |
Previous Message | David Rysdam | 2005-12-22 18:48:10 | Re: reading EXPLAIN output |