\connect sportsdb

CREATE TYPE MEDAL_TYPE AS (
    medal_name VARCHAR(10),
    A INTEGER,
    Time VARCHAR(32)
);

--create table R_TRACK (
--       C VARCHAR(32) references TRACK(C),
--       A INTEGER references ATHLETES(A), /* VARCHAR(5)--> INTEGER */
--       Time VARCHAR(32));

CREATE OR REPLACE FUNCTION get_medals(c_target VARCHAR) RETURNS setof medal_type as
$$
    DECLARE
        r medal_type%rowtype;
        null_test VARCHAR(32);
        a_gold integer;
        a_gold_time VARCHAR(32);
        a_silver integer;
        a_silver_time VARCHAR(32);
        a_bronze integer;
        a_bronze_time VARCHAR(32);
    BEGIN
        -- does this competition exist?
        Select time into null_test from R_track where c = $1;
        IF(null_test IS NULL) THEN
            RAISE EXCEPTION 'Either track competition % does not exist or there were no participants in said competition thus far this year', $1;
        END IF;
        Select a into a_gold, time into a_gold_time from R_track where time in (select min(time) from R_track where c=$1 and time <> '000:00:00');
        r.medal_name = 'Gold';
        r.a = a_gold;
        r.time = a_gold_time;
        return next r;
        Select a into a_silver, time into a_silver_time from R_track where time in (select min(time) from R_track where c=$1 and time <> '000:00:00' and a <> a_gold and time <> a_gold_time);
        r.medal_name = 'Silver';
        r.a = a_silver;
        r.time = a_silver_time;
        return next r;
        Select a into a_bronze, time into a_bronze_time from R_track where time in (select min(time) from R_track where c=$1 and time <> '000:00:00' and a <> a_gold and a <> a_silver and time <> a_gold_time and time <> a_silver_time);
        r.medal_name = 'Bronze';
        r.a = a_bronze;
        r.time = a_bronze_time;
        RETURN;
    END
$$
LANGUAGE plpgsql;