instead of insert

CREATE TABLE stables
(
    id      BIGSERIAL,
    name    TEXT NOT NULL,
    motor   TEXT NOT NULL,
    country TEXT NOT NULL,
    rubber  TEXT NOT NULL,

    PRIMARY KEY (id)
);

CREATE TABLE stages
(
    id         BIGSERIAL,
    attendance INT  NOT NULL,
    country    TEXT NOT NULL,
    name       TEXT NOT NULL,
    lap_length INT  NOT NULL,
    DATE       DATE NOT NULL,

    PRIMARY KEY (id),
    CHECK ( lap_length >= 1)
);

CREATE TABLE results
(
    id             BIGSERIAL,
    stable_id      BIGINT,
    stage_id       BIGINT,
    laps_count     INT      NOT NULL,
    pilot_name     TEXT     NOT NULL,
    race_time      INTERVAL NOT NULL,
    pit_stop_count INT      NOT NULL,
    place          INT      NOT NULL,

    PRIMARY KEY (id),
    FOREIGN KEY (stable_id) REFERENCES stables (id),
    FOREIGN KEY (stage_id) REFERENCES stages (id),
    CHECK ( pit_stop_count >= 0 ),
    CHECK ( place >= 1 ),
    CHECK ( laps_count >= 1 )
);

INSERT INTO stables(id, name, motor, country, rubber)
VALUES (1, 'Ferrari', 'Ferrari', 'Italy', 'Pirelli'),
       (2, 'Mercedes-Benz', 'Mercedes-AMG F1 M11 EQ', 'Germany', 'Michelin'),
       (3, 'ASTON MARTIN RED BULL RACING', 'Honda', 'Great Britain', 'Michelin'),
       (4, 'MCLAREN F1 TEAM', 'Renault', 'England', 'Michelin'),
       (5, 'RENAULT DP WORLD F1 TEAM', 'Renault', 'French', 'Michelin'),
       (6, 'SCUDERIA ALPHATAURI HONDA', 'Honda', 'Japan', 'Michelin'),
       (7, 'BWT RACING POINT F1 TEAM', 'Mercedes', 'Germany', 'Michelin'),
       (8, 'ALFA ROMEO RACING', 'Ferrari', 'Italy', 'Pirelli'),
       (9, 'WILLIAMS RACING', 'Mercedes', 'Germany', 'Michelin'),
       (10, 'HAAS F1 TEAM', 'Ferrari', 'Italy', 'Michelin');

ALTER SEQUENCE stables_id_seq restart WITH 11;

INSERT INTO stables(name)
VALUES ('Ferrari');


INSERT INTO stages(id, attendance, country, name, lap_length, DATE)
VALUES (1, 15000, 'Germany', 'Germany-grand-prix', 5689, '2003-09-28'),
       (2, 16000, 'Italy', 'Germany-grand-prix', 6451, '2003-10-28'),
       (3, 17000, 'Spain', 'Spain-grand-prix', 3957, '2003-11-28'),
       (4, 18000, 'Russia', 'Russia-grand-prix', 2805, '2003-12-28'),
       (5, 19000, 'Monaco', 'Monaco-grand-prix', 2581, '2004-01-28'),
       (6, 20000, 'Great Britain', 'Great Britain-grand-prix', 8321, '2004-02-28'),
       (7, 21000, 'France', 'France-grand-prix', 2500, '2004-03-28'),
       (8, 22000, 'Poland', 'Poland-grand-prix', 2483, '2004-04-28'),
       (9, 23000, 'Portugal', 'Portugal-grand-prix', 8391, '2004-05-28'),
       (10, 24000, 'Germany', 'Germany-grand-prix', 5689, '2004-09-28');

ALTER SEQUENCE stages_id_seq restart WITH 11;


INSERT INTO results(id, stable_id, stage_id, laps_count, pilot_name, race_time, pit_stop_count, place)
VALUES (1, 1, 1, 50, 'Lewis', '3:12:5', 2, 1),
       (2, 2, 1, 50, 'Ferrari', '3:18:5', 3, 2),
       (3, 3, 1, 50, 'Rock', '3:23:5', 3, 3),
       (4, 2, 2, 70, 'Ferrari', '4:18:5', 2, 1),
       (5, 5, 2, 70, 'Mock', '5:18:5', 3, 2),
       (6, 6, 2, 70, 'Loki', '6:18:5', 3, 3),
       (7, 7, 3, 30, 'Qwerty', '2:08:5', 3, 1),
       (8, 8, 3, 30, 'Bull', '2:18:5', 3, 2),
       (9, 9, 3, 30, 'Rico', '3:18:5', 3, 3),
       (10, 10, 4, 30, 'Bea', '4:18:5', 3, 5),
       (11, 1, 5, 30, 'Nita', '5:18:5', 3, 7),
       (12, 2, 6, 30, 'Colt', '4:12:5', 3, 3),
       (13, 3, 7, 30, 'Fang', '5:12:5', 3, 6),
       (14, 4, 8, 80, 'Sprout', '6:12:5', 3, 1),
       (15, 5, 9, 80, 'Spike', '7:12:5', 3, 2);

