could someone please recommend the most efficient way subtracting elements of one array from the other in PostgreSQL 9.5?
Should I create a new array or can I work on the existing one (and if the latter - will FOREACH work well when elements are removed "from under its feet"?).
Both arrays contain non-unique letters and represent a hand of a player and a list of characters to be swapped.
words=> select words_swap_game(1,1,'ТЕ');
NOTICE: swap_array = {Т,Е}
NOTICE: hand_array = {Т,Ъ,Б,В,Е,О,Р}
NOTICE: pile_array = {С,Н,Л,Л,Д,П,Г,Ц,И,К,Ж,М,У,А,Д,Е,В,Г,Ч,О,*,Я,И,О,И,П,С,Е,О,Е,А,О,У,Т,З,К,А,Ы,Н,М,Н,Ф,Е,Н,Ь,Р,Ы,С,В,М,Д,Б,Й,П,Ш,Н,К,К,*,Ж,И,А,Л,Я,Е,М,М,Э,В,Р,О,Н,А,С,Й,Т,Н,А,П,А,Н,Р,И,К,Ю,О,Й,Е,А,Е,Д,О,Й,К,И,Д,С,Л,О,С,З,Х,П,И,Б,Т,И,Я,В,Щ,П,У,А,А,Е,Х}
words_swap_game
-----------------
(1 row)
CREATE OR REPLACE FUNCTION words_swap_game(
IN in_uid integer,
IN in_gid integer,
IN in_swap varchar(7))
RETURNS void AS
$func$
DECLARE
swap_array varchar[];
hand_array varchar[];
pile_array varchar[];
BEGIN
swap_array := (SELECT STRING_TO_ARRAY(in_swap, NULL));
RAISE NOTICE 'swap_array = %', swap_array;
SELECT hand1, pile
INTO hand_array, pile_array
FROM words_games
WHERE gid = in_gid
AND player1 = in_uid
/* and it is first player's turn */
AND (played1 IS NULL OR played1 < played2);
IF NOT FOUND THEN
SELECT hand2, pile
INTO hand_array, pile_array
FROM words_games
WHERE gid = in_gid
AND player2 = in_uid
/* and it is second player's turn */
AND (played2 IS NULL OR played2 < played1);
END IF;
RAISE NOTICE 'hand_array = %', hand_array;
RAISE NOTICE 'pile_array = %', pile_array;
IF NOT hand_array @> swap_array THEN
RAISE EXCEPTION 'Hand % does not contain swap %', hand_array, swap_array;
END IF;
FOREACH x IN ARRAY swap_array
LOOP
RAISE NOTICE 'x = %', x;
IF x = ANY(hand_array) THEN
RAISE NOTICE 'Found';
-- How to remove x from hand_array?
END IF;
END LOOP;