afarber/words_skip_game.sql

## gistfile1.txt
CREATE OR REPLACE FUNCTION words_skip_game(
                in_uid integer,
                in_gid integer
        ) RETURNS TABLE (
                out_uid integer, -- the player to be notified
                out_fcm text,
                out_apns text,
                out_sns text,
                out_note text
        ) AS
$func$
DECLARE
        _skips    integer;
        _finished timestamptz;
        _opponent integer;
        _score1   integer;
        _score2   integer;
BEGIN
        IF EXISTS (SELECT 1 FROM words_users
                            WHERE uid = in_uid AND
                            banned_until > CURRENT_TIMESTAMP) THEN
                RAISE EXCEPTION 'User % is banned', in_uid;
        END IF;

        INSERT INTO words_moves (
                action,
                gid,
                uid,
                played
        ) VALUES (
                'skip',
                in_gid,
                in_uid,
                CURRENT_TIMESTAMP
        );

        SELECT SUM(skips) FROM (
                SELECT CASE WHEN action='skip' OR action='swap' THEN 1 ELSE 0 END AS skips, played
                FROM words_moves
                WHERE gid = in_gid
                ORDER BY played DESC
                LIMIT 6
        ) last_six_moves INTO _skips;

        IF _skips = 6 THEN
                _finished = CURRENT_TIMESTAMP;
        END IF;

        UPDATE words_games SET
                finished = _finished,
                played1  = CURRENT_TIMESTAMP
        WHERE
                gid      = in_gid AND
                player1  = in_uid AND
                -- game is not over yet
                finished IS NULL  AND
                -- and it is first player's turn
                (played1 IS NULL OR played1 < played2)
        RETURNING
                player2,
                score1,
                score2
        INTO
                _opponent,
                _score1,
                _score2;

        IF NOT FOUND THEN
                UPDATE words_games SET
                        finished = _finished,
                        played2  = CURRENT_TIMESTAMP
                WHERE
                        gid      = in_gid AND
                        player2  = in_uid AND
                        -- game is not over yet
                        finished IS NULL  AND
                        -- and it is second player's turn
                        (played2 IS NULL OR played2 < played1)
                RETURNING
                        player1,
                        score2,
                        score1
                INTO
                        _opponent,
                        _score1,
                        _score2;
        END IF;

        IF NOT FOUND THEN
                RAISE EXCEPTION 'Game % not found for user %', in_gid, in_uid;
        END IF;

        -- the game continues
        IF _finished IS NULL THEN
                SELECT
                        _opponent,
                        fcm,
                        apns,
                        sns,
                        CASE
                                WHEN _skips = 5 THEN 'Противник пропускает ход (еще один пропуск завершит игру)'
                                ELSE 'Противник пропускает ход'
                        END
                FROM    words_users
                WHERE   uid = _opponent
                INTO STRICT
                        out_uid,
                        out_fcm,
                        out_apns,
                        out_sns,
                        out_note;
                RETURN NEXT;
        -- the game is over because of 6 skips in the row
        ELSE
                SELECT
                        in_uid,
                        fcm,
                        apns,
                        sns,
                        CASE
                                WHEN _score1 > _score2 THEN 'Победа: '
                                WHEN _score1 < _score2 THEN 'Поражение: '
                                ELSE 'Ничья: '
                        END || _score1 || ':' || _score2
                FROM    words_users
                WHERE   uid = in_uid
                INTO STRICT
                        out_uid,
                        out_fcm,
                        out_apns,
                        out_sns,
                        out_note;
                RETURN NEXT;

                SELECT
                        _opponent,
                        fcm,
                        apns,
                        sns,
                        CASE
                                WHEN _score2 > _score1 THEN 'Победа: '
                                WHEN _score2 < _score1 THEN 'Поражение: '
                                ELSE 'Ничья: '
                        END || _score2 || ':' || _score1
                FROM    words_users
                WHERE   uid = _opponent
                INTO STRICT
                        out_uid,
                        out_fcm,
                        out_apns,
                        out_sns,
                        out_note;
                RETURN NEXT;
        END IF;
