
-- This script defines a procedure for entering an observation.

CREATE OR REPLACE PROCEDURE insert_observation(
    observer_email       IN VARCHAR(40),
    star_name            IN CHAR(5),
    star_constellation   IN CHAR(3),
    location_name        IN VARCHAR(40),
    observation_datetime IN TIMESTAMP(0) WITH TIME ZONE,
    observed_brightness  IN NUMERIC(6, 3),
    notes                IN VARCHAR(80)) AS $$
DECLARE
    observer_id INTEGER;
    location_id INTEGER;
BEGIN
    -- We need the observer ID. If the email address does not exist in the database or is not 
    -- unique, the following will raise an exeception that gets caught and translated below.
    SELECT observer.id INTO STRICT observer_id
    FROM   observer
    WHERE  observer.email_addr = observer_email;

    -- We also need the location ID. If the location name does not exist in the database or is 
    -- not unique, the following will raise an exception that gets caught and translated below.
    SELECT location.id INTO STRICT location_id
    FROM   location
    WHERE  location.name = location_name;

    -- One might qualify the location by the ID of the owning observer. Thus locations like 
    -- 'My House' would be unique in that context:
    --
    -- SELECT location.id INTO STRICT location_id
    -- FROM   location
    -- WHERE  location.name = location_name AND location.owner_id = observer_id;
    --
    -- The problem with this is that the procedure would only allow an observer to record
    -- observations taken at locations the observer owned. For observers visiting another
    -- location (e.g., at a star party), that's not a viable solution.
    

    -- Now insert the information. If the star specified does not exist in the database, or if 
    -- there is some other constraint error in the provided data, the following will raise an 
    -- exception that gets caught and translated below.
    INSERT INTO observation
      (name, const, observer_id, location_id, datetime, brightness, notes)
    VALUES
      (star_name,             -- From parameter list.
       star_constellation,    -- From parameter list.
       observer_id,           -- Previously looked up.
       location_id,           -- Previously looked up.
       observation_datetime,  -- From parameter list.
       observed_brightness,   -- From parameter list.
       notes);                -- From parameter list.

EXCEPTION
    WHEN OTHERS THEN
        RAISE 'Inserting observation failed!' USING HINT = 'Check the arguments!';
END;
$$ LANGUAGE plpgsql;

