From: | Alexander Farber <alexander(dot)farber(at)gmail(dot)com> |
---|---|
To: | pgsql-general <pgsql-general(at)postgresql(dot)org> |
Subject: | Check if there 6 last records of same type without gaps |
Date: | 2016-09-06 10:32:00 |
Message-ID: | CAADeyWimsH=cu8j9VKCqJHzWdPwTiLUHeEpO3GM_Mh5xafudBQ@mail.gmail.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-general |
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;
From | Date | Subject | |
---|---|---|---|
Next Message | Sándor Daku | 2016-09-06 11:30:59 | Re: Check if there 6 last records of same type without gaps |
Previous Message | Attila Soki | 2016-09-06 09:35:35 | pgadmin4 rc1 query tool performance |