temp_tables.sql

CREATE OR REPLACE FUNCTION public.getgroupsfordisciplineall(pdisciplineid integer)
 RETURNS TABLE("ID" integer, "GroupNum" integer, "GradeID" integer, "GradeNum" integer, "Degree" bachelor_master_specialist, "SpecID" integer, "SpecName" character varying, "SpecAbbr" character varying)
 LANGUAGE plpgsql
AS $function$

    DECLARE vSemesterID INT DEFAULT -1; vYear INT DEFAULT -1;
BEGIN
    vSemesterID := GetDisciplineProperty(pDisciplineID, 'semester');
     SELECT "year"  into vYear from semesters where semesters.ID = vSemesterID;

    -- general + attached
/*    drop table if exists tGroup; -- этого не было
   CREATE TEMPORARY TABLE IF NOT EXISTS tGroup AS (
        SELECT students_groups.GroupID
        FROM view_disciplines_recordbooks
        INNER JOIN students_groups ON students_groups.RecordBookID = view_disciplines_recordbooks.RecordBookID AND
                                      students_groups.SemesterID = vSemesterID AND
                                      students_groups.State <= 'outlet'
        WHERE view_disciplines_recordbooks.DisciplineID = pDisciplineID AND
              'attach' = COALESCE(view_disciplines_recordbooks.Type, 'attach')
        GROUP BY students_groups.GroupID
    );
*/
    return query
    SELECT  view_groups.GroupID AS "ID",
            view_groups.GroupNum as "GroupNum",
            view_groups.GradeID	as "GradeID",
            view_groups.GradeNum as "GradeNum",
            view_groups."degree" as "Degree",
            view_groups.SpecID as "SpecID",
            view_groups.SpecName as "SpecName",
            view_groups.SpecAbbr as "SpecAbbr"
        FROM (SELECT students_groups.GroupID
        		FROM view_disciplines_recordbooks
        		INNER JOIN students_groups ON students_groups.RecordBookID = view_disciplines_recordbooks.RecordBookID AND
                                      students_groups.SemesterID = vSemesterID AND
                                      students_groups.State <= 'outlet'
        		WHERE view_disciplines_recordbooks.DisciplineID = pDisciplineID AND
              				'attach' = COALESCE(view_disciplines_recordbooks.Type, 'attach')
        		GROUP BY students_groups.GroupID) tGroup
        INNER JOIN view_groups ON tGroup.GroupID = view_groups.GroupID AND view_groups.Year = vYear
        ORDER BY view_groups.GradeID ASC, view_groups.GroupID ASC;
END;
$function$;

CREATE OR REPLACE FUNCTION public.getratesforgroup(pdisciplineid integer, pgroupid integer)
 RETURNS TABLE("ID" integer, "LastName" character varying, "FirstName" character varying, "SecondName" character varying, intermediate integer, bonus integer, exam integer)
 LANGUAGE plpgsql
AS $function$

  -- DECLARE vInGeneralGroup BOOLEAN DEFAULT FALSE;
    declare vSemesterID INT DEFAULT -1;
BEGIN
    vSemesterID = GetDisciplineProperty(pDisciplineID, 'semester');
    
    /*
         vInGeneralGroup = EXISTS(
        SELECT * FROM disciplines_groups
        WHERE disciplines_groups.DisciplineID = pDisciplineID AND
              disciplines_groups.GroupID = pGroupID
        LIMIT 1
    );


    DROP TABLE IF EXISTS tStudents;
        CREATE TEMPORARY TABLE tStudents AS (
        SELECT students_groups.RecordBookID
        FROM students_groups
        LEFT JOIN disciplines_students ON disciplines_students.DisciplineID = pDisciplineID AND
                                          disciplines_students.RecordBookID = students_groups.RecordBookID