How to check 2 cells from different tables using trigger in PostgreSQL?

Question

PostgreSQL

Good afternoon everyone! I read everything I could on the Internet, but so far I have not succeeded, can you help me? :(
The fact is that I have 4 tables:

• Departments (Department_name)
  
• Subjects (Subject_name, Department_id)
  
• Teachers (full name, Academic_degree, id_Department)
  
• Slave_subjects (id_subject, id_teacher)
  

The bottom line is that before you enter a record in the table Slave_subjects , where each teacher is assigned to the subject of his department. So that a person could not put a physical education teacher on a couple of physics or mats, for example. The trigger must fire before adding a record to the table. Those. the function checks the id of the departments of subjects and teachers, whether they really match. I ask for help, otherwise I can’t figure it out myself (
PS I tried to sketch something, it almost turned out, but it gives an error .

CREATE TABLE Преподаватели
(
  ID_Преподавателя SERIAL PRIMARY KEY,
  ФИО CHARACTER VARYING(50) UNIQUE NOT NULL,
  Учёная_степень CHARACTER VARYING(30) NOT NULL,
  id_Кафедры INT NOT NULL,
  FOREIGN KEY (id_Кафедры) REFERENCES Кафедры (id_Кафедры),
  UNIQUE (ФИО, id_Кафедры),
  UNIQUE (ФИО, Учёная_степень),
  CHECK (Учёная_степень IN ('Преподаватель', 'Доцент', 'Профессор')),
  CHECK (ФИО != '')
);



CREATE TABLE Кафедры
(
  id_Кафедры SERIAL PRIMARY KEY,
  Название_кафедры CHARACTER VARYING(50) UNIQUE NOT NULL
);

CREATE TABLE Предметы
(
  id_Предмета SERIAL PRIMARY KEY,
  Название_предмета CHARACTER VARYING(50) NOT NULL UNIQUE,
  id_Кафедры INT NOT NULL,
  FOREIGN KEY (id_Кафедры) REFERENCES Кафедры (id_Кафедры),
  CHECK (Название_предмета != '' AND id_Кафедры != '1')
);

CREATE TABLE Ведомые_предметы
(
  id_Ведомого_предмета SERIAL PRIMARY KEY,
  id_Преподавателя INT NOT NULL,
  id_Предмета INT NOT NULL,
  FOREIGN KEY (id_Преподавателя) REFERENCES Преподаватели (id_Преподавателя),
  FOREIGN KEY (id_Предмета) REFERENCES Предметы (id_Предмета),
  UNIQUE (id_Преподавателя, id_Предмета)
);

CREATE OR REPLACE FUNCTION LecturerCheck() RETURNS trigger AS '
DECLARE
  Subject CHARACTER VARYING(50);
  Lecturer CHARACTER VARYING(30);
BEGIN
  select id_Кафедры into Subject from Предметы;
  select id_Кафедры into Lecturer from Преподаватели;
  if Subject = Lecturer then INSERT INTO Ведомые_предметы VALUES (NOW());
  else raise exception ''Error'';
  end if;
END;
' LANGUAGE plpgsql

CREATE TRIGGER tr_LecturerCheck 
BEFORE INSERT ON Ведомые_предметы FOR EACH ROW
EXECUTE PROCEDURE lecturercheck()