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
6 -- start with weapon stats
7 update player_weapon_stats
8 set player_id = p_winner_player_id
9 where player_id = p_loser_player_id;
11 get diagnostics rowcount = ROW_COUNT;
12 raise notice '% weapon stat rows updated.', rowcount;
15 update player_game_stats
16 set player_id = p_winner_player_id
17 where player_id = p_loser_player_id;
19 get diagnostics rowcount = ROW_COUNT;
20 raise notice '% game stat rows updated.', rowcount;
22 -- copy player_elos entries that don't currently exist for the winner
23 insert into xonstat.player_elos
24 select p_winner_player_id, game_type_cd, games, elo from xonstat.player_elos pe
25 where player_id = p_loser_player_id
26 and not exists (select 1 from xonstat.player_elos where player_id = p_winner_player_id and game_type_cd = pe.game_type_cd);
28 -- update the existing player_elos entries that have a higher elo on the loser
29 update xonstat.player_elos pe
32 (select lpe.game_type_cd, lpe.elo
33 from xonstat.player_elos wpe, xonstat.player_elos lpe
34 where wpe.game_type_cd = lpe.game_type_cd
35 and wpe.player_id = 67
36 and lpe.player_id = 720
37 and lpe.elo > wpe.elo) newpe
39 and pe.game_type_cd = newpe.game_type_cd;
41 -- then hashkeys (winner takes the loser's hashkey)
43 set player_id = p_winner_player_id
44 where player_id = p_loser_player_id;
46 get diagnostics rowcount = ROW_COUNT;
47 raise notice '% hashkeys copied.', rowcount;
49 -- and finally deactivate the old player record
50 update players set active_ind = false where player_id = p_loser_player_id;