From: | Alexander Farber <alexander(dot)farber(at)gmail(dot)com> |
---|---|
To: | |
Cc: | pgsql-general <pgsql-general(at)postgresql(dot)org> |
Subject: | Re: Subtract one array from another, both with non-unique elements |
Date: | 2016-03-08 13:28:22 |
Message-ID: | CAADeyWgUbqu6L1rnu5ebixv9hwjf_7Yde9Rf4B7NctOkkYjRbg@mail.gmail.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-general |
Hello again,
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):
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;
from_ignore boolean[];
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;
from_ignore := ARRAY_FILL(FALSE,
ARRAY[ARRAY_LENGTH(from_array, 1)]);
remove_ignore := ARRAY_FILL(FALSE,
ARRAY[ARRAY_LENGTH(remove_array, 1)]);
RAISE NOTICE 'from_ignore = %', from_ignore;
RAISE NOTICE 'remove_ignore = %', remove_ignore;
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 from_ignore[i] = FALSE AND
remove_ignore[j] = FALSE AND
from_array[i] = remove_array[j] THEN
from_ignore[i] := TRUE;
remove_ignore[j] := TRUE;
END IF;
END LOOP;
END LOOP;
FOR i IN ARRAY_LOWER(from_array,
1)..ARRAY_UPPER(from_array, 1) LOOP
IF from_ignore[i] = FALSE THEN
result_array := ARRAY_APPEND(result_array,
from_array[i]);
END IF;
END LOOP;
RETURN result_array;
END;
$func$ LANGUAGE plpgsql;
# select words_array_subtract(ARRAY['A','A','B','B','C'], ARRAY['A','B']);
NOTICE: from_ignore = {f,f,f,f,f}
NOTICE: remove_ignore = {f,f}
words_array_subtract
----------------------
{A,B,C}
(1 row)
# select words_array_subtract(ARRAY[1,2,2,3,4,4, 5], ARRAY[2,4,4]);
NOTICE: from_ignore = {f,f,f,f,f,f,f}
NOTICE: remove_ignore = {f,f,f}
words_array_subtract
----------------------
{1,2,3,5}
(1 row)
If you have any improvement proposals please tell me.
Regards
Alex
From | Date | Subject | |
---|---|---|---|
Next Message | Alexander Farber | 2016-03-08 13:59:40 | Re: Subtract one array from another, both with non-unique elements |
Previous Message | Berend Tober | 2016-03-08 10:50:02 | Re: Logger into table and/or to cli |