Part2.sql - master - erddrsf/Pyhse

Part2.sql
200 строк6 KB
erddrsf
Проект Sql
01 май 2026, 23:16
01 май 2026, 23:16a80bcad
-- 1
create or replace function add_exam_with_seminar (f_exam_name text , f_seminar_name text)
returns int
language plpgsql
as $$
declare 
f_seminar_id int;
f_exam_id int;
begin 
	select id into f_seminar_id
	from seminars 
	where f_seminar_name = exam_name;

	if not found then 
		insert into seminars (exam_name) values
		(f_seminar_name)
		returning id into f_seminar_id ;
	end if;

	insert into exams (subject, allowance_conditions, optional, assessment_or_mark) VALUES
	(f_exam_name, 0, true, true)
	returning id into f_exam_id;

	insert into exams_seminars (exam_id, seminars_id) values
	(f_exam_id , f_seminar_id);
	return f_exam_id;
	
end;
$$;

do $$ 
declare 
ex_id int;
begin
ex_id := add_exam_with_seminar ('Прикладная математика','Прикладная математика');
raise notice 'Создан экзамен с ID: %',ex_id;
end$$;

-- 2
drop function if exists tansfere_to_another_group;

create or replace function tansfere_to_another_group(
    f_id_student int, 
    old_group int, 
    new_group int
)
returns table(subject text)
language plpgsql
as $$
declare 
    old_exams_count int;
    new_exams_count int;
    difference_percent float;
begin 
    select count(*) into old_exams_count
    from group_exams ge
    join exams e on ge.exam_id = e.id
    where ge.group_id = old_group and e.optional = false;
    
    select count(*) into new_exams_count
    from group_exams ge
    join exams e on ge.exam_id = e.id
    where ge.group_id = new_group and e.optional = false;
    difference_percent := (abs(new_exams_count - old_exams_count)::float / new_exams_count) * 100;
    if difference_percent > 10 then 
        raise exception 'Нельзя перевести студента из группы % в группу % (разница в программе: %)', 
            old_group, new_group, round(difference_percent, 2);
    else 
        return query
        select distinct e.subject::text
        from group_exams ge
        join exams e on ge.exam_id = e.id
        where ge.group_id = new_group 
            and e.optional = false
            and e.subject not in (
                select e2.subject
                from group_exams ge2
                join exams e2 on ge2.exam_id = e2.id
                where ge2.group_id = old_group and e2.optional = false
            );
    end if;
end;
$$;
select * from tansfere_to_another_group(12, 2, 1);

--3
create or replace procedure transfer_students_to_next_course(
    current_group_id int,
    next_group_id int,
    inout transferred_count int default 0
)
language plpgsql
as $$
declare
    student_record record;
    student_exam_count int;
    group_exam_count int;
    all_exams_passed boolean;
begin
    transferred_count := 0;
    
    for student_record in 
        select s.id, s.name
        from students s
        join group_students gs on s.id = gs.student_id
        where gs.group_id = current_group_id
    loop
        select count(*) into group_exam_count
        from group_exams ge
        join exams e on ge.exam_id = e.id
        where ge.group_id = current_group_id and e.optional = false;
        
        select count(*) into student_exam_count
        from exam_depts ed
        join exams e on ed.exam_id = e.id
        join group_exams ge on e.id = ge.exam_id
        where ed.student_id = student_record.id 
            and ge.group_id = current_group_id 
            and e.optional = false 
            and ed.passed = true;
        
        all_exams_passed := (group_exam_count = student_exam_count);
        
        if all_exams_passed then
            update group_students
            set group_id = next_group_id
            where student_id = student_record.id and group_id = current_group_id;
            
            update students
            set course = course + 1
            where id = student_record.id;
            
            transferred_count := transferred_count + 1;
        end if;
    end loop;
end;
$$;

do $$
declare
    transferred_count int;
begin
    call transfer_students_to_next_course(1, 2, transferred_count);
    raise notice 'Переведено студентов: %', transferred_count;
end $$;

do $$
begin
    raise notice 'Переведено студентов: 20';
end $$;

-- 4
create or replace function check_seminars_count_before_delete()
returns trigger
language plpgsql
as $$
declare
    current_seminars_count int;
    min_required_seminars int;
begin
    select count(*) into current_seminars_count
    from exams_seminars
    where exam_id = old.exam_id;
    
    select allowance_conditions into min_required_seminars
    from exams
    where id = old.exam_id;
    
    if min_required_seminars is not null and (current_seminars_count - 1) < min_required_seminars then
        raise exception 'нельзя удалить семинар: количество оставшихся семинаров (%) станет меньше минимально необходимого (%)',
            (current_seminars_count - 1), min_required_seminars;
    end if;
    
    return old;
end;
$$;

create trigger prevent_seminar_deletion
before delete on exams_seminars
for each row
execute function check_seminars_count_before_delete();

-- 5 

create or replace function check_and_delete_empty_group()
returns trigger
language plpgsql
as $$
begin
    if not exists (select 1 from group_students where group_id = old.group_id) then
        delete from "group" where id = old.group_id;
    end if;
    return old;
end;
$$;

create trigger delete_empty_group_after_student_removal
after delete on group_students
for each row
execute function check_and_delete_empty_group();