END
$func$ LANGUAGE plpgsql;


## words_skip_game.sql
CREATE OR REPLACE FUNCTION words_skip_game(
                in_uid integer,
                in_gid integer
        ) RETURNS TABLE (
                out_uid integer, -- the player to be notified
                out_fcm text,
                out_apns text,
                out_sns text,
                out_note text
        ) AS
$func$
DECLARE
        _skips    integer;
        _finished timestamptz;
        _opponent integer;
        _score1   integer;
        _score2   integer;
BEGIN
        IF EXISTS (SELECT 1 FROM words_users
                            WHERE uid = in_uid AND
                            banned_until > CURRENT_TIMESTAMP) THEN
                RAISE EXCEPTION 'User % is banned', in_uid;
        END IF;

        INSERT INTO words_moves (
                action,
                gid,
                uid,
                played
        ) VALUES (
                'skip',
                in_gid,
                in_uid,
                CURRENT_TIMESTAMP
        );

        SELECT SUM(skips) FROM (
                SELECT CASE WHEN action='skip' OR action='swap' THEN 1 ELSE 0 END AS skips, played
                FROM words_moves
                WHERE gid = in_gid
                ORDER BY played DESC
                LIMIT 6
        ) last_six_moves INTO _skips;

        IF _skips = 6 THEN
                _finished = CURRENT_TIMESTAMP;
        END IF;

        UPDATE words_games SET
                finished = _finished,
                played1  = CURRENT_TIMESTAMP
        WHERE
                gid      = in_gid AND
                player1  = in_uid AND
                -- game is not over yet
                finished IS NULL  AND
                -- and it is first player's turn
                (played1 IS NULL OR played1 < played2)
        RETURNING
                player2,
                score1,
                score2
        INTO
                _opponent,
                _score1,
                _score2;

        IF NOT FOUND THEN
                UPDATE words_games SET
                        finished = _finished,
                        played2  = CURRENT_TIMESTAMP
                WHERE
                        gid      = in_gid AND
                        player2  = in_uid AND
                        -- game is not over yet
                        finished IS NULL  AND
                        -- and it is second player's turn
                        (played2 IS NULL OR played2 < played1)
                RETURNING
                        player1,
                        score2,
                        score1
                INTO
                        _opponent,
                        _score1,
                        _score2;
        END IF;

        IF NOT FOUND THEN
                RAISE EXCEPTION 'Game % not found for user %', in_gid, in_uid;
        END IF;

        -- the game continues
        IF _finished IS NULL THEN
                SELECT
                        _opponent,
                        fcm,
                        apns,
                        sns,
                        CASE
                                WHEN _skips = 5 THEN 'Противник пропускает ход (еще один пропуск завершит игру)'
                                ELSE 'Противник пропускает ход'
                        END
                FROM    words_users
                WHERE   uid = _opponent
                INTO STRICT
                        out_uid,
                        out_fcm,
                        out_apns,
                        out_sns,
                        out_note;
                RETURN NEXT;
        -- the game is over because of 6 skips in the row
        ELSE
                SELECT
                        in_uid,
                        fcm,
                        apns,
                        sns,
                        CASE
                                WHEN _score1 > _score2 THEN 'Победа: '
                                WHEN _score1 < _score2 THEN 'Поражение: '
                                ELSE 'Ничья: '
                        END || _score1 || ':' || _score2
                FROM    words_users
                WHERE   uid = in_uid
                INTO STRICT
                        out_uid,
                        out_fcm,
                        out_apns,
                        out_sns,
                        out_note;
                RETURN NEXT;

                SELECT
                        _opponent,
                        fcm,
                        apns,
                        sns,
                        CASE
                                WHEN _score2 > _score1 THEN 'Победа: '
                                WHEN _score2 < _score1 THEN 'Поражение: '
                                ELSE 'Ничья: '
                        END || _score2 || ':' || _score1
                FROM    words_users
                WHERE   uid = _opponent
                INTO STRICT
                        out_uid,
                        out_fcm,
                        out_apns,
                        out_sns,
                        out_note;
                RETURN NEXT;
        END IF;
