CREATE OR REPLACE FUNCTION public.fn_admin_list_professor_assignments(p_course_id integer)
 RETURNS TABLE(id integer, professor_id bigint, level_id integer, name character varying, surname character varying, email text, level_name character varying)
 LANGUAGE sql
 STABLE
AS $function$
    SELECT
        cpl.id,
        cpl.professor_id,
        cpl.level_id,
        p.name,
        p.surname,
        p.email::text,
        l.name AS level_name
    FROM public.course_professor_levels cpl
    JOIN public.partners p ON p.partner_id = cpl.professor_id
    LEFT JOIN public.levels l ON l.level_id = cpl.level_id AND l.course_id = cpl.course_id
    WHERE cpl.course_id = p_course_id
    ORDER BY p.surname, p.name, l.order_index;
$function$
;