Функции в PostgreSQL. Проектирование структуры БД хоккейной лиги, страница 4

                        END IF;

                        INSERT INTO log VALUES(DEFAULT, ''remove_player'', now());

                        RETURN ''Done!'';

            END;

' LANGUAGE 'plpgsql';

--Добавлениеигрокавкоманду

--Параметры: id игрока, id команды, позиция на поле, игровой номер

CREATE OR REPLACE FUNCTION add_player_to_team(INT,INT,pos,INT)

RETURNS CHAR

AS '

            BEGIN

                        UPDATE players SET

                                    team_id = $2,

                                    position = $3,

                                    num = $4

                        WHERE player_id = $1;

                        IF NOT FOUND THEN

                                    RETURN ''player_id = '' || $1 || '' does not exist!'';

                        END IF;

                        INSERT INTO log VALUES(DEFAULT, ''add_player_to_team'', now());

                        RETURN ''Done!'';

            END;

' LANGUAGE 'plpgsql';

--Изменение заработка игрока

--Параметры: id игрока, зарплата, премия, штраф

CREATE OR REPLACE FUNCTION set_income(INT,INT,INT,INT)

RETURNS CHAR

AS '

            BEGIN

                        UPDATE players SET

salary = $2,

bonus = $3,

penalty = $4

                        WHERE player_id = $1;

                        IF NOT FOUND THEN

                                    RETURN ''player_id = '' || $1 || '' does not exist!'';

                        END IF;

                        INSERT INTO log VALUES(DEFAULT, ''set_income'', now());

                        RETURN ''Done!'';

            END;

' LANGUAGE 'plpgsql';

--Добавлениеигры

--Параметры: дата игры, название города, id команды 1, id команды 2, общие затраты

--на проведение игры, цена билета, число билетов

--Примечание: для каждого игрока из команд  добавляется новая запись в таблицу stats

CREATE OR REPLACE FUNCTION add_game(DATE,VARCHAR(20),INT,INT,INT,INT,INT)

RETURNS CHAR

AS '

            DECLARE

                        id INT;

                        row RECORD;

            BEGIN

                        SELECT city_id INTO id FROM city WHERE name = upper($2);

                        IF NOT FOUND THEN

                                    RETURN ''city = '' || $2 || '' does not exist!'';

                        END IF;

                        INSERT INTO games(

                                    day,

                                    city_id,

                                    team1_id,

                                    team2_id,

                                    resources,

                                    tickets_price,

                                    tickets)

                        VALUES($1, id, $3, $4, $5, $6, $7)

                        RETURNING game_id INTO id;

                        FOR row IN

                                    SELECT player_id FROM players

                                    WHERE team_id = $3 OR team_id = $4

                        LOOP

                                    INSERT INTO stats(game_id, player_id)

                                    VALUES(id, row.player_id);

                        END LOOP;

                        INSERT INTO log VALUES(DEFAULT, ''add_game'', now());

                        RETURN ''game_id = '' || id;

            END;

' LANGUAGE 'plpgsql';

--Удалениеигры

--Параметры: id игры

CREATE OR REPLACE FUNCTION remove_game(INT)

RETURNS CHAR

AS '

            BEGIN

                        DELETE FROM games WHERE game_id = $1;

                        IF NOT FOUND THEN

                                    RETURN ''game_id = '' || $1 || '' does not exist!'';

                        END IF;

                        INSERT INTO log VALUES(DEFAULT, ''remove_game'', now());

                        RETURN ''Done!'';

            END;