bd_funcs_script.txt

CREATE FUNCTION add_user(
    p_login VARCHAR,
    p_password VARCHAR,
    p_birth_date INT,
    p_height REAL,
    p_goal VARCHAR,
    p_fitness_level INT,
    p_activity_level INT,
    p_available_days INT
) RETURNS INT AS $$
DECLARE
    new_user_id INT;
BEGIN
    INSERT INTO users (
        login, password, birth_date, height, goal, fitness_level, activity_level, available_days, role
    ) VALUES (
        p_login, p_password, p_birth_date, p_height, p_goal, p_fitness_level, p_activity_level, p_available_days, 'user'
    ) RETURNING user_id INTO new_user_id;

    RETURN new_user_id;
END;
$$ LANGUAGE plpgsql;

CREATE FUNCTION add_admin(
    p_login VARCHAR,
    p_password VARCHAR
) RETURNS INT AS $$
DECLARE
    new_admin_id INT;
BEGIN
    INSERT INTO users (
        login, password, role
    ) VALUES (
        p_login, p_password, 'admin'
    ) RETURNING user_id INTO new_admin_id;

    RETURN new_admin_id;
END;
$$ LANGUAGE plpgsql;

CREATE FUNCTION get_user_achievements(p_user_id INT) 
RETURNS TABLE (
    achievement_id INT,
    name VARCHAR,
    description VARCHAR,
    when_created DATE
) AS $$
BEGIN
    RETURN QUERY
    SELECT a.achievement_id, a.name, a.description, a.when_created
    FROM user_achievement ua
    JOIN achievement a ON ua.achievement_id = a.achievement_id
    WHERE ua.user_id = p_user_id;
END;
$$ LANGUAGE plpgsql;

CREATE OR REPLACE FUNCTION get_exercises_for_training_day(
    p_training_day_id INT
) RETURNS TABLE (
    exercise_id INT,
    exercise_name VARCHAR,
    muscle_group VARCHAR,
    description VARCHAR
) AS $$
BEGIN
    RETURN QUERY
    SELECT
        e.exercise_id,
        e.name AS exercise_name,
        e.muscle_group,
        e.description
    FROM
        exercise_training_day etd
    JOIN exercises e ON etd.exercise_id = e.exercise_id
    WHERE
        etd.training_day_id = p_training_day_id;
END;
$$ LANGUAGE plpgsql;

CREATE OR REPLACE FUNCTION get_user_allergies(p_user_id INT)
RETURNS TABLE(allergy_name VARCHAR) AS $$
BEGIN
    RETURN QUERY
    SELECT a.name
    FROM user_allergy ua
    JOIN allergies a ON ua.allergy_id = a.allergy_id
    WHERE ua.user_id = p_user_id;
END;
$$ LANGUAGE plpgsql;