gistfile1.sql

DO $$ 
<<outer_block>>
declare
	left_rating int;
	left_player varchar(100);
	left_scoring int;
	right_rating int;
	right_player varchar(100);
	right_scoring int;
begin
	drop table if exists elo_rating;
	create table elo_rating(player varchar(200) primary key, rating decimal);
	insert into elo_rating (SELECT player, 1600 FROM players);
	
	FOR left_player, left_scoring, right_player, right_scoring IN
	select 
		g."left" as left_player, 
		(CASE WHEN g.chosen = g."left" THEN 1 WHEN g.chosen = 'none' THEN 0.5 ELSE 0 END) as left_rating, 
		g."right" as right_player,
		(CASE WHEN g.chosen = g."right" THEN 1 WHEN g.chosen = 'none' THEN 0.5 ELSE 0 END) as right_rating
	from game g LOOP
		left_rating := (select rating from elo_rating where player = left_player limit 1);
		right_rating := (select rating from elo_rating where player = right_player limit 1);
		update elo_rating set rating = left_rating + 
				(/*K*/10 * (left_scoring - (1/power(10, (left_rating - right_rating)/400 + 1))))
				where player = left_player
				;
		update elo_rating set rating = right_rating + 
				(/*K*/10 * (right_scoring - (1/power(10, (right_rating - left_rating)/400 + 1))))
				where player = right_player;
	END LOOP;	
END outer_block $$;