3AszeGHV

1-- RESET CURRENT CONTENT
2
3TRUNCATE TABLE lab2.groups CASCADE;
4ALTER SEQUENCE lab2.groups_id_seq RESTART WITH 1;
5TRUNCATE TABLE lab2.students CASCADE;
6
7-- RANDOM FILL
8
9do $$
10    declare
11        counter integer := 1;
12        direction_id integer;
13        direction_index integer;
14        c_group_number integer;
15        direction_name text;
16        faculty integer;
17        g_group_id integer;
18        is_man boolean;
19        g_gender text;
20        has_middle_name boolean;
21        g_first_name text;
22        g_middle_name text;
23        g_last_name text;
24        random_days integer;
25        start_date date := '1998-01-01';
26        end_date date := '2005-06-01';
27        g_birth_date date;
28    begin
29        while counter <= 10000
30        loop
31            -- common local constants
32            is_man = random() < 0.5;
33            g_gender = CASE WHEN is_man THEN 'male' ELSE 'female' END;
34            has_middle_name = random() > 0.01;
35
36            -- generate first name
37            g_first_name = (
38                SELECT data
39                FROM lab2.src_first_names
40                WHERE gender = g_gender
41                ORDER BY random()
42                LIMIT 1
43            );
44
45            -- generate middle name
46            if has_middle_name
47            then
48                g_middle_name = (
49                    SELECT data
50                    FROM lab2.src_middle_names
51                    WHERE gender = g_gender
52                    ORDER BY random()
53                    LIMIT 1
54                );
55            else
56                g_middle_name = null;
57            end if;
58
59            -- generate last name
60            g_last_name = (
61                SELECT data
62                FROM lab2.src_last_names
63                WHERE gender = g_gender
64                ORDER BY random()
65                LIMIT 1
66            );
67
68            -- calculate direction ID and group number
69            direction_id = (counter - 1) / 81 + 1;
70            direction_index = counter % 81;
71            c_group_number = direction_index / 27 + 1;
72
73            -- query direction data by ID
74            faculty = (SELECT faculty_id FROM lab2.src_directions WHERE id = direction_id);
75            direction_name = (SELECT data FROM lab2.src_directions WHERE id = direction_id);
76
77            -- create a group entry if not exists
78            g_group_id = (
79                SELECT id
80                FROM lab2.groups
81                WHERE direction = direction_name
82                AND faculty_id = faculty
83                AND group_number = c_group_number
84            );
85
86            if g_group_id is null
87            then
88                INSERT INTO lab2.groups (faculty_id, direction, group_number)
89                VALUES (faculty, direction_name, c_group_number);
90
91                g_group_id = (
92                    SELECT id
93                    FROM lab2.groups
94                    WHERE direction = direction_name
95                    AND faculty_id = faculty
96                    AND group_number = c_group_number
97                );
98            end if;
99
100            -- generate a birth date
101            random_days = floor(random() * (end_date - start_date));
102            g_birth_date = start_date + random_days;
103
104            -- create a student entry
105            INSERT INTO lab2.students (id, group_id, first_name, middle_name, last_name, gender, birth_date)
106            VALUES (counter, g_group_id, g_first_name, g_middle_name, g_last_name, g_gender, g_birth_date);
107
108            counter = counter + 1;
109        end loop;
110    end;
111$$