18 lines
910 B
PL/PgSQL
18 lines
910 B
PL/PgSQL
-- Update the auth_user_subjects RLS Helper Function to include subjects of lessons taught by the teacher
|
|
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 "Student" WHERE id = requesting_user_id() AND requesting_user_role() = 'student'
|
|
)
|
|
UNION
|
|
SELECT "subjectId" FROM "Lesson" WHERE "classId" IN (
|
|
SELECT "classId" FROM "Student" WHERE "parentId" = requesting_user_id() AND requesting_user_role() = 'parent'
|
|
);
|
|
END;
|
|
$$;
|