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

' LANGUAGE 'plpgsql';

--Продажабилетов

--Параметры: id игры, число билетов

CREATE OR REPLACE FUNCTION sell_tickets(INT, INT)

RETURNS CHAR

AS '

            DECLARE amount INT;

            BEGIN

                        SELECT sold_tickets INTO amount FROM games WHERE game_id = $1;

                        IF NOT FOUND THEN

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

                        END IF;

                        UPDATE games SET sold_tickets = amount + $2 WHERE game_id = $1;

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

                        RETURN ''Done!'';

            END;

' LANGUAGE 'plpgsql';

--Изменение результатов игры заданного игрока

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

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

RETURNS CHAR

AS '

            BEGIN

                        UPDATE stats SET goals = $3, assists = $4, game_time = $5, penalty_time = $6

                        WHERE game_id = $1 AND player_id = $2;

                        IF NOT FOUND THEN

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

                        END IF;

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

                        RETURN ''Done!'';

            END;

' LANGUAGE 'plpgsql';

--Случайное задание  статистики игр, зарплат игроков и числа проданных билетов

CREATE OR REPLACE FUNCTION fill()

RETURNS CHAR

AS '

            DECLARE

                        player_count INT;

                        game_count INT;

            BEGIN

                        SELECT COUNT(player_id) INTO player_count FROM players;

                        SELECT COUNT(game_id) INTO game_count FROM games;

                        FOR i IN 1..player_count LOOP

                                    UPDATE players SET

                                               salary = round(random()*10^5)::INT,

                                               bonus = round(random()*10^2)::INT,

                                               penalty = round(random()*10^2)::INT

                                    WHERE player_id = i;

                        END LOOP;

                        FOR i IN 1..game_count

                        LOOP

UPDATE games SET sold_tickets = round(tickets * random())::INT WHERE game_id = i;

                                    FOR j IN 1..player_count

                                    LOOP

                                               UPDATE stats SET

                                                           goals = round(random()*10)::INT,

                                                           assists = round(random()*10)::INT,

                                                           game_time = round(random()*10^2)::INT,

                                                           penalty_time = round(random()*10)::INT

                                               WHERE game_id = i AND player_id = j;

                                    END LOOP;

                        END LOOP;

                        RETURN ''Done!'';

            END;

' LANGUAGE 'plpgsql';

--Заполнениетаблиц

SELECT add_city('novosibirsk');

SELECT add_city('irkutsk');

SELECT add_city('omsk');

SELECT add_city('tomsk');

SELECT add_city('krasnoyarsk');

SELECT add_city('sochi');

SELECT add_city('grozny');

SELECT add_city('kemerovo');

SELECT add_city('ekaterinburg');

SELECT add_city('magnitogorsk');

SELECT add_player('dimon', 'barsukov', '19910212', 'novosibirsk', 184, 70, 'beginner');

SELECT add_player('vasya', 'pupkin', '19950510', 'irkutsk', 173, 90, 'beginner');