ALTER SEQUENCE results_id_seq restart WITH 16;


-- SELECT Section

-- 1 Вывести всех чемпионов разных гран-при
SELECT r.pilot_name, r.race_time
FROM results AS r
WHERE r.place = 1;


-- 2 Вывести все конюшни, по определённым местам
SELECT s.name, s.country, r.place
FROM stables AS s
         JOIN results r ON s.id = r.stable_id
WHERE r.place <= ?
ORDER BY r.place;

-- 3 Вывести среднюю скорость гонщиков
SELECT r.pilot_name,
       ((s.lap_length * r.laps_count) / 1000::FLOAT / (SELECT EXTRACT(epoch FROM r.race_time) / 3600)) AS speed
FROM results AS r
         JOIN stages s ON r.stage_id = s.id
ORDER BY speed DESC;


-- DELETE/UPDATE Section
-- 1 Удалить конюшни


DELETE
FROM results USING stables
WHERE stables.id = results.stable_id
  AND stables.name = ?;

DELETE
FROM stables
WHERE stables.name = ?;

SELECT *
FROM results;

SELECT *
FROM stables;

-- 2 Добавить конюшни

UPDATE stables
SET motor = ?
WHERE name = ?;

-- 3 Обновить результаты гонок
UPDATE results
SET place = ?
WHERE pilot_name = ?;


-- Использование оконных функций
-- Подсчитать средннее место конющни
SELECT s.name, place, AVG(place) OVER (PARTITION BY stable_id) AS FIRST
FROM results
         JOIN stables s ON results.stable_id = s.id


SELECT *
FROM results
WHERE stage_id = 3;

-- Запрос который использует таблицу и алиас к этой таблице
-- Найти все пары автомобилей, которые заняли первые и вторые места
SELECT DISTINCT r1.place,
                r1.stable_id,
                r1.stage_id,
                r1.race_time,
                r1.pilot_name,
                r2.place,
                r2.stable_id,
                r2.stage_id,
                r2.race_time,
                r2.pilot_name
FROM results AS r1,
     results AS r2
WHERE (r1.place = 2
    AND r2.place = 1)
    AND r1.stage_id = r2.stage_id
    AND r1.id != r2.id;
--     and r1.stage_id > 2 and r2.stage_id > 2;

SET role admin;


-- 1.4
-- Грязного чтения нет
BEGIN ISOLATION LEVEL READ COMMITTED;

SELECT COUNT(id)
FROM results;

SELECT COUNT(id)
FROM results;
COMMIT;

-- Неповторяющее чтение есть
BEGIN ISOLATION LEVEL READ COMMITTED;

SELECT pilot_name
FROM results
WHERE id = 1;

SELECT pilot_name
FROM results
WHERE id = 1;

COMMIT;


-- Фантомы есть
BEGIN ISOLATION LEVEL READ COMMITTED;

SELECT COUNT(id)
FROM results;

SELECT COUNT(id)
FROM results;

COMMIT;

-- Неповторяемого чтения нет
BEGIN ISOLATION LEVEL REPEATABLE READ;

SELECT pilot_name
FROM results
WHERE id = 1;

SELECT pilot_name
FROM results
WHERE id = 1;

COMMIT;

-- Фантомов нет
BEGIN ISOLATION LEVEL REPEATABLE READ;

SELECT COUNT(id)
FROM results;

SELECT COUNT(id)
FROM results;

COMMIT;

BEGIN ISOLATION LEVEL REPEATABLE READ;

INSERT INTO results(stable_id, stage_id, laps_count, pilot_name, race_time, pit_stop_count, place)
VALUES (3, 6, (SELECT COUNT(id)
               FROM results
               WHERE pilot_name = 'First'), 'Lewis', '3:12:5', 2, 1);

COMMIT;

-- Фантомов нет
BEGIN ISOLATION LEVEL SERIALIZABLE;

SELECT COUNT(id)
FROM results;

SELECT COUNT(id)
FROM results;

COMMIT;

--Аномалии сериализации нет
BEGIN ISOLATION LEVEL SERIALIZABLE;

