Thread: 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
RETURNS SETOF anyelement AS
$BODY$
SELECT ($1)[s] FROM generate_series(1, array_upper($1, 1)) AS s;
$BODY$
LANGUAGE 'sql' IMMUTABLE;
ALTER FUNCTION explode_array(in_array anyarray) OWNER TO postgres;
From: pgsql-general-owner@postgresql.org [mailto:pgsql-general-owner@postgresql.org] On Behalf Of David Gagnon
Sent: 26 February 2007 14:16
To: pgsql-general@postgresql.org
Subject: [GENERAL] 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
_ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ This e-mail is confidential and may be read only by the intended recipient. If you are not the intended recipient, please do not forward, copy or take any action based on it and, in addition, please delete this email and inform the sender. We cannot be sure that this e-mail or its attachments are free from viruses. In keeping with good computing practice, please ensure that you take adequate steps to check for any viruses. Before replying or sending any email to us, please consider that the internet is inherently insecure and is an inappropriate medium for certain kinds of information. We reserve the right to access and read all e-mails and attachments entering or leaving our systems. Registered office: Eurocom House, Ashbourne Road, Derby DE22 4NB Company number: 01574696. |