Good afternoon, for a 2-player game I store moves in the following 9.5.4 table:
CREATE TYPE words_action AS ENUM ('play', 'skip', 'swap', 'resign'); CREATE TABLE words_moves ( mid SERIAL PRIMARY KEY, action words_action NOT NULL, gid integer NOT NULL REFERENCES words_games ON DELETE CASCADE, uid integer NOT NULL REFERENCES words_users ON DELETE CASCADE, played timestamptz NOT NULL, tiles jsonb, score integer CHECK (score > 0) ); ALTER TABLE words_games ADD CONSTRAINT words_mid_fk FOREIGN KEY (mid) REFERENCES words_moves; And then I have a custom function for skipping a move (and inserting a 'skip' into the above table): CREATE OR REPLACE FUNCTION words_skip_game( IN in_uid integer, IN in_gid integer, OUT out_gid integer) RETURNS integer AS $func$ DECLARE _finished timestamptz; _score1 integer; _score2 integer; _uid2 integer; BEGIN INSERT INTO words_moves ( action, gid, uid, played ) VALUES ( 'skip', in_gid, in_uid, CURRENT_TIMESTAMP ); Could you please suggest a good way to check that the last 6 moves where 'skip', so that I can end the game when each player skipped her move 3 times in a row? IF /* there are 6 'skip's - how to do it please? */ THEN _finished = CURRENT_TIMESTAMP; END IF; Below is the rest of my function, thank you for any ideas - Regards Alex UPDATE words_games SET finished = _finished, played1 = CURRENT_TIMESTAMP WHERE gid = in_gid AND player1 = in_uid AND finished IS NULL AND -- and it is first player's turn (played1 IS NULL OR played1 < played2) RETURNING gid, score1, score2, player2 INTO out_gid, _score1, -- my score _score2, -- her score _uid2; IF NOT FOUND THEN UPDATE words_games SET finished = _finished, played2 = CURRENT_TIMESTAMP WHERE gid = in_gid AND player2 = in_uid AND finished IS NULL AND -- and it is second player's turn (played2 IS NULL OR played2 < played1); RETURNING gid, score2, -- swapped score1, player1 INTO out_gid, _score1, -- my score _score2, -- her score _uid2; END IF; IF NOT FOUND THEN RAISE EXCEPTION 'Game % not found for user %', in_gid, in_uid; END IF; -- game over, update win/loss/draw stat for both players IF _finished IS NOT NULL THEN IF _score1 > _score2 THEN UPDATE words_users SET win = win + 1 WHERE uid = in_uid; UPDATE words_users SET loss = loss + 1 WHERE uid = _uid2; ELSIF _score1 < _score2 THEN UPDATE words_users SET loss = loss + 1 WHERE uid = in_uid; UPDATE words_users SET win = win + 1 WHERE uid = _uid2; ELSE UPDATE words_users SET draw = draw + 1 WHERE uid = in_uid OR uid = _uid2; END IF; END IF; END $func$ LANGUAGE plpgsql;