deleting from arrays

On Sun, Jan 16, 2005 at 11:56:04PM -0600, mstory@uchicago.edu wrote:

I've searched the documentation for a simple way to delete a single value from
an array, i've come up with a complecated way to do it, but was wondering if
there was some simple command to remove a single value from an array, where the
position of the value in the array is unknown.

For integer arrays see the contrib/intarray module. Otherwise you
could write a function and create an operator around it -- maybe
there's an easier way, but the following works for arrays of any
type in simple tests:

CREATE FUNCTION array_remove(anyarray, anyelement) RETURNS anyarray AS '
DECLARE
a ALIAS FOR $1;
v ALIAS FOR $2;
newa a%TYPE := ''{}'';
i integer;
BEGIN
FOR i IN array_lower(a, 1) .. array_upper(a, 1) LOOP
IF a[i] <> v THEN
newa := array_append(newa, a[i]);
END IF;
END LOOP;

RETURN newa;
END;
' LANGUAGE plpgsql IMMUTABLE STRICT;

CREATE OPERATOR - (
LEFTARG = anyarray,
RIGHTARG = anyelement,
PROCEDURE = array_remove
);

SELECT '{bob,carol,ted,alice}'::text[] - 'carol';
?column?
-----------------
{bob,ted,alice}
(1 row)

SELECT '{2,3,5,7}'::int[] - 3;
?column?
----------
{2,5,7}
(1 row)

--
Michael Fuhr
http://www.fuhr.org/~mfuhr/