issue316_discipline_noteachers.sql

drop function if exists public.discipline_get_internals(pdisciplineid integer);
CREATE OR REPLACE FUNCTION public.discipline_get_internals(pdisciplineid integer)
RETURNS TABLE("Name" character varying, "DisciplineID" integer, "PlanExternalID" integer, "SubjectExternalID" character varying)
 LANGUAGE plpgsql
AS $function$
begin
  return query
  select subjects.name as "Name",
       disciplines.id as "DisciplineID",
       study_plans.externalid as "PlanExternalID",
       subjects.externalid as "SubjectExternalID"
  from disciplines
  join subjects on disciplines.subjectid = subjects.id
  left join disciplines_study_plans on disciplines.id = disciplines_study_plans.disciplineid
  left join study_plans on disciplines_study_plans.studyplanid = study_plans.id
  where disciplines.id = pdisciplineid;
END;
$function$;