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

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