functions/merge_players.sql

   1 create or replace function merge_players(p_winner_player_id players.player_id%TYPE, p_loser_player_id players.player_id%TYPE) RETURNS void as
   2 $$
   3 declare
   4    rowcount integer;
   5    weighted_elo record;
   6 begin
   7    raise notice 'Merging % and %', p_winner_player_id, p_loser_player_id;
   8
   9    -- start with weapon stats
  10    update player_weapon_stats
  11    set player_id = p_winner_player_id
  12    where player_id = p_loser_player_id;
  13
  14    get diagnostics rowcount = ROW_COUNT;
  15    raise notice '% weapon stat rows updated.', rowcount;
  16
  17    -- then game stats
  18    update player_game_stats
  19    set player_id = p_winner_player_id
  20    where player_id = p_loser_player_id;
  21
  22    get diagnostics rowcount = ROW_COUNT;
  23    raise notice '% game stat rows updated.', rowcount;
  24
  25    -- copy player_elos entries that don't currently exist for the winner
  26    insert into xonstat.player_elos
  27    select p_winner_player_id, game_type_cd, games, elo from xonstat.player_elos pe
  28    where player_id = p_loser_player_id
  29    and not exists (select 1 from xonstat.player_elos where player_id = p_winner_player_id and game_type_cd = pe.game_type_cd);
  30
  31    -- take the weighted average of the elos and sum the games to get the new total
  32    FOR weighted_elo IN (select game_type_cd, elo_sum/game_count aggregate_elo, game_count
  33       from (
  34          select game_type_cd, sum(greatest(games, 1)*elo) elo_sum, greatest(sum(games),1) game_count
  35          from player_elos
  36          where player_id in (p_winner_player_id, p_loser_player_id)
  37          group by game_type_cd
  38       ) es)
  39    LOOP
  40       update xonstat.player_elos
  41       set elo = weighted_elo.aggregate_elo, games = weighted_elo.game_count
  42       where player_id = p_winner_player_id
  43       and game_type_cd = weighted_elo.game_type_cd;
  44
  45       raise notice 'New % Elo is %.', weighted_elo.game_type_cd, weighted_elo.aggregate_elo;
  46    END LOOP;
  47
  48    -- update the existing player_elos entries that have a higher elo on the loser
  49    -- update xonstat.player_elos pe
  50    -- set elo = newpe.elo
  51    -- from
  52       -- (select lpe.game_type_cd, lpe.elo
  53       -- from xonstat.player_elos wpe, xonstat.player_elos lpe
  54       -- where wpe.game_type_cd = lpe.game_type_cd
  55       -- and wpe.player_id = 67
  56       -- and lpe.player_id = 720
  57       -- and lpe.elo > wpe.elo) newpe
  58    -- where player_id = 67
  59    -- and pe.game_type_cd = newpe.game_type_cd;
  60
  61    -- then hashkeys (winner takes the loser's hashkey)
  62    update hashkeys
  63    set player_id = p_winner_player_id
  64    where player_id = p_loser_player_id;
  65
  66    get diagnostics rowcount = ROW_COUNT;
  67    raise notice '% hashkeys copied.', rowcount;
  68
  69    -- and finally deactivate the old player record
  70    update players set active_ind = false where player_id = p_loser_player_id;
  71 end;
  72 $$
  73 language plpgsql;