Re: How to delete few elements from array beginning? - Mailing list pgsql-general

Hello fellow PostgreSQL users,

what is please the most efficient way to delete a slice from the start of a longer array (after I have copied it to another array)?

Do I really have to copy a large slice of the array to itself, like in the last line here:

        pile_array := pile_array || swap_array;

        /* here I copy away swap_len elements */
        new_hand := pile_array[1:swap_len];

        /* here I don't know how to efficiently remove already copied elements */
        pile_array := pile_array[(swap_len + 1):ARRAY_LENGTH(pile_array, 1)];

or is there a better way?

Thank you

Alex

P.S. The listing of the entire stored function in question:

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
        i           integer;
        j           integer;
        swap_len    integer;
        hand_len    integer;
        pile_len    integer;
        swap_array  varchar[];
        pile_array  varchar[];
        old_hand    varchar[];
        new_hand    varchar[];
        hand_ignore boolean[];
BEGIN
        swap_array := STRING_TO_ARRAY(in_swap, NULL);
        swap_len := ARRAY_LENGTH(swap_array, 1);

        SELECT hand1, pile, ARRAY_LENGTH(hand1, 1), ARRAY_LENGTH(pile, 1)
        INTO old_hand, pile_array, hand_len, pile_len
        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, ARRAY_LENGTH(hand2, 1), ARRAY_LENGTH(pile, 1)
                INTO old_hand, pile_array, hand_len, pile_len
                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;

        pile_array := pile_array || swap_array;
        -- pile_array := words_shuffle(pile_array);
        new_hand := pile_array[1:swap_len];
        pile_array := pile_array[(swap_len + 1):pile_len]; /* XXX is it good? */

        hand_ignore := ARRAY_FILL(FALSE, ARRAY[hand_len]);

        <<hand_loop>>
        FOR i IN 1..hand_len LOOP
                FOR j IN 1..swap_len LOOP
                        IF hand_ignore[j] = FALSE AND
                           old_hand[i] = swap_array[j] THEN
                                hand_ignore[j] := TRUE;
                                CONTINUE hand_loop;
                        END IF;
                END LOOP;

                new_hand := new_hand || old_hand[i];
        END LOOP;
/*
        UPDATE words_games
        SET hand1 = new_hand,
        pile = pile_array,
        played1 = CURRENT_TIMESTAMP
        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
                UPDATE words_games
                SET hand2 = new_hand,
                pile = pile_array,
                played2 = CURRENT_TIMESTAMP
                WHERE gid = in_gid
                AND player2 = in_uid
                -- and it is second player's turn
                AND (played2 IS NULL OR played2 < played1);
        END IF;
*/

END
$func$ LANGUAGE plpgsql;