gUYu2bAP

1DROP TABLE IF EXISTS Personnel CASCADE;
2CREATE TABLE Personnel
3( emp_nbr INTEGER -- код работника
4 DEFAULT 0 NOT NULL PRIMARY KEY,
5 emp_name VARCHAR( 10 ) -- имя работника
6 DEFAULT '{ {vacant} }' NOT NULL,
7 address VARCHAR( 35 ) NOT NULL, -- адрес работника
8 birth_date DATE NOT NULL -- день рождения работника
9);
1075
11-- Произведем первоначальное заполнение таблицы.
12INSERT INTO Personnel VALUES
13( 0, 'вакансия', '', '2014-05-19' ),
14( 1, 'Иван', 'ул. Любителей языка C', '1962-12-01' ),
15( 2, 'Петр', 'ул. UNIX гуру', '1965-10-21' ),
16( 3, 'Антон', 'ул. Ассемблерная', '1964-04-17' ),
17( 4, 'Захар', 'ул. им. СУБД PostgreSQL', '1963-09-27' ),
18( 5, 'Ирина', 'просп. Программистов', '1968-05-12' ),
19( 6, 'Анна', 'пер. Перловый', '1969-03-20' ),
20( 7, 'Андрей', 'пл. Баз данных', '1945-11-07' ),
21( 8, 'Николай', 'наб. ОС Linux', '1944-12-01' );
22-- -----------------------------------------------------------
23-- Таблица "Организационная структура"
24-- -----------------------------------------------------------
25DROP TABLE IF EXISTS Org_chart CASCADE;
26CREATE TABLE Org_chart
27( job_title VARCHAR( 30 ) -- наименование должности
28 NOT NULL PRIMARY KEY,
29 emp_nbr INTEGER -- код работника
30 DEFAULT 0 NOT NULL -- 0 означает вакантную должность
31 REFERENCES Personnel( emp_nbr ) -- внешний ключ
32 ON DELETE SET DEFAULT
33 ON UPDATE CASCADE
34 -- Это ограничение будет отключаться при выполнении
35 -- одной из хранимых процедур, поэтому DEFERRABLE.
36 DEFERRABLE,
37 boss_emp_nbr INTEGER -- код начальника данного работника
38 DEFAULT 0
39 -- Поскольку null означает корень иерархии, то ограничение
40 -- NOT NULL вводить не будем.
41 REFERENCES Personnel( emp_nbr ) -- внешний ключ
42 ON DELETE SET DEFAULT
43 ON UPDATE CASCADE
44 -- Это ограничение будет отключаться при выполнении
45 -- одной из хранимых процедур, поэтому DEFERRABLE.
46 DEFERRABLE,
47 salary DECIMAL( 12, 4 ) -- зарплата работника, занимающего
48 -- эту должность
49 NOT NULL CHECK ( salary >= 0.00 ),
50 -- Работник не может быть сам себе начальником,
51 -- т. е. код работника не должен совпадать с кодом
52 -- начальника);
53 -- если должность не занята, то код работника и код
54 -- начальника равны 0.
55 CHECK ( ( boss_emp_nbr <> emp_nbr ) OR
56 ( boss_emp_nbr = 0 AND emp_nbr = 0 )
57 ),
58 -- Без этого внешнего ключа возможна ситуация, когда 
5976
60 -- удаляется запись, значение поля emp_nbr которой
61 -- используется в качестве значения поля boss_emp_nbr
62 -- в других записях. Другими словами, работник, которого
63 -- нет в орг. структуре, является начальником других
64 -- работников, присутствующих в орг. структуре).
65 FOREIGN KEY ( boss_emp_nbr )
66 REFERENCES Org_chart ( emp_nbr )
67 ON DELETE SET DEFAULT
68 ON UPDATE CASCADE
69 DEFERRABLE,
70 -- Пришлось добавить и это ограничение, иначе
71 -- внешний ключ FOREIGN KEY ( boss_emp_nbr ) создать
72 -- невозможно.
73 UNIQUE ( emp_nbr )
74);
75-- Произведем первоначальное заполнение таблицы.
76-- Обратите внимание, что у главы компании нет начальника,
77-- поэтому значение NULL.
78INSERT INTO Org_chart VALUES
79( 'Президент', 1, NULL, 1000.00 ),
80( 'Вице-президент 1', 2, 1, 900.00 ),
81( 'Вице-президент 2', 3, 1, 800.00 ),
82( 'Архитектор', 4, 3, 700.00 ),
83( 'Ведущий программист', 5, 3, 600.00 ),
84( 'Программист C', 6, 3, 500.00 ),
85( 'Программист Perl', 7, 5, 450.00 ),
86( 'Оператор', 8, 5, 400.00 );
87
88CREATE OR REPLACE FUNCTION check_org_chart() RETURNS trigger
89AS
90$$
91BEGIN
92 -- Эта функция только не позволяет сделать две и более
93-- записи с идентификатором босса, равным null, но против
94 -- зацикливаний она бессильна.
95 -- Это условие не позволяет удалить из таблицы последнюю
96 -- запись (главный босс должен быть всегда -- это так и
97 -- нужно?).
98 -- ПРИМЕЧАНИЕ. Условие COUNT( boss_emp_nbr ) подсчитывает
99 -- только те записи, у которых значение поля boss_emp_nbr
10077
101 -- не равно NULL. Условие COUNT( * ) подсчитывает все записи.
102 IF ( SELECT COUNT( * ) FROM Org_chart ) - 1 <>
103 ( SELECT COUNT( boss_emp_nbr ) FROM Org_chart )
104THEN
105 -- Прервем выполнение функции и выведем сообщение.
106 RAISE EXCEPTION 'Bad orgchart structure';
107 ELSE
108 -- В зависимости от вида операции (встроенная переменная
109 -- TG_OP) с таблицей возвратим либо старую (OLD), либо
110 -- новую (NEW) версии строки таблицы.
111 IF ( TG_OP = 'DELETE' ) THEN
112 RETURN OLD;
113 ELSIF ( TG_OP = 'UPDATE' ) THEN
114 RETURN NEW;
115 ELSIF ( TG_OP = 'INSERT' ) THEN
116 RETURN NEW;
117 END IF;
118 RETURN NULL;
119 END IF;
120END;
121$$
122LANGUAGE plpgsql;
123
124DROP TRIGGER IF EXISTS check_org_chart ON Org_chart;
125
126CREATE TRIGGER check_org_chart
127AFTER INSERT OR UPDATE OR DELETE ON Org_chart
128 FOR EACH ROW EXECUTE PROCEDURE check_org_chart();
129
130-- -----------------------------------------------------------
131-- Функция для проверки структуры дерева на предмет
132-- отсутствия циклов.
133-- -----------------------------------------------------------
134CREATE OR REPLACE FUNCTION tree_test() RETURNS CHAR( 6 ) AS
135$$
136BEGIN
137 -- Создадим временную таблицу на основе иерархии
138 -- должностей.
139 CREATE TEMP TABLE Tree ON COMMIT DROP AS
140 SELECT emp_nbr, boss_emp_nbr FROM Org_chart;
141 -- Удаляем листья дерева. Условие означает следующее:
142 -- SELECT COUNT( * ) -- общее фактическое число узлов
143 -- дерева, т. к. каждая запись в таблице соответствует
144 -- одному узлу;
145 -- SELECT COUNT( * ) - 1 -- теоретическое число ребер
146 -- дерева, которое должно быть на 1 меньше фактического
147 -- числа узлов;
148 -- SELECT COUNT( boss_emp_nbr ) -- число записей,
149 -- для которых значение поля boss_emp_nbr не равно NULL
150 -- (см. описание функции COUNT()), должно быть ровно на 1
151 -- меньше, чем COUNT( * ), т. к. только у одного работника
152 -- значение поля boss_emp_nbr может быть равно NULL --
153 -- это глава организации.
154 WHILE ( SELECT COUNT( * ) FROM Tree ) - 1
155 = ( SELECT COUNT( boss_emp_nbr ) FROM Tree )
156 LOOP
157 -- Удаляем записи (строки) о работниках, которые
158 -- не являются начальниками ни для одного из других
159 -- работников (в подзапросе выбираются все начальники).
160 DELETE
161 FROM Tree
162 WHERE Tree.emp_nbr NOT IN ( SELECT T2.boss_emp_nbr
163 FROM Tree AS T2
164WHERE T2.boss_emp_nbr
165 IS NOT NULL );
166 END LOOP;
167 -- Эта проверка должна выполняться уже после завершения
168 -- удаления записей из таблицы Tree. Если записей
169 -- не осталось, значит, дерево связанное.
170 IF NOT EXISTS ( SELECT * FROM Tree )
171 THEN
172 RETURN ( 'Tree' );
173 -- Если хоть одна запись осталась, значит,
174 -- в дереве есть циклы.
175 ELSE
176 RETURN ( 'Cycles' );
177 END IF;
178END;
179$$
180LANGUAGE plpgsql;
181
182-- -----------------------------------------------------------
183-- Функция для обхода дерева снизу вверх, начиная с конкретного
184-- узла.
185-- ВАРИАНТ 1.
186-- -----------------------------------------------------------
187CREATE OR REPLACE FUNCTION up_tree_traversal(
188 IN current_emp_nbr INTEGER )
189 RETURNS TABLE( emp_nbr INTEGER, boss_emp_nbr INTEGER ) AS
190$$
191BEGIN
192 -- Выбираем запись для текущего работника. На первой
193 -- итерации это будет работник, с которого начинается
194 -- обход дерева вверх.
195 WHILE EXISTS ( SELECT *
196 FROM Org_chart AS O
197WHERE O.emp_nbr = current_emp_nbr )
198 LOOP
199 -- Если нужно, то выполним какое-либо действие
200 -- для текущего узла дерева (т. е. для текущего
201 -- работника). Для этого нужно процедуру SomeProc
202 -- заменить на какую-то полезную процедуру.
203 -- CALL SomeProc (current_emp_nbr);
204 -- Добавим очередную пару (работник; начальник)
205 -- к формируемому множеству таких пар.
206 -- ПРИМЕЧАНИЕ. Этот оператор RETURN не завершает
207 -- выполнение процедуры, а лишь ДОБАВЛЯЕТ очередную
208 -- запись (строку) к результирующей таблице.
209 RETURN QUERY SELECT O.emp_nbr, O.boss_emp_nbr
210 FROM Org_chart AS O
211WHERE O.emp_nbr = current_emp_nbr;
212 -- Идем вверх по дереву к корню. Теперь текущим
213 -- работником становится начальник только что
214 -- обработанного работника, тем самым мы перемещаемся
215 -- на один уровень вверх по дереву. Когда текущим
216 -- работником станет главный начальник, у которого
217 -- уже нет начальника, тогда результатом этого запроса
218 -- будет current_emp_nbr = NULL, в результате чего
219 -- условие цикла будет не выполнено, и цикл завершится.
220 current_emp_nbr = ( SELECT O.boss_emp_nbr
221 FROM Org_chart AS O
222WHERE O.emp_nbr = current_emp_nbr );
223 END LOOP;
224END;
225$$
226LANGUAGE plpgsql;
227
228-- -----------------------------------------------------------
229-- Функция для обхода дерева снизу вверх, начиная с конкретного
230-- узла.
231-- ВАРИАНТ 2.
232-- -----------------------------------------------------------
233CREATE OR REPLACE FUNCTION up_tree_traversal2(
234 IN current_emp_nbr INTEGER )
235 RETURNS SETOF RECORD AS
236$$
237DECLARE
238 -- В соответствии с выбранным типом результирующего значения,
239 -- возвращаемого функцией, объявим переменную типа RECORD.
240 rec RECORD;
241BEGIN
242 -- Выбираем запись для текущего работника. На первой
243 -- итерации это будет работник, с которого начинается
244 -- обход дерева вверх.
245 WHILE EXISTS ( SELECT *
246 FROM Org_chart AS O
247WHERE O.emp_nbr = current_emp_nbr )
248 LOOP
249 -- Если нужно, то выполним какое-либо действие
250 -- для текущего узла дерева (т. е. для текущего
251 -- работника). Для этого нужно процедуру SomeProc
252 -- заменить на какую-то полезную процедуру.
253 -- CALL SomeProc (current_emp_nbr);
254 -- Добавим очередную пару (работник; начальник)
255 -- к формируемому множеству таких пар.
256 SELECT O.emp_nbr, O.boss_emp_nbr
257 INTO rec
258 FROM Org_chart AS O
259 WHERE O.emp_nbr = current_emp_nbr;
260 -- ПРИМЕЧАНИЕ. Этот оператор RETURN не завершает
261 -- выполнение процедуры, а лишь ДОБАВЛЯЕТ очередную
262 -- запись к результирующему множеству.
263 RETURN NEXT rec;
264
265 -- Идем вверх по дереву к корню. Теперь текущим
266 -- работником становится начальник только что
267 -- обработанного работника, тем самым мы перемещаемся
268 -- на один уровень вверх по дереву. Когда текущим
269 -- работником станет главный начальник, у которого
270 -- уже нет начальника, тогда результатом этого запроса
271 -- будет current_emp_nbr = NULL, в результате чего
272 -- условие цикла будет не выполнено, и цикл завершится.
273 current_emp_nbr = ( SELECT O.boss_emp_nbr
274 FROM Org_chart AS O
275WHERE O.emp_nbr = current_emp_nbr );
276 END LOOP;
277 RETURN;
278END;
279$$
280LANGUAGE plpgsql;
281
282-- -----------------------------------------------------------
283-- Функция для удаления поддерева.
284-- -----------------------------------------------------------
285CREATE OR REPLACE FUNCTION delete_subtree(
286 IN dead_guy INTEGER ) RETURNS VOID AS
287$$
288-- Параметр dead_guy -- код работника, возглавляющего поддерево.
289BEGIN
290 -- Создадим врЕменную последовательность. Она нужна
291 -- для того, чтобы формировать отрицательные значения
292 -- для полей emp_nbr и boss_emp_nbr.
293 -- У J. Celko использовалось значение -99999, которое
294 -- записывалось в поля emp_nbr и boss_emp_nbr удаляемых
295 -- записей. Это значение служило меткой удаляемой записи.
296 -- Но мы добавили ограничение UNIQUE ( emp_nbr )
297 -- в таблицу Org_chart. Поэтому теперь уже стало
298 -- невозможно иметь более одной записи со значением поля
299 -- emp_nbr равным -99999. Мы вынуждены записывать в это поле
300 -- РАЗЛИЧНЫЕ значения в разных записях таблицы Org_chart.
301 -- А такие значения удобно формировать, используя
302 -- последовательность.
303 CREATE TEMP SEQUENCE New_emp_nbr START WITH 1;
304 -- Создадим временную таблицу.
305 CREATE TEMP TABLE Working_table ( emp_nbr INTEGER NOT NULL )
306 ON COMMIT DROP;
307 -- Отложим проверку всех ограничений FOREIGN KEY
308 -- до конца транзакции, иначе СУБД не позволит нам
309 -- выполнять обновления полей emp_nbr и boss_emp_nbr:
310 -- мы будем записывать в них отрицательные значения,
311 -- а этих значений нет в таблице Personnel, на которую
312 -- ссылается таблица Org_chart.
313 SET CONSTRAINTS org_chart_emp_nbr_fkey,
314 org_chart_boss_emp_nbr_fkey,
315org_chart_boss_emp_nbr_fkey1
316 DEFERRED;
317 -- Пометим корень удаляемого поддерева и всех
318 -- непосредственных подчиненных путем записи в поле
319 -- emp_nbr или в поле boss_emp_nbr отрицательного значения,
320 -- формируемого с помощью последовательности.
321 UPDATE Org_chart
322 SET emp_nbr = CASE WHEN emp_nbr = dead_guy
323 THEN nextval( 'New_emp_nbr' ) * -1
324 ELSE emp_nbr
325 END,
326 boss_emp_nbr = CASE WHEN boss_emp_nbr = dead_guy
327 THEN nextval( 'New_emp_nbr' ) * -1
328ELSE boss_emp_nbr
329 END
330 -- Условие WHERE означает, что выбираются лишь записи,
331 -- в которых либо в поле emp_nbr, либо в поле boss_emp_nbr
332 -- стоит значение идентификатора "главы" удаляемого поддерева.
333 WHERE dead_guy IN ( emp_nbr, boss_emp_nbr );
334 -- Помечаем листья дерева, т. е. записи для работников,
335 -- не являющихся начальниками для других работников.
336 WHILE EXISTS ( SELECT * FROM Org_chart
337 WHERE boss_emp_nbr < 0 AND emp_nbr >= 0 )
338 LOOP
339 -- Получим список подчиненных следующего уровня.
340 -- Сначала удалим все записи из временной таблицы.
341 DELETE FROM Working_table;
342
343 -- Выбираем подчиненных.
344 INSERT INTO Working_table
345 SELECT emp_nbr FROM Org_chart
346 WHERE boss_emp_nbr < 0;
347 -- Пометим следующий уровень подчиненных.
348 -- Получаем очередное число из последовательности
349 -- и умножаем его на -1, поскольку нам нужны
350 -- отрицательные коды работников, т. к. именно
351 -- отрицательные значения являются меткой удаляемой записи.
352 UPDATE Org_chart
353 SET emp_nbr = nextval( 'New_emp_nbr' ) * -1
354 WHERE emp_nbr IN ( SELECT emp_nbr FROM Working_table );
355 -- Помечаем начальников следующего уровня
356 -- (при движении вниз по дереву).
357 UPDATE Org_chart
358 SET boss_emp_nbr = nextval( 'New_emp_nbr' ) * -1
359 WHERE boss_emp_nbr IN ( SELECT emp_nbr FROM Working_table
360);
361 END LOOP;
362 -- Удаляем все помеченные узлы.
363 DELETE FROM Org_chart WHERE emp_nbr < 0;
364 -- Снова активизируем все ограничения.
365 SET CONSTRAINTS ALL IMMEDIATE;
366 -- Удалим врЕменную последовательность.
367 DROP SEQUENCE New_emp_nbr;
368END;
369$$
370LANGUAGE plpgsql;
371
372-- -----------------------------------------------------------
373-- Представление (VIEW) для реконструирования организационной
374-- структуры.
375-- -----------------------------------------------------------
376DROP VIEW IF EXISTS Personnel_org_chart CASCADE;
377CREATE VIEW Personnel_org_chart
378( emp_nbr, emp, boss_emp_nbr, boss ) AS
379 -- За основу принимается таблица Org_chart).
380 -- ПРИМЕЧАНИЕ. LEFT OUTER JOIN необходим,
381 -- т. к. у руководителя организации нет начальника
382 -- и значение поля boss_emp_nbr у него NULL.
383 SELECT O1.emp_nbr, E1.emp_name, O1.boss_emp_nbr, B1.emp_name
384 FROM ( Org_chart AS O1 LEFT OUTER JOIN Personnel AS B1
385 ON O1.boss_emp_nbr = B1.emp_nbr ), Personnel AS E1
386 WHERE O1.emp_nbr = E1.emp_nbr;
387
388-- -----------------------------------------------------------
389-- Построение всех путей сверху дерева вниз
390-- (только для четырех уровней иерархии)
391-- -----------------------------------------------------------
392DROP VIEW IF EXISTS Create_paths;
393CREATE VIEW Create_paths ( level1, level2, level3, level4 ) AS
394 SELECT O1.emp AS e1, O2.emp AS e2, O3.emp AS e3,
395 O4.emp AS e4
396 FROM Personnel_org_chart AS O1
397 LEFT OUTER JOIN Personnel_org_chart AS O2
398 ON O1.emp = O2.boss
399 LEFT OUTER JOIN Personnel_org_chart AS O3
400 ON O2.emp = O3.boss
401 LEFT OUTER JOIN Personnel_org_chart AS O4
402 ON O3.emp = O4.boss
403 -- Если закомментировать условие WHERE, тогда будут
404 -- построены цепочки, начинающиеся с каждого работника,
405 -- а не только с главного руководителя.
406 WHERE O1.emp = 'Иван';
407
408-- -----------------------------------------------------------
409-- Функция для удаления элемента иерархии и продвижения
410-- дочерних элементов на один уровень вверх (т. е. к "бабушке").
411-- -----------------------------------------------------------
412CREATE OR REPLACE FUNCTION delete_and_promote_subtree(
413 IN dead_guy INTEGER ) RETURNS VOID AS
414$$
415-- Параметр dead_guy -- код работника, возглавляющего поддерево.
416BEGIN
417 -- Назначить нового начальника всем непосредственным
418 -- подчиненным удаляемого работника.
419 UPDATE Org_chart
420 -- Получим код начальника для удаляемого работника.
421 SET boss_emp_nbr = ( SELECT boss_emp_nbr
422 FROM Org_chart
423WHERE emp_nbr = dead_guy
424 )
425 WHERE boss_emp_nbr = dead_guy;
426 -- Теперь удаляем работника. Все его подчиненные уже
427 -- переподчинены вышестоящему начальнику.
428 DELETE FROM Org_chart WHERE emp_nbr = dead_guy;
429END;
430$$
431LANGUAGE plpgsql;