Advertisement
Not a member of Pastebin yet?
Sign Up,
it unlocks many cool features!
- \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;
- a_gold integer;
- a_gold_time integer;
- a_silver integer;
- a_silver_time integer;
- a_bronze integer;
- a_bronze_time integer;
- 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;
Advertisement
Add Comment
Please, Sign In to add comment
Advertisement