-
Notifications
You must be signed in to change notification settings - Fork 0
/
functions.sql
207 lines (173 loc) · 5.19 KB
/
functions.sql
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
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
161
162
163
164
165
166
167
168
169
170
171
172
173
174
175
176
177
178
179
180
181
182
183
184
185
186
187
188
189
190
191
192
193
194
195
196
197
198
199
200
201
202
203
204
205
206
207
------------------- UPDATE FACTORY -------------------
CREATE OR REPLACE FUNCTION
update_factory(p_id integer, new_name text, new_foundation_date date, new_address text, new_car_number integer)
RETURNS void AS $$
BEGIN
UPDATE factorys
SET name = new_name,
foundation_date = new_foundation_date,
address = new_address,
car_number = new_car_number
WHERE id = p_id;
END
$$ LANGUAGE plpgsql;
------------------- UPDATE CAR -------------------
CREATE OR REPLACE FUNCTION
update_car(p_id integer, new_name text, new_year integer, new_factory_id integer)
RETURNS void AS $$
BEGIN
UPDATE cars
SET name = new_name,
year = new_year,
factory_id = new_factory_id
WHERE id = p_id;
END
$$ LANGUAGE plpgsql;
------------------- DELETE FACTORY BY ID -------------------
CREATE OR REPLACE FUNCTION delete_factory(p_id integer)
RETURNS void AS $$
BEGIN
DELETE FROM factorys
WHERE id = p_id;
END
$$ LANGUAGE plpgsql;
------------------- DELETE CAR BY ID -------------------
CREATE OR REPLACE FUNCTION delete_car(f_id integer)
RETURNS void AS $$
BEGIN
DELETE FROM cars
WHERE id = f_id;
END
$$ LANGUAGE plpgsql;
------------------- DELETE FACTORY BY NAME -------------------
CREATE OR REPLACE FUNCTION delete_factorys_by_name(name_to_find text)
RETURNS void AS $$
BEGIN
DELETE FROM factorys
WHERE name = name_to_find;
END
$$ LANGUAGE plpgsql;
------------------- DELETE CAR BY NAME -------------------
CREATE OR REPLACE FUNCTION delete_cars_by_name(name_to_find text)
RETURNS void AS $$
BEGIN
DELETE FROM cars
WHERE name = name_to_find;
END
$$ LANGUAGE plpgsql;
------------------- FIND FACTORY -------------------
CREATE OR REPLACE FUNCTION find_factorys(name_to_find text)
RETURNS TABLE(id integer, name text, foundation_date date, address text, car_number integer)
AS $func$
BEGIN
RETURN QUERY
SELECT * FROM factorys p
WHERE p.name = name_to_find;
END
$func$ LANGUAGE plpgsql;
------------------- FIND CAR -------------------
CREATE OR REPLACE FUNCTION find_cars(name_to_find text)
RETURNS TABLE(id integer, name text, year integer, factory_id integer)
AS $func$
BEGIN
RETURN QUERY
SELECT * FROM cars f
WHERE f.name = name_to_find;
END
$func$ LANGUAGE plpgsql;
------------------- INSERT FACTORY -------------------
CREATE OR REPLACE FUNCTION insert_factory(p_id integer, name text, foundation_date date, address text, car_number integer)
RETURNS void AS $$
BEGIN
INSERT INTO factorys VALUES
(p_id, name, foundation_date, address, (SELECT COUNT(*) FROM cars f WHERE f.factory_id = p_id))
ON CONFLICT DO NOTHING;
END
$$ LANGUAGE plpgsql;
------------------- INSERT CAR -------------------
CREATE OR REPLACE FUNCTION insert_car(id integer, name text, year integer, factory_id integer)
RETURNS void AS $$
BEGIN
INSERT INTO cars VALUES
(id, name, year, factory_id)
ON CONFLICT DO NOTHING;
END
$$ LANGUAGE plpgsql;
------------------- DELETE ALL FACTORY -------------------
CREATE OR REPLACE FUNCTION delete_all_factorys()
RETURNS void AS $$
BEGIN
DELETE FROM factorys;
END
$$ LANGUAGE plpgsql;
------------------- DELETE ALL CAR -------------------
CREATE OR REPLACE FUNCTION delete_all_cars()
RETURNS void AS $$
BEGIN
DELETE FROM cars;
END
$$ LANGUAGE plpgsql;
------------------- SELECT ALL FACTORY -------------------
CREATE OR REPLACE FUNCTION get_factorys()
RETURNS TABLE(id integer, name text, foundation_date date, address text, car_number integer)
AS $func$
BEGIN
RETURN QUERY
SELECT * FROM factorys;
END
$func$ LANGUAGE plpgsql;
------------------- SELECT ALL CAR -------------------
CREATE OR REPLACE FUNCTION get_cars()
RETURNS TABLE(id integer, name text, year integer, factory_id integer)
AS $func$
BEGIN
RETURN QUERY
SELECT * FROM cars;
END
$func$ LANGUAGE plpgsql;
------------------- TRIGGER FUNCTION -------------------
CREATE OR REPLACE FUNCTION update_number() RETURNS TRIGGER AS $$
BEGIN
IF TG_OP = 'INSERT' THEN
UPDATE factorys
SET car_number = (SELECT COUNT(*) FROM cars f WHERE f.factory_id = NEW.factory_id)
WHERE id = NEW.factory_id;
RETURN NEW;
ELSIF TG_OP = 'UPDATE' THEN
UPDATE factorys
SET car_number = (SELECT COUNT(*) FROM cars f WHERE f.factory_id = OLD.factory_id)
WHERE id = OLD.factory_id;
UPDATE factorys
SET car_number = (SELECT COUNT(*) FROM cars f WHERE f.factory_id = NEW.factory_id)
WHERE id = NEW.factory_id;
RETURN NEW;
ELSIF TG_OP = 'DELETE' THEN
UPDATE factorys
SET car_number = (SELECT COUNT(*) FROM cars f WHERE f.factory_id = OLD.factory_id)
WHERE id = OLD.factory_id;
RETURN OLD;
END IF;
END;
$$ LANGUAGE plpgsql;
------------------- CREATE TABLES -----------------
CREATE OR REPLACE FUNCTION create_tables(dbname text, username text, passwd text) RETURNS void AS $$
BEGIN
CREATE TABLE factorys(
id integer PRIMARY KEY,
name text NOT NULL,
foundation_date date NOT NULL,
address text NOT NULL,
car_number integer NOT NULL DEFAULT 0
);
CREATE TABLE cars(
id integer PRIMARY KEY,
name text NOT NULL,
year integer NOT NULL,
factory_id integer NOT NULL);
CREATE INDEX on cars(name);
CREATE INDEX on factorys(name);
CREATE TRIGGER car_number_updater
AFTER INSERT OR UPDATE OR DELETE ON
cars FOR EACH ROW EXECUTE PROCEDURE update_number();
END
$$ LANGUAGE plpgsql;