Re: Comibining UPDATE ... SET ... FROM (SELECT ...) with a JOIN - Mailing list pgsql-general

I'm making the assumption that you only have one ip/user in words_users.

with lockrow as (
   SELECT g.gid, u.ip
   FROM   words_games g join words_users u
        ON (g.player1 = u.uid)
  WHERE  g.finished IS NULL
    AND    g.player1 <> in_uid
    AND    g.played1 IS NOT NULL
    AND    g.player2 IS NULL
   LIMIT  1
   FOR    UPDATE SKIP LOCKED
), do_the_update as (
   UPDATE words_games g1
   SET    player2 = in_uid
   FROM lockrow g2
   WHERE     g1.gid = g2.gid
   RETURNING g1.gid, g1.player2
)
select m.gid into out_gid, u.ip into out_uip
from do_the_update m
  join lockrow u on (gid)
;

The general idea being lock the row in the first CTE, update it in the
second, returning your values, and then query against those in the
final select to get the ip.  If it didn't update anything, you'll get
no results.