Re: how to sort an array and remove duplicate in plpgsql - Mailing list pgsql-general

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