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 aggregate # of games and the *greatest* Elo
32 -- value and use that going forward
34 select game_type_cd, sum(games) sum_games,
35 max(elo) max_elo, max(update_dt) max_update_dt
37 where player_id in (p_winner_player_id, p_loser_player_id)
41 update xonstat.player_elos
42 set elo = r.max_elo, games = r.sum_games, update_dt = r.max_update_dt
43 where player_id = p_winner_player_id
44 and game_type_cd = r.game_type_cd
45 and games != r.sum_games;
47 raise notice 'New % Elo is %.', r.game_type_cd, r.max_elo;
50 -- then hashkeys (winner takes the loser's hashkey)
52 set player_id = p_winner_player_id
53 where player_id = p_loser_player_id;
55 get diagnostics rowcount = ROW_COUNT;
56 raise notice '% hashkeys copied.', rowcount;
58 -- and finally deactivate the old player record
59 update players set active_ind = false where player_id = p_loser_player_id;