Re: Subtract one array from another, both with non-unique elements

[Alexander Farber <alexander.farber@xxxxxxxxx>]

Here 1-pass version, if you have improvement suggestions, you are welcome -

DROP FUNCTION IF EXISTS words_array_subtract(anyarray, anyarray);
CREATE OR REPLACE FUNCTION words_array_subtract(from_array anyarray, remove_array anyarray)
        RETURNS anyarray AS
$func$
        DECLARE
                i integer;
                j integer;
                remove_ignore boolean[];
                result_array from_array%TYPE := '{}';
        BEGIN
                IF NOT from_array @> remove_array THEN
                        RAISE EXCEPTION '% does not contain %', from_array, remove_array;
                END IF;

                remove_ignore := ARRAY_FILL(FALSE, ARRAY[ARRAY_LENGTH(remove_array, 1)]);
                RAISE NOTICE 'remove_ignore = %', remove_ignore;

                <<from_loop>>
                FOR i IN ARRAY_LOWER(from_array, 1)..ARRAY_UPPER(from_array, 1) LOOP
                        FOR j IN ARRAY_LOWER(remove_array, 1)..ARRAY_UPPER(remove_array, 1) LOOP
                                IF remove_ignore[j] = FALSE AND
                                   from_array[i] = remove_array[j] THEN
                                        remove_ignore[j] := TRUE;
                                        CONTINUE from_loop;
                                END IF;
                        END LOOP;

                        result_array := ARRAY_APPEND(result_array, from_array[i]);
                END LOOP;

                RETURN result_array;
        END;
$func$ LANGUAGE plpgsql;

# select words_array_subtract(ARRAY[1,2,2,3,4,4, 5], ARRAY[2,4,4]);
NOTICE:  remove_ignore = {f,f,f}
 words_array_subtract
----------------------
 {1,2,3,5}
(1 row)

# select words_array_subtract(ARRAY['A','A','B','B','C'], ARRAY['A','B']);
NOTICE:  remove_ignore = {f,f}
 words_array_subtract
----------------------
 {A,B,C}
(1 row)

On Tue, Mar 8, 2016 at 2:28 PM, Alexander Farber <alexander.farber@xxxxxxxxx> wrote:

I could not find  a solution neither
at http://postgres.cz/wiki/Array_based_functions

nor at https://github.com/JDBurnZ/postgresql-anyarray

So I have written my own stored function

to subtract one non-unique array from another

(like swapping tiles in a word game):