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

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

                        IF NOT FOUND THEN

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

                        END IF;

                        UPDATE teams SET name = upper($2), city_id = id WHERE team_id = $1;

                        IF NOT FOUND THEN

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

                        END IF;

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

                        RETURN ''Done!'';

            END;

' LANGUAGE 'plpgsql';

--Удаление команды

--Параметры: id команды

CREATE OR REPLACE FUNCTION remove_team(INT)

RETURNS CHAR

AS '

            BEGIN

                        DELETE FROM teams WHERE team_id = $1;

                        IF NOT FOUND THEN

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

                        END IF;

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

                        RETURN ''Done!'';

            END;

' LANGUAGE 'plpgsql';

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

--Параметры: имя, фамилия, дата рождения, название города, рост, вес, опыт

CREATE OR REPLACE FUNCTION add_player(VARCHAR(20),VARCHAR(20),DATE,VARCHAR(20),INT,INT,exp)

RETURNS CHAR

AS '

            DECLARE id INT;

            BEGIN

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

                        IF NOT FOUND THEN

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

                        END IF;

                        INSERT INTO players(

                                    name, surname,

                                    bday, city_id,

                                    height, weight,

                                    experience)

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

                        RETURNING player_id INTO id;

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

                        RETURN ''player_id = '' || id;

            END;

' LANGUAGE 'plpgsql';

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

--Параметры: id игрока,  имя, фамилия, дата рождения, город, рост, вес, опыт

CREATE OR REPLACE FUNCTION edit_player(INT,VARCHAR(20),VARCHAR(20),DATE,VARCHAR(20),INT,INT,exp)

RETURNS CHAR

AS '

            DECLARE id INT;

            BEGIN

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

                        IF NOT FOUND THEN

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

                        END IF;

                        UPDATE players SET

                                    name = $2,

                                    surname = $3,

                                    bday = $4,

                                    city_id = id,

                                    height = $6,

                                    weight = $7,

                                    experience = $8

                        WHERE player_id = $1;

                        IF NOT FOUND THEN

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

                        END IF;

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

                        RETURN ''Done!'';

            END;

' LANGUAGE 'plpgsql';

--Удалениеигрока

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

CREATE OR REPLACE FUNCTION remove_player(INT)

RETURNS CHAR

AS '

            BEGIN

                        DELETE FROM players WHERE player_id = $1;

                        IF NOT FOUND THEN

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