In one table I store user ids and their IP addresses -
CREATE TABLE words_users ( uid SERIAL PRIMARY KEY, ip inet NOT NULL );
And in another table I keep 2-player games and timestamps of last moves (NULL if a player hasn't played yet):
CREATE TABLE words_games ( gid SERIAL PRIMARY KEY, finished timestamptz,
player1 integer REFERENCES words_users(uid) ON DELETE CASCADE NOT NULL, player2 integer REFERENCES words_users(uid) ON DELETE CASCADE,
played1 timestamptz, played2 timestamptz );
When a user wants to start a new game, I first check if there is maybe a new game already available - with just 1 player while the other "seat" is vacant:
UPDATE words_games g1 SET player2 = in_uid FROM ( SELECT gid FROM words_games WHERE finished IS NULL AND player1 <> in_uid AND played1 IS NOT NULL AND player2 IS NULL LIMIT 1 FOR UPDATE SKIP LOCKED ) g2 WHERE g1.gid = g2.gid RETURNING g1.gid INTO out_gid;
This code works great, but now I am trying to add an (obviously not solving all cheating/proxy/etc. problems) check, that the IP addresses of both users must be different.
Fetching "ip" in the internal SELECT statement is trivial with:
UPDATE words_games g1 SET player2 = in_uid FROM ( SELECT g.gid, u.ip FROM words_games g, words_users u WHERE g.finished IS NULL AND g.player1 <> in_uid AND g.played1 IS NOT NULL AND g.player2 IS NULL
ON (g.player1 = u.uid)
LIMIT 1 FOR UPDATE SKIP LOCKED ) g2 WHERE g1.gid = g2.gid RETURNING g1.gid INTO out_gid;
But how to fetch the "ip" column in the surrounding UPDATE statement?