Re: how to sort an array and remove duplicate in plpgsql

Hi all,

I'm messing up with this problem for a while and I searched the web
without success. I have an array of timestamp and I needed sorted and I
need to remove duplicate value. The Select statement offers the SORT BY and
UNIQUE that may help me but so far I didn't find the way to plug my array
variable into the select and get back the sorted array in return.

Any help or clue will be really appreciated!

Regards

David

am Mon, dem 26.02.2007, um 9:15:52 -0500 mailte David Gagnon folgendes:
> Hi all,
>
> I?m messing up with this problem for a while and I searched the web without
> success. I have an array of timestamp and I needed sorted and I need to remove
> duplicate value. The Select statement offers the SORT BY and UNIQUE that may
> help me but so far I didn?t find the way to plug my array variable into the
> select and get back the sorted array in return.

CREATE OR REPLACE FUNCTION array_sort (ANYARRAY)
RETURNS ANYARRAY
LANGUAGE SQL
AS $$
SELECT ARRAY(
SELECT $1[s.i] AS "foo"
FROM
generate_series(array_lower($1,1), array_upper($1,1)) AS s(i)
ORDER BY foo
);
$$;

You can change the 'SELECT $1' to 'SELECT DISTINCT $1' to remove
duplicate values.

Andreas
--
Andreas Kretschmer
Kontakt: Heynitz: 035242/47150, D1: 0160/7141639 (mehr: -> Header)
GnuPG-ID: 0x3FFF606C, privat 0x7F4584DA http://wwwkeys.de.pgp.net