18 lines
981 B
PL/PgSQL
18 lines
981 B
PL/PgSQL
-- Fix auth_user_subjects(): use TeacherSubject (not _SubjectToTeacher) and include teacher's lesson subjects
|
|
CREATE OR REPLACE FUNCTION auth_user_subjects() RETURNS SETOF integer LANGUAGE plpgsql SECURITY DEFINER SET search_path = public STABLE AS $$
|
|
BEGIN
|
|
RETURN QUERY
|
|
SELECT "subjectId" FROM "TeacherSubject" WHERE "teacherId" = requesting_user_id() AND requesting_user_role() = 'teacher'
|
|
UNION
|
|
SELECT "subjectId" FROM "Lesson" WHERE "teacherId" = requesting_user_id() AND requesting_user_role() = 'teacher'
|
|
UNION
|
|
SELECT "subjectId" FROM "Lesson" WHERE "classId" IN (
|
|
SELECT "classId" FROM "StudentClass" WHERE "studentId" = requesting_user_id() AND requesting_user_role() = 'student'
|
|
)
|
|
UNION
|
|
SELECT "subjectId" FROM "Lesson" WHERE "classId" IN (
|
|
SELECT "classId" FROM "StudentClass" WHERE "studentId" IN (SELECT id FROM "Student" WHERE "parentId" = requesting_user_id()) AND requesting_user_role() = 'parent'
|
|
);
|
|
END;
|
|
$$;
|