83 lines
2.4 KiB
PL/PgSQL
83 lines
2.4 KiB
PL/PgSQL
|
-- Общая функция возвращения всех госпитализаций и периодов пациента
|
||
|
|
CREATE OR REPLACE FUNCTION get_hospitalization_periods (p_patient_id int)
|
||
|
|
RETURNS TABLE (
|
||
|
|
admission_datetime timestamp,
|
||
|
|
discharge_datetime timestamp
|
||
|
|
)
|
||
|
|
AS $$
|
||
|
|
BEGIN
|
||
|
|
RETURN QUERY
|
||
|
|
SELECT
|
||
|
|
ha.admission_datetime,
|
||
|
|
hd.discharge_datetime
|
||
|
|
FROM
|
||
|
|
hospital_admission ha
|
||
|
|
LEFT JOIN hospital_discharge hd ON ha.hospital_admission_id = hd.hospital_admission_id
|
||
|
|
WHERE
|
||
|
|
ha.patient_id = p_patient_id;
|
||
|
|
END;
|
||
|
|
$$
|
||
|
|
LANGUAGE plpgsql;
|
||
|
|
|
||
|
|
-- Триггер: запрещает новую госпитализацию при наличии незавершённой
|
||
|
|
CREATE OR REPLACE FUNCTION check_active_hospitalization ()
|
||
|
|
RETURNS TRIGGER
|
||
|
|
AS $$
|
||
|
|
DECLARE
|
||
|
|
open_hospitalization boolean;
|
||
|
|
BEGIN
|
||
|
|
SELECT
|
||
|
|
EXISTS (
|
||
|
|
SELECT
|
||
|
|
1
|
||
|
|
FROM
|
||
|
|
get_hospitalization_periods (NEW.patient_id)
|
||
|
|
WHERE
|
||
|
|
discharge_datetime IS NULL) INTO open_hospitalization;
|
||
|
|
IF open_hospitalization THEN
|
||
|
|
RAISE EXCEPTION
|
||
|
|
USING ERRCODE = 'P0001', MESSAGE = '10001: ACTIVE_HOSPITALIZATION_EXISTS';
|
||
|
|
END IF;
|
||
|
|
RETURN NEW;
|
||
|
|
END;
|
||
|
|
$$
|
||
|
|
LANGUAGE plpgsql;
|
||
|
|
|
||
|
|
-- Триггер: запрещает перекрытие госпитализаций
|
||
|
|
CREATE OR REPLACE FUNCTION check_hospitalization_overlap ()
|
||
|
|
RETURNS TRIGGER
|
||
|
|
AS $$
|
||
|
|
DECLARE
|
||
|
|
overlap_found boolean;
|
||
|
|
BEGIN
|
||
|
|
SELECT
|
||
|
|
EXISTS (
|
||
|
|
SELECT
|
||
|
|
1
|
||
|
|
FROM
|
||
|
|
get_hospitalization_periods (NEW.patient_id)
|
||
|
|
WHERE
|
||
|
|
discharge_datetime IS NOT NULL
|
||
|
|
AND NEW.admission_datetime < discharge_datetime
|
||
|
|
AND NEW.admission_datetime > admission_datetime) INTO overlap_found;
|
||
|
|
IF overlap_found THEN
|
||
|
|
RAISE EXCEPTION
|
||
|
|
USING ERRCODE = 'P0001', MESSAGE = '10002: HOSPITALIZATION_OVERLAP';
|
||
|
|
END IF;
|
||
|
|
RETURN NEW;
|
||
|
|
END;
|
||
|
|
$$
|
||
|
|
LANGUAGE plpgsql;
|
||
|
|
|
||
|
|
-- Подключение триггеров на hospital_admissions
|
||
|
|
CREATE TRIGGER trg_check_active_hospitalization
|
||
|
|
BEFORE INSERT ON hospital_admission
|
||
|
|
FOR EACH ROW
|
||
|
|
EXECUTE FUNCTION check_active_hospitalization ();
|
||
|
|
|
||
|
|
CREATE TRIGGER trg_check_hospitalization_overlap
|
||
|
|
BEFORE INSERT ON hospital_admission
|
||
|
|
FOR EACH ROW
|
||
|
|
EXECUTE FUNCTION check_hospitalization_overlap ();
|
||
|
|
|