INSERT INTO results(stable_id, stage_id, laps_count, pilot_name, race_time, pit_stop_count, place)
VALUES (5, 5, (SELECT COUNT(id)
               FROM results
               WHERE pilot_name = 'Lewis'), 'First', '3:12:5', 2, 1);

COMMIT;
-- DROP TABLE results;
--
-- DROP TABLE stables;
-- DROP TABLE stages;

-- Триггер на pilot_name, stable_id и stage_id гонщика

CREATE OR REPLACE FUNCTION control_repetition()
    RETURNS TRIGGER
    LANGUAGE plpgsql
AS
$$
BEGIN
    IF NOT EXISTS(SELECT *
                  FROM results
                  WHERE stage_id = NEW.stage_id
                    AND stable_id = NEW.stable_id
                    AND pilot_name = NEW.pilot_name) THEN
    ELSE

        BEGIN
            RAISE EXCEPTION 'Отмена добавления результата: гонщик не может проехать за одну и ту же конюшню'
                'дважды один и тот же этап гонки';
        END;
    END IF;
    RETURN NEW;
END;
$$;


DROP FUNCTION control_repetition();
DROP TRIGGER res_ins_trigger ON results;

CREATE TRIGGER res_ins_trigger
    BEFORE INSERT
    ON results
    FOR EACH ROW
    WHEN (pg_trigger_depth() < 1)
EXECUTE PROCEDURE control_repetition();

SELECT *
FROM results;

INSERT INTO results(stable_id, stage_id, laps_count, pilot_name, race_time, pit_stop_count, place)
VALUES (2, 3, 50, 'Andrey', '3:12:5', 2, 1);


grant SELECT, UPDATE, INSERT ON results TO another_user;

grant SELECT (name), UPDATE (motor) ON stables TO another_user;

grant INSERT (name), INSERT(id) ON stables TO another_user;

grant SELECT ON stages TO another_user;

CREATE VIEW stables_name_view AS
SELECT name
FROM stables;

SET role another_user;
SET role admin;
INSERT INTO stables_name_view
VALUES ('ofc');



SELECT *
FROM stables;

ALTER TABLE stables
    ALTER COLUMN name SET DEFAULT '';

ALTER TABLE stables
    ALTER COLUMN motor SET DEFAULT '';

ALTER TABLE stables
    ALTER COLUMN country SET DEFAULT '';

ALTER TABLE stables
    ALTER COLUMN rubber SET DEFAULT '';


SET role admin;

CREATE TRIGGER stables_name_view_insert
    INSTEAD OF INSERT
    ON stables_name_view
    FOR EACH ROW
EXECUTE FUNCTION view_insert_row();

CREATE OR REPLACE FUNCTION view_insert_row()
    RETURNS TRIGGER
    LANGUAGE plpgsql
AS
$$
BEGIN
   INSERT INTO stables(name)
    VALUES (NEW.name);
    RETURN NEW;
END;
$$;

-- Собственная реализация вставки instead of insert на view, пустыми text заполнить not null поля
-- Убрать ненужные пары alias

grant SELECT ON stables_name_view TO another_user;

-- Циклы, ветвления, переменные
CREATE OR REPLACE FUNCTION get_all_first_places(name text) RETURNS int AS
$BODY$
DECLARE
    r                      results % ROWTYPE;
    namesakes_first_places int = 0;
BEGIN
    FOR r IN
        SELECT * FROM results WHERE place = 1
        LOOP
            IF r.pilot_name = name
            THEN
                namesakes_first_places = namesakes_first_places + 1;
            END IF;
        END LOOP;
    RETURN namesakes_first_places;
END;


$BODY$
    LANGUAGE plpgsql;

SELECT *
FROM results
WHERE place = 1;

SELECT *
FROM get_all_first_places('Andrey');

-- Курсоры, исключения
CREATE OR REPLACE FUNCTION find_first_or_raise_pilot_with_current_place(input_place int) RETURNS text AS
$BODY$
DECLARE
    r    results % ROWTYPE;
    cursor_place CURSOR FOR SELECT *
                            FROM results
                            WHERE place = input_place;
    name text;
BEGIN
    OPEN cursor_place;
    LOOP
        FETCH cursor_place INTO r;

        IF r.pilot_name IS NULL THEN
            CLOSE cursor_place;
            RAISE EXCEPTION 'Нет пилота с выбранным местом';
        ELSE
            CLOSE cursor_place;
            name = r.pilot_name;
            RETURN name;

        END IF;
    END LOOP;
END ;


$BODY$
    LANGUAGE plpgsql;

SELECT *
FROM find_first_or_raise_pilot_with_current_place(7);

SELECT *
FROM results
WHERE place = 7;