myheats

commit d14f841de98fd3c2a110c7c952060d914b603dc3
parent a0260a400450109bcc6274d79689fb52fbd23e51
Author: Andreas Gruhler <andreas.gruhler@adfinis.com>
Date:   Sun, 12 Mar 2023 22:26:09 +0100

doc: sql views and functions

Diffstat:
M
README.md
 | 
29
++++++++++++++++++++++++++++-

1 file changed, 28 insertions(+), 1 deletion(-)
diff --git a/README.md b/README.md
@@ -4,7 +4,7 @@ Live heats, scoring and leaderboard for sport events.
 
 ## Running the App
 
-```
+```bash
 # Export Supabase database credential for anonymous access
 export REACT_APP_SUPABASE_KEY=
 
@@ -17,3 +17,29 @@ npm start
 The Supabase schema is stored in the `schema` folder and can be created using plain psql.
 
 ![supabase-schema](supabase-schema.png)
+
+Additionally, following views and functions are required:
+
+```sql
+-- Summarize all ratings of a heat
+create or replace view rating_summary as
+  select a.id as athlete_id, r.heat as heat_id, SUM(r.rating) as rating_summary
+  from ratings r
+  join athletes as a on a.id = r.athlete
+  group by a.id, r.heat
+
+-- Create distinct set of athletes (startlist) from a set of heats
+create or replace function distinct_startlist(heat_ids numeric[])
+returns table(id bigint, athlete bigint, nr bigint, firstname varchar, lastname varchar,
+birthday date, school varchar)
+language plpgsql
+as $$
+begin
+  return query
+  select distinct on (a.id) s.id, a.id, a.nr, a.firstname, a.lastname, a.birthday, a.school
+  from startlist as s
+  join athletes as a on s.athlete = a.id
+  where s.heat = any (heat_ids);
+end;
+$$;
+```
+\ No newline at end of file