ZNQqHdFy

1CREATE TABLE Personnel
2( emp_nbr INTEGER
3 DEFAULT 0 NOT NULL PRIMARY KEY,
4 emp_name VARCHAR( 10 )
5 DEFAULT '{ {vacant} }' NOT NULL,
6 address VARCHAR( 35 ) NOT NULL,
7 birth_date DATE NOT NULL
8);
9
10INSERT INTO Personnel VALUES
11( 0, 'вакансия', '', '2014-05-19' ),
12( 1, 'Иван', 'ул. Любителей языка C', '1962-12-01' ),
13( 2, 'Петр', 'ул. UNIX гуру', '1965-10-21' ),
14( 3, 'Антон', 'ул. Ассемблерная', '1964-04-17' ),
15( 4, 'Захар', 'ул. им. СУБД PostgreSQL', '1963-09-27' ),
16( 5, 'Ирина', 'просп. Программистов', '1968-05-12' ),
17( 6, 'Анна', 'пер. Перловый', '1969-03-20' ),
18( 7, 'Андрей', 'пл. Баз данных', '1945-11-07' ),
19( 8, 'Николай', 'наб. ОС Linux', '1944-12-01' );
20
21DROP TABLE IF EXISTS Org_chart CASCADE;
22CREATE TABLE Org_chart
23( job_title VARCHAR( 30 )
24 NOT NULL PRIMARY KEY,
25 emp_nbr INTEGER
26 DEFAULT 0 NOT NULL
27 REFERENCES Personnel( emp_nbr )
28 ON DELETE SET DEFAULT
29 ON UPDATE CASCADE
30 DEFERRABLE,
31 boss_emp_nbr INTEGER
32 DEFAULT 0
33 REFERENCES Personnel( emp_nbr )
34 ON DELETE SET DEFAULT
35 ON UPDATE CASCADE
36 DEFERRABLE,
37 salary DECIMAL( 12, 4 )
38 NOT NULL CHECK ( salary >= 0.00 ),
39 CHECK ( ( boss_emp_nbr <> emp_nbr ) OR
40 ( boss_emp_nbr = 0 AND emp_nbr = 0 )
41 ),
42FOREIGN KEY ( boss_emp_nbr )
43 REFERENCES Org_chart ( emp_nbr )
44 ON DELETE SET DEFAULT
45 ON UPDATE CASCADE
46 DEFERRABLE,
47 UNIQUE ( emp_nbr )
48);
49
50
51INSERT INTO Org_chart VALUES
52( 'Президент', 1, NULL, 1000.00 ),
53( 'Вице-президент 1', 2, 1, 900.00 ),
54( 'Вице-президент 2', 3, 1, 800.00 ),
55( 'Архитектор', 4, 3, 700.00 ),
56( 'Ведущий программист', 5, 3, 600.00 ),
57( 'Программист C', 6, 3, 500.00 ),
58( 'Программист Perl', 7, 5, 450.00 ),
59( 'Оператор', 8, 5, 400.00 );
60
61CREATE OR REPLACE FUNCTION check_org_chart() RETURNS trigger
62AS
63$$
64BEGIN
65IF ( SELECT COUNT( * ) FROM Org_chart ) - 1 <>
66 ( SELECT COUNT( boss_emp_nbr ) FROM Org_chart )
67THEN
68 RAISE EXCEPTION 'Bad orgchart structure';
69 ELSE
70 IF ( TG_OP = 'DELETE' ) THEN
71 RETURN OLD;
72 ELSIF ( TG_OP = 'UPDATE' ) THEN
73 RETURN NEW;
74 ELSIF ( TG_OP = 'INSERT' ) THEN
75 RETURN NEW;
76 END IF;
77 RETURN NULL;
78 END IF;
79END;
80$$
81LANGUAGE plpgsql;
82
83DROP TRIGGER IF EXISTS check_org_chart ON Org_chart;
84
85CREATE TRIGGER check_org_chart
86AFTER INSERT OR UPDATE OR DELETE ON Org_chart
87 FOR EACH ROW EXECUTE PROCEDURE check_org_chart();
88
89CREATE OR REPLACE FUNCTION tree_test() RETURNS CHAR( 6 ) AS
90$$
91BEGIN
92 CREATE TEMP TABLE Tree ON COMMIT DROP AS
93 SELECT emp_nbr, boss_emp_nbr FROM Org_chart;
94 WHILE ( SELECT COUNT( * ) FROM Tree ) - 1
95 = ( SELECT COUNT( boss_emp_nbr ) FROM Tree )
96 LOOP
97 DELETE
98 FROM Tree
99 WHERE Tree.emp_nbr NOT IN ( SELECT T2.boss_emp_nbr
100 FROM Tree AS T2
101WHERE T2.boss_emp_nbr
102 IS NOT NULL );
103 END LOOP;
104 IF NOT EXISTS ( SELECT * FROM Tree )
105 THEN
106 RETURN ( 'Tree' );
107ELSE
108 RETURN ( 'Cycles' );
109 END IF;
110END;
111$$
112LANGUAGE plpgsql;
113
114CREATE OR REPLACE FUNCTION up_tree_traversal(
115 IN current_emp_nbr INTEGER )
116 RETURNS TABLE( emp_nbr INTEGER, boss_emp_nbr INTEGER ) AS
117$$
118BEGIN
119 WHILE EXISTS ( SELECT *
120 FROM Org_chart AS O
121WHERE O.emp_nbr = current_emp_nbr )
122 LOOP
123 RETURN QUERY SELECT O.emp_nbr, O.boss_emp_nbr
124 FROM Org_chart AS O
125WHERE O.emp_nbr = current_emp_nbr;
126current_emp_nbr = ( SELECT O.boss_emp_nbr
127 FROM Org_chart AS O
128WHERE O.emp_nbr = current_emp_nbr );
129 END LOOP;
130END;
131$$
132LANGUAGE plpgsql;
133
134CREATE OR REPLACE FUNCTION delete_subtree(
135 IN dead_guy INTEGER ) RETURNS VOID AS
136$$
137BEGIN
138CREATE TEMP SEQUENCE New_emp_nbr START WITH 1;
139 CREATE TEMP TABLE Working_table ( emp_nbr INTEGER NOT NULL )
140 ON COMMIT DROP;
141 SET CONSTRAINTS org_chart_emp_nbr_fkey,
142 org_chart_boss_emp_nbr_fkey,
143org_chart_boss_emp_nbr_fkey1
144 DEFERRED;
145 UPDATE Org_chart
146 SET emp_nbr = CASE WHEN emp_nbr = dead_guy
147 THEN nextval( 'New_emp_nbr' ) * -1
148 ELSE emp_nbr
149 END,
150 boss_emp_nbr = CASE WHEN boss_emp_nbr = dead_guy
151 THEN nextval( 'New_emp_nbr' ) * -1
152ELSE boss_emp_nbr
153 END
154 WHERE dead_guy IN ( emp_nbr, boss_emp_nbr );
155 WHILE EXISTS ( SELECT * FROM Org_chart
156 WHERE boss_emp_nbr < 0 AND emp_nbr >= 0 )
157 LOOP
158 DELETE FROM Working_table;
159 INSERT INTO Working_table
160 SELECT emp_nbr FROM Org_chart
161 WHERE boss_emp_nbr < 0;
162 UPDATE Org_chart
163 SET emp_nbr = nextval( 'New_emp_nbr' ) * -1
164 WHERE emp_nbr IN ( SELECT emp_nbr FROM Working_table );
165 UPDATE Org_chart
166 SET boss_emp_nbr = nextval( 'New_emp_nbr' ) * -1
167 WHERE boss_emp_nbr IN ( SELECT emp_nbr FROM Working_table
168);
169 END LOOP;
170 DELETE FROM Org_chart WHERE emp_nbr < 0;
171 SET CONSTRAINTS ALL IMMEDIATE;
172 DROP SEQUENCE New_emp_nbr;
173END;
174$$
175LANGUAGE plpgsql;
176
177DROP VIEW IF EXISTS Personnel_org_chart CASCADE;
178CREATE VIEW Personnel_org_chart
179( emp_nbr, emp, boss_emp_nbr, boss ) AS
180 SELECT O1.emp_nbr, E1.emp_name, O1.boss_emp_nbr, B1.emp_name
181 FROM ( Org_chart AS O1 LEFT OUTER JOIN Personnel AS B1
182 ON O1.boss_emp_nbr = B1.emp_nbr ), Personnel AS E1
183 WHERE O1.emp_nbr = E1.emp_nbr;
184
185
186DROP VIEW IF EXISTS Create_paths;
187CREATE VIEW Create_paths ( level1, level2, level3, level4 ) AS
188 SELECT O1.emp AS e1, O2.emp AS e2, O3.emp AS e3,
189 O4.emp AS e4
190 FROM Personnel_org_chart AS O1
191 LEFT OUTER JOIN Personnel_org_chart AS O2
192 ON O1.emp = O2.boss
193 LEFT OUTER JOIN Personnel_org_chart AS O3
194 ON O2.emp = O3.boss
195 LEFT OUTER JOIN Personnel_org_chart AS O4
196 ON O3.emp = O4.boss
197 -- Если закомментировать условие WHERE, тогда будут
198 -- построены цепочки, начинающиеся с каждого работника,
199 -- а не только с главного руководителя.
200 WHERE O1.emp = 'Иван';
201
202
203CREATE OR REPLACE FUNCTION delete_and_promote_subtree(
204 IN dead_guy INTEGER ) RETURNS VOID AS
205$$
206BEGIN
207 UPDATE Org_chart
208 SET boss_emp_nbr = ( SELECT boss_emp_nbr
209 FROM Org_chart
210WHERE emp_nbr = dead_guy
211 )
212 WHERE boss_emp_nbr = dead_guy;
213 DELETE FROM Org_chart WHERE emp_nbr = dead_guy;
214END;
215$$
216LANGUAGE plpgsql;