END
$func$ LANGUAGE plpgsql;
	CREATE OR REPLACE FUNCTION words_skip_game(
	in_uid integer,
	in_gid integer
	) RETURNS TABLE (
	out_uid integer, -- the player to be notified
	out_fcm text,
	out_apns text,
	out_sns text,
	out_note text
	) AS
	$func$
	DECLARE
	_skips integer;
	_finished timestamptz;
	_opponent integer;
	_score1 integer;
	_score2 integer;
	BEGIN
	IF EXISTS (SELECT 1 FROM words_users
	WHERE uid = in_uid AND
	banned_until > CURRENT_TIMESTAMP) THEN
	RAISE EXCEPTION 'User % is banned', in_uid;
	END IF;

	INSERT INTO words_moves (
	action,
	gid,
	uid,
	played
	) VALUES (
	'skip',
	in_gid,
	in_uid,
	CURRENT_TIMESTAMP
	);

	SELECT SUM(skips) FROM (
	SELECT CASE WHEN action='skip' OR action='swap' THEN 1 ELSE 0 END AS skips, played
	FROM words_moves
	WHERE gid = in_gid
	ORDER BY played DESC
	LIMIT 6
	) last_six_moves INTO _skips;

	IF _skips = 6 THEN
	_finished = CURRENT_TIMESTAMP;
	END IF;

	UPDATE words_games SET
	finished = _finished,
	played1 = CURRENT_TIMESTAMP
	WHERE
	gid = in_gid AND
	player1 = in_uid AND
	-- game is not over yet
	finished IS NULL AND
	-- and it is first player's turn
	(played1 IS NULL OR played1 < played2)
	RETURNING
	player2,
	score1,
	score2
	INTO
	_opponent,
	_score1,
	_score2;

	IF NOT FOUND THEN
	UPDATE words_games SET
	finished = _finished,
	played2 = CURRENT_TIMESTAMP
	WHERE
	gid = in_gid AND
	player2 = in_uid AND
	-- game is not over yet
	finished IS NULL AND
	-- and it is second player's turn
	(played2 IS NULL OR played2 < played1)
	RETURNING
	player1,
	score2,
	score1
	INTO
	_opponent,
	_score1,
	_score2;
	END IF;

	IF NOT FOUND THEN
	RAISE EXCEPTION 'Game % not found for user %', in_gid, in_uid;
	END IF;

	-- the game continues
	IF _finished IS NULL THEN
	SELECT
	_opponent,
	fcm,
	apns,
	sns,
	CASE
	WHEN _skips = 5 THEN 'Противник пропускает ход (еще один пропуск завершит игру)'
	ELSE 'Противник пропускает ход'
	END
	FROM words_users
	WHERE uid = _opponent
	INTO STRICT
	out_uid,
	out_fcm,
	out_apns,
	out_sns,
	out_note;
	RETURN NEXT;
	-- the game is over because of 6 skips in the row
	ELSE
	SELECT
	in_uid,
	fcm,
	apns,
	sns,
	CASE
	WHEN _score1 > _score2 THEN 'Победа: '
	WHEN _score1 < _score2 THEN 'Поражение: '
	ELSE 'Ничья: '
	END \|\| _score1 \|\| ':' \|\| _score2
	FROM words_users
	WHERE uid = in_uid
	INTO STRICT
	out_uid,
	out_fcm,
	out_apns,
	out_sns,
	out_note;
	RETURN NEXT;

	SELECT
	_opponent,
	fcm,
	apns,
	sns,
	CASE
	WHEN _score2 > _score1 THEN 'Победа: '
	WHEN _score2 < _score1 THEN 'Поражение: '
	ELSE 'Ничья: '
	END \|\| _score2 \|\| ':' \|\| _score1
	FROM words_users
	WHERE uid = _opponent
	INTO STRICT
	out_uid,
	out_fcm,
	out_apns,
	out_sns,
	out_note;
	RETURN NEXT;
	END IF;
	END
	$func$ LANGUAGE plpgsql;