Good evening,
in PostgreSQL 10.3 I have written the following custom function (trying to
fetch 10 latest games played by a user):
CREATE OR REPLACE FUNCTION words_stat_games(
in_social integer,
in_sid text
) RETURNS TABLE (
out_gid integer,
out_reason text,
out_state1 text,
out_score1 integer,
out_score2 integer
) AS
$func$
SELECT
g.gid,
g.reason,
CASE WHEN g.player1 = s.uid THEN g.state1 ELSE g.state2 END,
CASE WHEN g.player1 = s.uid THEN g.score1 ELSE g.score2 END,
CASE WHEN g.player1 = s.uid THEN g.score2 ELSE g.score1 END
FROM words_games g
JOIN words_social s ON (g.player1 = s.uid OR g.player2 = s.uid)
WHERE g.finished IS NOT NULL
ORDER BY g.finished DESC
LIMIT 10;
$func$ LANGUAGE sql STABLE;
Unfortunately, it returns multiple records and with wrong values too:
# select * from words_stat_games(1, '109998440415755555271');
out_gid | out_reason | out_state1 | out_score1 | out_score2
---------+------------+------------+------------+------------
1978 | resigned | lost | 0 | 0
1978 | resigned | won | 0 | 0
1847 | resigned | lost | 234 | 441
1847 | resigned | won | 441 | 234
1847 | resigned | won | 441 | 234
1800 | expired | won | 41 | 0
1798 | expired | lost | 8 | 28
1798 | expired | won | 28 | 8
1800 | expired | lost | 0 | 41
1926 | expired | won | 35 | 13
(10 rows)
Why does it return the game 1978 twice and also the out_state1 changes
between 'lost' and 'won' values?
I hoped to handle that with my "CASE WHEN g.player1 = s.uid THEN g.state1
ELSE g.state2 END", but it obviously fails
Below are my 2 table definitions, thank you for any hints.
CREATE TABLE words_social (
sid text NOT NULL,
social integer NOT NULL CHECK (0 < social AND social <= 64),
uid integer NOT NULL REFERENCES words_users ON DELETE CASCADE,
PRIMARY KEY(sid, social)
)
CREATE TABLE words_games (
gid SERIAL PRIMARY KEY,
created timestamptz NOT NULL,
finished timestamptz,
player1 integer REFERENCES words_users(uid) ON DELETE CASCADE NOT
NULL CHECK (player1 <> player2),
player2 integer REFERENCES words_users(uid) ON DELETE CASCADE,
reason text, -- regular, resigned, expired, banned
state1 text, -- tie, winning, losing, draw, won, lost
state2 text, -- tie, winning, losing, draw, won, lost
score1 integer NOT NULL CHECK (score1 >= 0),
score2 integer NOT NULL CHECK (score2 >= 0)
);
CREATE INDEX words_games_state1_index ON words_games(state1);
CREATE INDEX words_games_state2_index ON words_games(state2);
CREATE INDEX words_games_reason_index ON words_games(reason);
