Newer
Older
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
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
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
CREATE SEQUENCE seq_subgroups
START WITH 1
INCREMENT BY 1
NO MINVALUE
NO MAXVALUE
CACHE 1;
-- DROP SEQUENCE public.seq_subgroups;
select setval('seq_subgroups', 1, true);
create table subgroups (
id integer default nextval('seq_subgroups'::regclass) NOT NULL,
title character varying(20) NOT NULL,
teacher_id integer NOT NULL,
discipline_id integer NOT NULL
);
ALTER TABLE ONLY subgroups
ADD CONSTRAINT subgroups_pkey PRIMARY KEY (id);
ALTER TABLE ONLY subgroups
ADD CONSTRAINT subgroups_ibfk_1 FOREIGN KEY (discipline_id) REFERENCES disciplines(id);
ALTER TABLE ONLY subgroups
ADD CONSTRAINT subgroups_ibfk_2 FOREIGN KEY (teacher_id) REFERENCES teachers(id);
CREATE SEQUENCE seq_students_subgroups
START WITH 1
INCREMENT BY 1
NO MINVALUE
NO MAXVALUE
CACHE 1;
create table students_subgroups (
id integer default nextval('seq_students_subgroups'::regclass) NOT NULL,
recordbook_id integer NOT NULL,
subgroup_id integer NOT NULL
);
ALTER TABLE ONLY students_subgroups
ADD CONSTRAINT students_subgroups_pkey PRIMARY KEY (id);
ALTER TABLE ONLY students_subgroups
ADD CONSTRAINT students_subgroups_ibfk_1 FOREIGN KEY (recordbook_id) REFERENCES record_books(id);
ALTER TABLE ONLY students_subgroups
ADD CONSTRAINT students_subgroups_ibfk_2 FOREIGN KEY (subgroup_id) REFERENCES subgroups(id);
CREATE SEQUENCE seq_subgroups_teachers
START WITH 1
INCREMENT BY 1
NO MINVALUE
NO MAXVALUE
CACHE 1;
create table subgroups_teachers (
id integer default nextval('seq_subgroups_teachers'::regclass) NOT NULL,
subgroup_id integer NOT NULL,
teacher_id integer NOT NULL
);
ALTER TABLE ONLY subgroups_teachers
ADD CONSTRAINT subgroups_teachers_pkey PRIMARY KEY (id);
ALTER TABLE ONLY subgroups_teachers
ADD CONSTRAINT subgroups_teachers_ibfk_1 FOREIGN KEY (subgroup_id) REFERENCES subgroups(id);
ALTER TABLE ONLY subgroups_teachers
ADD CONSTRAINT subgroups_teachers_ibfk_2 FOREIGN KEY (teacher_id) REFERENCES teachers(id);
CREATE OR REPLACE FUNCTION public.CreateSubgroup(ptitle character varying, pteacherid integer, pdisciplineid integer)
RETURNS integer
LANGUAGE plpgsql
AS $function$
declare vID int default -1;
begin
INSERT INTO subgroups (title, teacher_id, discipline_id)
VALUES (ptitle, pteacherid, pdisciplineid) returning id into vID;
RETURN vID;
end
$function$;
CREATE OR REPLACE FUNCTION public.subgroup_delete(psubgroupid integer)
RETURNS integer
LANGUAGE plpgsql
AS $function$
begin
delete from subgroups
where subgroups.id = psubgroupid;
RETURN 0;
end
$function$;
CREATE OR REPLACE FUNCTION public.Subgroup_GetInfo(psubgroupid integer)
RETURNS TABLE("Title" character varying, "TeacherID" integer, "DisciplineID" integer)
LANGUAGE sql
AS $function$
SELECT subgroups.title AS "Title",
subgroups.teacher_id AS "TeacherID",
subgroups.discipline_id AS "DisciplineID"
FROM subgroups
WHERE subgroups.id = psubgroupid;
$function$;
CREATE OR REPLACE FUNCTION public.Discipline_GetSubgroups(pdisciplineid integer)
RETURNS TABLE("ID" integer, "Title" character varying, "TeacherID" integer)
LANGUAGE sql
AS $function$
SELECT subgroups.id AS "ID",
subgroups.title AS "Title",
subgroups.teacher_id AS "TeacherID",
subgroups.discipline_id AS "DisciplineID"
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
161
162
163
164
FROM subgroups
WHERE subgroups.discipline_id = pdisciplineid;
$function$;
CREATE OR REPLACE FUNCTION public.Subgroup_BindStudent(pstudentid integer, psubgroupid integer)
RETURNS integer
LANGUAGE plpgsql
AS $function$
declare vID int default -1;
begin
insert into students_subgroups(recordbook_id, subgroup_id)
values(pstudentid, psubgroupid) returning id into vID;
RETURN vID;
end
$function$;
CREATE OR REPLACE FUNCTION public.Subgroup_UnbindStudent(pstudentid integer, psubgroupid integer)
RETURNS integer
LANGUAGE plpgsql
AS $function$
declare vID int default -1;
begin
delete from students_subgroups
where students_subgroups.recordbook_id = pstudentid and students_subgroups.subgroup_id = psubgroupid;
RETURN 0;
end
$function$;
CREATE OR REPLACE FUNCTION public.Subgroup_GetStudents(psubgroupid integer)
RETURNS TABLE("ID" integer)
LANGUAGE sql
AS $function$
select students_subgroups.recordbook_id from students_subgroups
where students_subgroups.subgroup_id = psubgroupid;
$function$;
CREATE OR REPLACE FUNCTION public.Subgroup_BindTeacher(pteacherid integer, psubgroupid integer)
RETURNS integer
LANGUAGE plpgsql
AS $function$
declare vID int default -1;
begin
insert into subgroups_teachers(teacher_id, subgroup_id)
values(pteacherid, psubgroupid) returning id into vID;
RETURN vID;
end
$function$;