99-init-db.sql (1355B)
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 -- Enable PostgreSQL publication: 27 -- * https://www.postgresql.org/docs/current/logical-replication-publication.html 28 -- * https://github.com/porsager/postgres#realtime-subscribe 29 -- Set wal_level to "logical" before creating subscriptions 30 alter system set wal_level = logical; 31 32 begin; 33 -- Remove the myheats_realtime publication 34 drop publication if exists myheats_realtime; 35 36 -- Re-create the myheats_realtime publication with no tables 37 create publication myheats_realtime; 38 commit; 39 40 -- Add a table to the publication 41 alter publication myheats_realtime add table scores;