77 lines
2.1 KiB
PL/PgSQL
77 lines
2.1 KiB
PL/PgSQL
CREATE OR REPLACE FUNCTION fn_low_enrollment(threshold INTEGER)
|
|
RETURNS TABLE(
|
|
discipline_id INTEGER,
|
|
discipline_name VARCHAR,
|
|
department_name VARCHAR,
|
|
enrolled BIGINT
|
|
) AS $$
|
|
BEGIN
|
|
RETURN QUERY
|
|
SELECT d.id,
|
|
d.name AS discipline_name,
|
|
dept.name AS department_name,
|
|
COUNT(st.id) AS enrolled
|
|
FROM Disciplines d
|
|
LEFT JOIN Departments dept ON d.department_id = dept.id
|
|
LEFT JOIN Statements st ON st.discipline_id = d.id
|
|
GROUP BY d.id, d.name, dept.name
|
|
HAVING COUNT(st.id) < threshold;
|
|
END;
|
|
$$ LANGUAGE plpgsql;
|
|
|
|
CREATE OR REPLACE FUNCTION fn_generate_transcript(p_student INTEGER)
|
|
RETURNS TEXT
|
|
LANGUAGE plpgsql
|
|
AS $$
|
|
DECLARE
|
|
rec RECORD;
|
|
student_info RECORD;
|
|
transcript TEXT;
|
|
BEGIN
|
|
SELECT p.name || ' ' || p.surname AS full_name,
|
|
s.group_id,
|
|
s.status
|
|
INTO student_info
|
|
FROM Students s
|
|
JOIN Physicals p ON s.person = p.passport_no
|
|
WHERE s.id = p_student;
|
|
|
|
transcript := 'Transcript for student ' || p_student || E'\n'
|
|
|| 'Name: ' || student_info.full_name || E'\n'
|
|
|| 'Group: ' || student_info.group_id || E'\n'
|
|
|| 'Status: ' || student_info.status || E'\n' || E'\n';
|
|
|
|
FOR rec IN
|
|
SELECT d.name AS discipline_name,
|
|
st.grade,
|
|
to_char(st.conducted_at,'YYYY-MM-DD') AS date
|
|
FROM Statements st
|
|
JOIN Disciplines d ON st.discipline_id = d.id
|
|
WHERE st.student_id = p_student
|
|
ORDER BY st.conducted_at
|
|
LOOP
|
|
transcript := transcript || rec.date || ' - ' || rec.discipline_name || ': ' || rec.grade || E'\n';
|
|
END LOOP;
|
|
|
|
RETURN transcript;
|
|
END;
|
|
$$;
|
|
|
|
CREATE OR REPLACE PROCEDURE sp_graduate_by_admission_year(p_year INTEGER)
|
|
LANGUAGE plpgsql
|
|
AS $$
|
|
DECLARE
|
|
rec RECORD;
|
|
BEGIN
|
|
FOR rec IN
|
|
SELECT s.id, g.study_starts
|
|
FROM Students s
|
|
JOIN Groups g ON s.group_id = g.group_id
|
|
WHERE EXTRACT(YEAR FROM g.study_starts) = p_year
|
|
AND s.status = 'УЧИТСЯ'
|
|
LOOP
|
|
INSERT INTO Movement(student_id, type, new_status, issued_at)
|
|
VALUES (rec.id, 'ОТЧИСЛЕН', 'ОТЧИСЛЕН', NOW());
|
|
END LOOP;
|
|
END;
|
|
$$; |