How to delete few elements from array beginning?

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;

Responses

Browse pgsql-general by date

  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