Newer
Older
Anton Bagliy
committed
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
CREATE SEQUENCE seq_nicknames
START WITH 1
INCREMENT BY 1
NO MINVALUE
NO MAXVALUE
CACHE 1;
select setval('seq_nicknames', 1, true);
CREATE TABLE nicknames
(
id integer DEFAULT nextval('seq_nicknames'::regclass) NOT NULL,
name character varying(50) NOT NULL,
accountid integer NOT NULL,
servicename character varying(20) NOT NULL
);
ALTER TABLE ONLY nicknames
ADD CONSTRAINT nicknames_name_service_key UNIQUE (name, servicename);
ALTER TABLE ONLY nicknames
ADD CONSTRAINT nicknames_ibfk_1 FOREIGN KEY (accountid) REFERENCES accounts (id);
DROP FUNCTION IF EXISTS public.getrecordbookforsubjectandnick(pnick character varying, psubject character varying, psemesterid integer, pservice character varying);
CREATE OR REPLACE FUNCTION public.getrecordbookforsubjectandnick(pnick character varying, psubject character varying, psemesterid integer, pservice character varying)
RETURNS TABLE("RecordBookID" integer, "DisciplineID" integer)
LANGUAGE sql
AS $function$
SELECT record_books.ID AS "RecordBookID",
disciplines.ID AS "DisciplineID"
FROM record_books
JOIN record_books_plans on record_books.id = record_books_plans.recordbookid
JOIN disciplines_study_plans on disciplines_study_plans.studyplanid = record_books_plans.studyplanid
JOIN disciplines on disciplines.id = disciplines_study_plans.disciplineid
JOIN subjects on disciplines.subjectid = subjects.id
JOIN students on record_books.studentid = students.id
JOIN accounts on accounts.id = students.accountid
JOIN nicknames on nicknames.accountid = accounts.id
WHERE subjects.name like '%'||psubject||'%'
AND disciplines.semesterid = psemesterid
AND nicknames.name = pnick
AND nicknames.servicename = pservice
LIMIT 1;
$function$;
DROP FUNCTION IF EXISTS public.GetSubmoduleByNumber(pdiscipline integer, pnumber integer);
CREATE OR REPLACE FUNCTION public.GetSubmoduleByNumber(pdiscipline integer, pnumber integer)
RETURNS integer
LANGUAGE plpgsql
AS $function$
DECLARE vSubmoduleID integer DEFAULT NULL;
BEGIN
SELECT submodules.id into vSubmoduleID
FROM submodules
JOIN modules on modules.id = submodules.moduleid
JOIN disciplines on modules.disciplineid = disciplines.id
WHERE disciplines.id = pdiscipline AND modules."type" = 'regular'
ORDER by modules.ordernum asc,
submodules.ordernum asc
OFFSET pnumber
limit 1;
Anton Bagliy
committed
RETURN vSubmoduleID;
EXCEPTION
when others
then RETURN -1;
Anton Bagliy
committed
end
$function$;