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

From: Alexander Farber <alexander(dot)farber(at)gmail(dot)com>
To: pgsql-general <pgsql-general(at)postgresql(dot)org>
Subject: Subtract one array from another, both with non-unique elements
Date: 2016-03-06 17:22:11
Message-ID: CAADeyWhTRCUdbCCWRGkKrtP01NYOs+DXKDU5g=qQX2SneHGqoQ@mail.gmail.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-general

Hello,

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.

Here is my stored procedure sofar (apologies for non-english chars):

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;
END
$func$ LANGUAGE plpgsql;

Thanks
Alex

Responses

Browse pgsql-general by date

  From Date Subject
Next Message bricklen 2016-03-06 17:34:07 Re: Subtract one array from another, both with non-unique elements
Previous Message Adrian Klaver 2016-03-06 15:26:38 Re: PostgreSQL 9.5 and process REST calls enquiry