02-distinct-startlist-private-heats.sql (744B)
1 -- remove the function with the old signature 2 drop function if exists distinct_startlist(heat_ids numeric[]); 3 4 -- add function that returns startlists considering the heat visibility 5 create or replace function distinct_startlist(heat_ids numeric[], publicOnly boolean) 6 returns table(id bigint, athlete bigint, nr bigint, firstname text, lastname text, 7 birthday date, school text) 8 language plpgsql 9 set search_path = '' 10 as $$ 11 begin 12 return query 13 select distinct on (a.id) s.id, a.id, a.nr, a.firstname, a.lastname, a.birthday, a.school 14 from public.startlist as s 15 join public.athletes as a on s.athlete = a.id 16 join public.heats as h on s.heat = h.id 17 where s.heat = any(heat_ids) and 18 (publicOnly = false or h.private = false); 19 end; 20 $$;