Comibining UPDATE ... SET ... FROM (SELECT ...) with a JOIN

[Alexander Farber <alexander.farber@xxxxxxxxx>]

Good afternoon,

I have a question please.

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?

Thank you

Alex