Thread: deleting from arrays

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.

thanks,

matt

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/