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