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
7 raise notice 'Merging % and %', p_winner_player_id, p_loser_player_id;
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;
14 get diagnostics rowcount = ROW_COUNT;
15 raise notice '% weapon stat rows updated.', rowcount;
18 update player_game_stats
19 set player_id = p_winner_player_id
20 where player_id = p_loser_player_id;
22 get diagnostics rowcount = ROW_COUNT;
23 raise notice '% game stat rows updated.', rowcount;
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);
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
34 select game_type_cd, greatest(sum(games*elo),1) elo_sum, sum(games) game_count
36 where player_id in (p_winner_player_id, p_loser_player_id)
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;
45 raise notice 'New % Elo is %.', weighted_elo.game_type_cd, weighted_elo.aggregate_elo;
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
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;
61 -- then hashkeys (winner takes the loser's hashkey)
63 set player_id = p_winner_player_id
64 where player_id = p_loser_player_id;
66 get diagnostics rowcount = ROW_COUNT;
67 raise notice '% hashkeys copied.', rowcount;
69 -- and finally deactivate the old player record
70 update players set active_ind = false where player_id = p_loser_player_id;