Newer
Older
ALTER TABLE public.study_groups ADD COLUMN specid integer DEFAULT null;
ALTER TABLE public.study_groups
DROP CONSTRAINT study_groups_facultyid_gradeid_groupnum_formid_key;
ALTER TABLE ONLY public.study_groups
ADD CONSTRAINT study_groups_facultyid_gradeid_groupnum_formid_spec_key UNIQUE (facultyid, gradeid, groupnum, formid, specid);
ALTER TABLE ONLY public.study_groups
ADD CONSTRAINT study_groups_ibfk_5 FOREIGN KEY (specid) REFERENCES specializations(id);
-- script to fill study_groups.specid from latest record in groups_years
-- after creating empty specid column
update study_groups set specid=(
select groups_years.specializationid from groups_years
where groups_years.groupid=study_groups.id
order by groups_years.year desc
limit 1
) where specid is null;
DROP FUNCTION IF EXISTS public.creategroup(pgradeid integer, pgroupnum integer, pspecname character varying, pfacultyid integer, pyear integer, pformid integer);
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
CREATE OR REPLACE FUNCTION public.creategroup(pgradeid integer, pgroupnum integer, pspecname character varying, pfacultyid integer, pyear integer, pformid integer)
RETURNS integer
LANGUAGE plpgsql
AS $function$
DECLARE vGroupID INT DEFAULT null;
vSpecId INT DEFAULT null;
vGroupYear INT DEFAULT null;
vIsSpecMatch BOOL DEFAULT null;
BEGIN
select specializations.id into vSpecId
from specializations
where specializations.facultyid=pFacultyID and specializations."name" like pSpecName;
if (vSpecId is null) then
INSERT INTO specializations (Name, Abbr, FacultyID)
VALUES (pSpecName, NULL, pFacultyID)
returning id into vSpecId;
end if;
-- create group vGroupID - UNIQUE (pGradeID, pGroupNum, pFacultyID)
select study_groups.id into vGroupID
from study_groups
where study_groups.gradeid=pgradeID
and study_groups.groupnum= pGroupNum
and study_groups.facultyid=pFacultyID
and study_groups.formid=pFormID
and study_groups.specid=vSpecId;
if ( vGroupID is null) then
INSERT INTO study_groups (GradeID, GroupNum, FacultyID, FormID, SpecID)
VALUES (pGradeID, pGroupNum, pFacultyID, pFormID, vSpecId)
returning id into vGroupID ;
end if;
SELECT groups_years.groupid, groups_years.specializationid = vSpecId
INTO vGroupYear, vIsSpecMatch
FROM groups_years
WHERE groups_years.groupid = vGroupID AND groups_years."year" = pYear
LIMIT 1;
IF (vGroupYear is null) THEN
INSERT INTO groups_years (GroupID, Year, SpecializationID)
VALUES (vGroupID, pYear, vSpecId);
ELSEIF NOT vIsSpecMatch THEN
RETURN -1;
END IF;
RETURN vGroupID;
EXCEPTION
when others then
RETURN -1;
END
$function$;