DB Wiki

CREATE DATABASE GrandNational;

-- Lists the entrants in the sweepstake
DROP TABLE participants;
CREATE TABLE IF NOT EXISTS participants (ID serial, 
                                         Name VARCHAR(20) PRIMARY KEY NOT NULL CHECK (LENGTH(Name) > 0));
INSERT INTO participants (name) VALUES ('Ian');
INSERT INTO participants (name) VALUES ('Pat');
INSERT INTO participants (name) VALUES ('Graham');
INSERT INTO participants (name) VALUES ('Tracy');
INSERT INTO participants (name) VALUES ('Michaela');
INSERT INTO participants (name) VALUES ('Glenn');
 
-- List of horses entered into the Grand National
DROP TABLE horses;
CREATE TABLE IF NOT EXISTS horses (HorseNo SMALLINT PRIMARY KEY NOT NULL CHECK (HorseNo BETWEEN 1 AND 40), 
                                   Name VARCHAR(40) UNIQUE);
INSERT INTO horses VALUES (1,'Bristol De Mai');
INSERT INTO horses VALUES (2,'Chris Dream');
INSERT INTO horses VALUES (3,'Yala Enki');
INSERT INTO horses VALUES (4,'Ballyoptic');
INSERT INTO horses VALUES (5,'Definitly Red');
INSERT INTO horses VALUES (6,'Lake View Lad');
INSERT INTO horses VALUES (7,'Burrows Saint');
INSERT INTO horses VALUES (8,'Magic Of Light');
INSERT INTO horses VALUES (9,'Acapella Bourgeois');
INSERT INTO horses VALUES (10,'Talkischeap');
INSERT INTO horses VALUES (11,'Tout Est Permis');
INSERT INTO horses VALUES (12,'Anibale Fly');
INSERT INTO horses VALUES (13,'Mister Malarky');
INSERT INTO horses VALUES (14,'Kimberlite Candy');
INSERT INTO horses VALUES (15,'Any Second Now');
INSERT INTO horses VALUES (16,'Balko Des Flos');
INSERT INTO horses VALUES (17,'Alpha Des Obeaux');
INSERT INTO horses VALUES (18,'OK Corral');
INSERT INTO horses VALUES (19,'Takingrisks');
INSERT INTO horses VALUES (20,'Shattered Love');
INSERT INTO horses VALUES (21,'Jett');
INSERT INTO horses VALUES (22,'Lord Du Mesnil');
INSERT INTO horses VALUES (23,'Potters Corner');
INSERT INTO horses VALUES (24,'Class Conti');
INSERT INTO horses VALUES (25,'Milan Native');
INSERT INTO horses VALUES (26,'Discorama');
INSERT INTO horses VALUES (27,'Vieux Lion Rouge');
INSERT INTO horses VALUES (28,'Cloth Cap');
INSERT INTO horses VALUES (29,'Caberet Queen');
INSERT INTO horses VALUES (30,'Minellacelebration');
INSERT INTO horses VALUES (31,'Canelo');
INSERT INTO horses VALUES (32,'The Long Mile');
INSERT INTO horses VALUES (33,'Give Me A Copper');
INSERT INTO horses VALUES (34,'Farclas');
INSERT INTO horses VALUES (35,'Minella Times');
INSERT INTO horses VALUES (36,'Sub Lieutenant');
INSERT INTO horses VALUES (37,'Hogans Height');
INSERT INTO horses VALUES (38,'Double Shuffle');
INSERT INTO horses VALUES (39,'Ami Desbois');
INSERT INTO horses VALUES (40,'Blaklion');
 
-- Lists the entrants and the horses that they got
DROP TABLE sweepstake;
CREATE TABLE IF NOT EXISTS sweepstake (PlayerID SMALLINT, 
                                       HorseNo SMALLINT PRIMARY KEY NOT NULL CHECK (HorseNo BETWEEN 1 AND 40), 
			               HorseName VARCHAR(40) UNIQUE);

-- Initialise the SWEEPSTAKE table
-- PostgreSQL Version 11 and Above
CREATE OR REPLACE PROCEDURE init()
LANGUAGE SQL 
AS $$
 DELETE FROM sweepstake;
 INSERT INTO sweepstake (HorseNo, HorseName) (SELECT horseno, name FROM horses);
$$;
 
-- PostgreSQL Version 10 and Below
CREATE OR REPLACE FUNCTION init()
RETURNS void AS $$
BEGIN
 DELETE FROM sweepstake;
 INSERT INTO sweepstake (HorseNo, HorseName) (SELECT horseno, name FROM horses);
END;
$$ LANGUAGE plpgsql;
 
-- Performs one round of the draw.
CREATE OR REPLACE FUNCTION OneCycle()
RETURNS void AS $$
DECLARE
  C1 CURSOR FOR SELECT PlayerID, PlayerName, horseno, HorseName FROM (
	          SELECT ROW_NUMBER() over (ORDER BY random() ASC) row_num,
	                 HorseNo, HorseName FROM sweepstake WHERE playerid IS NULL) AS T1,
	         (SELECT id PlayerID, name PlayerName FROM participants) AS T2
	        WHERE row_num = PlayerID;
BEGIN
  FOR a IN C1
  LOOP
    UPDATE sweepstake SET PlayerID = a.PlayerID WHERE HorseNo = a.HorseNo;
  END LOOP;
END;
$$ LANGUAGE plpgsql;
 
-- Generate the sweepstake data
CREATE OR REPLACE FUNCTION GenSweepstake()
RETURNS void AS $$
DECLARE
  C1 CURSOR FOR SELECT name, horseno, horsename
                FROM participants, sweepstake
                WHERE id = playerid ORDER BY horseno;
  NoParticipants REAL;
  NoHorses       REAL;
  NoCycles       SMALLINT;
BEGIN
  SELECT COUNT(*) INTO NoParticipants FROM participants;
  SELECT COUNT(*) INTO NoHorses FROM horses;
  NoCycles := CEIL(NoHorses/NoParticipants);
    perform init();
FOR a IN 1..NoCycles
LOOP
  perform OneCycle();
END LOOP;
END;
$$ LANGUAGE plpgsql;

CREATE OR REPLACE VIEW v_sweepstake AS
       SELECT name, horseno, horsename
       FROM participants, sweepstake
       WHERE id = playerid 
       ORDER BY horseno;
 
CREATE OR REPLACE VIEW v_check AS
       SELECT name, COUNT(name) No_Horses FROM v_sweepstake
       GROUP BY name
       ORDER BY 2 DESC;

SELECT GenSweepstake;