From: | Alexander Farber <alexander(dot)farber(at)gmail(dot)com> |
---|---|
To: | pgsql-general <pgsql-general(at)postgresql(dot)org> |
Subject: | How to delete few elements from array beginning? |
Date: | 2016-03-09 12:10:50 |
Message-ID: | CAADeyWj7VevahUVkb+zthj6TkZCg7LCpAQ+2mu=rDpkhZqtzkw@mail.gmail.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | 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;
From | Date | Subject | |
---|---|---|---|
Next Message | Alexandru Lazarev | 2016-03-09 12:18:50 | How does PostgreSQL serializable transaction works? (SELECT/UPDATE vs INSERT conflict) |
Previous Message | Albe Laurenz | 2016-03-09 10:45:46 | Re: Windows default directory for client certificates |