99-init-db.sql (1394B)
1 -- Summarize all scores of a heat 2 create or replace function score_summary(publicOnly boolean) 3 returns table(athlete_id bigint, heat_id bigint, score_summary double precision) 4 language plpgsql 5 set search_path = '' 6 as $$ 7 begin 8 return query 9 select a.id, s.heat, SUM(s.score) 10 from public.scores s 11 join public.athletes as a on a.id = s.athlete 12 join public.heats as h on s.heat = h.id 13 where (publicOnly = false or h.private = false) 14 group by a.id, s.heat; 15 end; 16 $$; 17 18 -- Create distinct set of athletes (startlist) from a set of heats 19 create or replace function distinct_startlist(heat_ids numeric[], publicOnly boolean) 20 returns table(id bigint, athlete bigint, nr bigint, firstname text, lastname text, 21 birthday date, school text) 22 language plpgsql 23 set search_path = '' 24 as $$ 25 begin 26 return query 27 select distinct on (a.id) s.id, a.id, a.nr, a.firstname, a.lastname, a.birthday, a.school 28 from public.startlist as s 29 join public.athletes as a on s.athlete = a.id 30 join public.heats as h on s.heat = h.id 31 where s.heat = any(heat_ids) and 32 (publicOnly = false or h.private = false); 33 end; 34 $$; 35 36 begin; 37 -- Remove the myheats_realtime publication 38 drop publication if exists myheats_realtime; 39 40 -- Re-create the myheats_realtime publication with no tables 41 create publication myheats_realtime; 42 commit; 43 44 -- Add a table to the publication 45 alter publication myheats_realtime add table scores;