Get rid of bad header chars in the file. All else is the same.
[xonotic/xonstatdb.git] / 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 begin
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;
10
11    get diagnostics rowcount = ROW_COUNT;
12    raise notice '% weapon stat rows updated.', rowcount;
13
14    -- then game stats
15    update player_game_stats
16    set player_id = p_winner_player_id
17    where player_id = p_loser_player_id;
18
19    get diagnostics rowcount = ROW_COUNT;
20    raise notice '% game stat rows updated.', rowcount;
21
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);
27
28    -- update the existing player_elos entries that have a higher elo on the loser
29    update xonstat.player_elos pe
30    set elo = newpe.elo
31    from
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
38    where player_id = 67
39    and pe.game_type_cd = newpe.game_type_cd;
40
41    -- then hashkeys (winner takes the loser's hashkey)
42    update hashkeys
43    set player_id = p_winner_player_id
44    where player_id = p_loser_player_id;
45    
46    get diagnostics rowcount = ROW_COUNT;
47    raise notice '% hashkeys copied.', rowcount;
48
49    -- and finally deactivate the old player record
50    update players set active_ind = false where player_id = p_loser_player_id;
51 end;
52 $$
53 language plpgsql;