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;