Search Postgresql Archives

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

[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]

 



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


[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]
[Index of Archives]     [Postgresql Jobs]     [Postgresql Admin]     [Postgresql Performance]     [Linux Clusters]     [PHP Home]     [PHP on Windows]     [Kernel Newbies]     [PHP Classes]     [PHP Books]     [PHP Databases]     [Postgresql & PHP]     [Yosemite]
  Powered by Linux