qzG6uxd0

1-- CREATE DATABASE library WITH ENCODING='UTF8';
2
3-- книга
4drop table if exists book cascade;
5CREATE TABLE book (
6  id SERIAL PRIMARY KEY,
7  author_name VARCHAR(128) NOT NULL, -- имя автора
8  title VARCHAR(128) NOT NULL -- название
9);
10
11-- сотрудник
12drop table if exists employee cascade;
13CREATE TABLE employee (
14  id SERIAL PRIMARY KEY,
15  first_name VARCHAR(128) NOT NULL, -- имя
16  second_name VARCHAR(128) NOT NULL -- фамилия
17);
18
19-- читатель
20drop table if exists reader cascade;
21CREATE TABLE reader (
22  id SERIAL PRIMARY KEY,
23  first_name VARCHAR(128) NOT NULL, -- имя
24  second_name VARCHAR(128) NOT NULL -- фамилия
25);
26
27-- взятая книга
28drop table if exists book_take cascade;
29CREATE TABLE book_take (
30  id SERIAL PRIMARY KEY,
31  book_id INTEGER NULL REFERENCES book(id) ON DELETE SET NULL, -- книга
32  reader_id INTEGER NULL REFERENCES reader(id) ON DELETE SET NULL, -- читатель взявший книгу
33  employee_id INTEGER NULL REFERENCES employee(id) ON DELETE SET NULL, -- сотрудник выдавший книгу
34  
35  date_taken DATE NOT NULL DEFAULT CURRENT_DATE, -- дата, когда была взята
36  date_due DATE NOT NULL, -- дата, когда необходимо вернуть
37  date_return DATE NULL -- фактическая дата возврата. NULL если не еще вернул
38);
39
40
41-- функция для триггера. Кидает исключение, когда количество не сданных книг на руках у читателя больше 2-х.
42CREATE OR REPLACE FUNCTION check_books_number() RETURNS trigger AS $$
43  DECLARE
44    taken_books_count INTEGER;
45  BEGIN
46    -- находим количество неодданых книг у читателя
47    SELECT INTO taken_books_count COUNT(*)
48      FROM book_take
49      WHERE NEW.reader_id = book_take.reader_id AND book_take.date_return IS NULL;
50      
51    -- RAISE NOTICE 'taken_books_count of % = %', NEW.reader_id, taken_books_count;
52    
53    -- проверяем что их не больше 2-х
54    IF taken_books_count > 2 THEN
55      RAISE NOTICE 'У читателя c id=% уже есть 3 книги на руках', NEW.reader_id;
56      RETURN NULL;
57    END IF;
58    
59    RETURN NEW;
60  END;
61$$ LANGUAGE PLPGSQL;
62
63
64-- триггер
65drop trigger if exists check_books_number_trigger on book_take;
66CREATE TRIGGER check_books_number_trigger
67  BEFORE INSERT
68  ON book_take
69  FOR EACH ROW
70  EXECUTE PROCEDURE check_books_number();
71
72
73-- список читателей обслужаных сотрудником за данный период
74CREATE OR REPLACE FUNCTION
75readers_of_employee(employee_surname VARCHAR, from_date DATE, _to_date DATE)
76RETURNS SETOF reader AS $$
77  SELECT DISTINCT r.*
78    FROM reader AS r
79    RIGHT JOIN book_take AS bt
80      ON r.id = bt.reader_id
81    LEFT JOIN employee AS e
82      ON bt.employee_id = e.id
83    WHERE e.second_name LIKE employee_surname AND bt.date_taken >= from_date AND bt.date_taken <= _to_date
84$$ LANGUAGE SQL;
85
86
87-- самый активный читатель в заданый период
88CREATE OR REPLACE FUNCTION
89most_active_reader(from_date DATE, _to_date DATE)
90RETURNS reader AS $$
91  SELECT r.*
92    FROM reader AS r
93    RIGHT JOIN book_take AS bt
94      ON r.id = bt.reader_id
95    WHERE bt.date_taken >= from_date AND bt.date_taken <= _to_date
96    GROUP BY r.id ORDER BY COUNT(bt.id) DESC LIMIT 1
97$$ LANGUAGE SQL;
98
99
100-- перегрузки под TIMESTAMPTZ
101CREATE OR REPLACE FUNCTION
102most_active_reader(from_date TIMESTAMPTZ, _to_date TIMESTAMPTZ)
103RETURNS reader AS $$
104  SELECT most_active_reader(from_date::date, _to_date::date)
105$$ LANGUAGE SQL;
106
107
108CREATE OR REPLACE FUNCTION
109readers_of_employee(employee_surname VARCHAR, from_date TIMESTAMPTZ, _to_date TIMESTAMPTZ)
110RETURNS SETOF reader AS $$
111  SELECT readers_of_employee(employee_surname, from_date::date, _to_date::date)
112$$ LANGUAGE SQL;
113
114
115--  Тестовые данные
116INSERT INTO book(author_name, title)
117VALUES
118  ('Ф. М. Достоевский', 'Идиот'),
119  ('А. Н. Толстой', 'Хождение по мукам'),
120  ('Л. Н. Толстой', 'Война и мир'),
121  ('Джордж Оруэл', 'Скотный двор'),
122  ('Джордж Оруэл', 'Памяти Каталонии')
123  ;
124
125INSERT INTO employee(first_name, second_name)
126VALUES
127  ('John', 'Doe'),
128  ('Иван', 'Кузнецов'),
129  ('Зигфрид', 'Терпигорев'),
130  ('Эмилиан', 'Голохвастов'),
131  ('Сергей', 'Смирнов')
132  ;
133
134INSERT INTO reader(first_name, second_name)
135VALUES
136  ('Иван', 'Смирнов'),
137  ('Петроний', 'Обижаев'),
138  ('Октавий', 'Губастов'),
139  ('Аскольд', 'Свирепов'),
140  ('Бова', 'Белотелов')
141  ;
142
143INSERT INTO book_take(book_id, reader_id, employee_id, date_taken, date_due, date_return)
144VALUES
145  (1, 1, 1, now() - INTERVAL '7 days', now() - INTERVAL '5 days', now() - INTERVAL '5 days'),
146  (1, 1, 1, now() - INTERVAL '7 days', now() - INTERVAL '5 days', now() - INTERVAL '2 days'),
147  (1, 1, 1, now() - INTERVAL '7 days', now() - INTERVAL '5 days', now() - INTERVAL '2 days'),
148  (1, 4, 1, now() - INTERVAL '7 days', now() - INTERVAL '5 days', now() - INTERVAL '2 days'),
149  (1, 4, 1, now() - INTERVAL '7 days', now() - INTERVAL '5 days', now() - INTERVAL '2 days'),
150  (2, 2, 1, now() - INTERVAL '14 days', now() - INTERVAL '5 days', now() - INTERVAL '2 days'),
151  (3, 2, 2, now() - INTERVAL '28 days', now() - INTERVAL '5 days', now() - INTERVAL '2 days')
152  ;
153  
154select readers_of_employee('Doe', (now() - INTERVAL '15 days')::date, (now() - INTERVAL '13 days')::date);
155select most_active_reader((now() - INTERVAL '15 days')::date, (now() - INTERVAL '13 days')::date);