· 7 years ago · Jan 16, 2019, 01:26 AM
1-- create table if not exists logger
2-- (
3-- id serial primary key,
4-- operation text,
5-- on_table text,
6-- record_id integer,
7-- old_data text,
8-- new_data text
9-- );
10
11-- create table if not exists customer
12-- (
13-- id serial primary key,
14-- first_name text,
15-- last_name text,
16-- address text,
17-- phone_number text unique
18-- );
19
20-- create table if not exists product
21-- (
22-- id serial primary key,
23-- nane text,
24-- available_quantity text
25-- );
26
27
28-- create table if not exists orders
29-- (
30-- id serial primary key,
31-- customer_id integer,
32-- additional_info text,
33-- status text,
34-- Foreign key(customer_id) references customer(id)
35-- );
36
37-- create table if not exists orders_extended
38-- (
39-- id serial primary key,
40-- orders_id integer,
41-- product_id integer,
42-- quantity integer,
43-- Foreign key(orders_id) references orders(id),
44-- Foreign key(product_id) references product(id)
45-- );
46
47
48-- INSERT into customer(first_name, last_name, address, phone_number) values('Mateusz', 'Roczek', 'Limanowskiego 12, Lodz, 97-410 Lodz', '111222333');
49-- INSERT into customer(first_name, last_name, address, phone_number) values('Mateusz', 'Motylinski', 'Zgierska 12, Zgierz, 97-215 Zgierz', '222333111');
50-- INSERT into customer(first_name, last_name, address, phone_number) values('Dominik', 'Mjerzejewski', 'Pogonowskiego 15, Warszawa, 23-120 Warszawa', '444222333');
51-- INSERT into customer(first_name, last_name, address, phone_number) values('Radoslaw', 'Skorzewski', '3-go Maja 23, Lutomiersk, 95-083 Lutomiersk', '333333111');
52-- INSERT into customer(first_name, last_name, address, phone_number) values('Mateusz', 'Roczek', 'Limanowskiego 24/2, Krakow, 12-534 Krakow', '666222333');
53
54
55-- INSERT into product(name, available_quantity) values('Pepsi 2L', 20);
56-- INSERT into product(name, available_quantity) values('Zapiekanki', 14);
57-- INSERT into product(name, available_quantity) values('Bulki do HotDogow', 50);
58-- INSERT into product(name, available_quantity) values('Parowki', 10);
59-- INSERT into product(name, available_quantity) values('Pizza 4 Sery', 5);
60-- INSERT into product(name, available_quantity) values('Pizza z Kurczakiem', 2);
61-- INSERT into product(name, available_quantity) values('Jablko', 0);
62
63-- INSERT into orders(customer_id, additional_info, status) values(1, 'MA BYC NA JUTRO', 'Przyjete');
64
65
66
67
68-- ALTER TABLE orders ALTER COLUMN customer_id SET NOT NULL;
69-- ALTER TABLE orders_extended ALTER COLUMN quantity SET NOT NULL;
70
71--ALTER TABLE product ALTER COLUMN quantity drop default;
72
73
74-- select * from product;
75
76/*
77CREATE OR REPLACE FUNCTION is_product_available()
78RETURNS TRIGGER AS $$
79BEGIN
80 IF ((Select is_avaiable from product where id = new.product_id) = 'true') THEN
81 RAISE NOTICE 'Product: %, is avaiable.', (Select name from product where id = new.product_id);
82 RETURN NEW;
83 END IF;
84 RAISE NOTICE 'Product: %, is not avaiable.', (Select name from product where id = new.product_id);
85 RETURN NULL;
86END;
87$$ LANGUAGE plpgsql;
88
89
90CREATE TRIGGER product_availablity
91BEFORE
92--(BEFORE)
93--(INSTEAD OF)
94INSERT OR UPDATE
95ON orders_extended
96FOR EACH ROW
97EXECUTE PROCEDURE is_product_available();
98*/
99
100/*
101CREATE OR REPLACE FUNCTION is_product_quantity()
102RETURNS TRIGGER AS $$
103BEGIN
104 IF ((Select quantity from product where id = new.product_id) > new.quantity or (Select quantity from product where id = new.product_id) = new.quantity) THEN
105 RAISE NOTICE 'The given quantity of product: %, is avaiable.', (Select name from product where id = new.product_id);
106 RETURN NEW;
107 END IF;
108 RAISE EXCEPTION 'Product: %, is not avaiable with that amount of quantity.', (Select name from product where id = new.product_id) || ' Precced to abort.';
109 RETURN NULL;
110END;
111$$ LANGUAGE plpgsql;
112
113
114CREATE TRIGGER product_quantity
115BEFORE
116--(BEFORE)
117--(INSTEAD OF)
118INSERT OR UPDATE
119ON orders_extended
120FOR EACH ROW
121EXECUTE PROCEDURE is_product_quantity();
122*/
123
124/*
125CREATE OR REPLACE FUNCTION create_order(IN a_client_id int, a_additional_info text)
126RETURNS TEXT
127AS $$
128BEGIN
129 IF ((Select id from customer where id = a_client_id) IS NOT NULL) THEN
130 INSERT INTO orders(customer_id, additional_info) values(a_client_id, a_additional_info);
131 RETURN 'Order has been successfully created!';
132 ELSE
133 RETURN 'Order creation has failed, client with provided id does not exists!';
134 END IF;
135END;
136$$ LANGUAGE plpgsql;
137*/
138
139/*
140CREATE OR REPLACE FUNCTION add_to_order(IN a_order_id int, a_product_id int, a_quantity int)
141RETURNS TEXT
142AS $$
143BEGIN
144 IF ((Select id from orders where id = a_order_id) IS NOT NULL AND
145 (Select id from product where id = a_product_id) IS NOT NULL) THEN
146 INSERT INTO orders_extended(orders_id, product_id, quantity) values(a_order_id, a_product_id, a_quantity);
147 UPDATE product SET quantity = quantity - a_quantity WHERE id = a_product_id;
148 RETURN 'Product has been added to order!';
149 ELSE
150 RETURN 'Order creation has failed, client with provided id or product does not exists!';
151 END IF;
152END;
153$$ LANGUAGE plpgsql;
154*/
155
156
157-- CREATE OR REPLACE FUNCTION showAvailableProducts()
158-- RETURNS TEXT
159-- AS $$
160-- DECLARE
161-- kursorek refcursor;
162-- v_id product.id%TYPE;
163-- v_name product.name%TYPE;
164-- v_quantity product.quantity%TYPE;
165
166-- v_counter int;
167
168-- BEGIN
169-- OPEN kursorek FOR SELECT id, name, quantity
170-- FROM product p
171-- WHERE p.is_avaiable = 'true';
172
173-- v_counter := 0;
174
175-- LOOP
176-- FETCH kursorek INTO v_id, v_name, v_quantity;
177-- EXIT WHEN NOT FOUND;
178-- v_counter = v_counter + 1;
179-- RAISE NOTICE 'ID: %, Name: %, Quantity: %', v_id, v_name, v_quantity;
180
181-- END LOOP;
182
183-- CLOSE kursorek;
184-- RETURN 'Shop has: ' || v_counter || ' avaiable products.';
185-- END;
186-- $$ LANGUAGE plpgsql;
187
188
189/*
190CREATE OR REPLACE FUNCTION order_proceed(IN a_order_id int)
191RETURNS TEXT
192AS $$
193DECLARE
194 v_status orders.status%TYPE;
195BEGIN
196 IF ((SELECT id FROM orders WHERE id = a_order_id) IS NOT NULL) THEN
197 v_status := (SELECT status FROM orders WHERE id = a_order_id);
198 IF (v_status = 'Pending') THEN
199 UPDATE orders SET status = 'Approved' WHERE id = a_order_id;
200 RETURN 'Order status has been changed to Approved';
201 ELSIF (v_status = 'Approved') THEN
202 UPDATE orders SET status = 'Completed' WHERE id = a_order_id;
203 RETURN 'Order status has been changed to Completed';
204 ELSIF (v_status = 'Completed') THEN
205 RETURN 'Order is completed, there is nothing more to do.';
206 ELSE
207 RETURN 'Order is Cancelled';
208 END IF;
209 ELSE
210 RETURN 'Order with given id does not exists!';
211 END IF;
212END;
213$$ LANGUAGE plpgsql;
214*/
215
216/*
217CREATE OR REPLACE FUNCTION order_cancel(IN a_order_id int)
218RETURNS TEXT
219AS $$
220DECLARE
221 v_status orders.status%TYPE;
222 rec RECORD;
223BEGIN
224 IF ((SELECT id FROM orders WHERE id = a_order_id) IS NOT NULL) THEN
225 v_status := (SELECT status FROM orders WHERE id = a_order_id);
226 IF (v_status != 'Pending') THEN
227
228 FOR rec in SELECT * FROM orders_extended WHERE orders_id = a_order_id
229 LOOP
230 UPDATE product SET quantity = quantity + rec.quantity WHERE id = rec.product_id;
231 END LOOP;
232
233 UPDATE orders SET status = 'Cancelled' WHERE id = a_order_id;
234 RETURN 'Order has been Cancelled';
235 ELSE
236 RETURN 'Orders with status higher than Pending cannot be cancelled!';
237 END IF;
238 ELSE
239 RETURN 'Order with given id does not exists!';
240 END IF;
241END;
242$$ LANGUAGE plpgsql;
243*/
244
245/*
246CREATE OR REPLACE FUNCTION orders_logger()
247RETURNS TRIGGER AS $$
248DECLARE
249 TYP_OPER TEXT;
250 v_OLD_DATA TEXT;
251 v_NEW_DATA TEXT;
252BEGIN
253 TYP_OPER := TG_OP;
254 IF (TG_OP = 'INSERT') THEN
255 v_OLD_DATA := '';
256 v_NEW_DATA := 'ID: ' || NEW.id || ' Customer_ID: ' || NEW.customer_id || ' Additional_info: ' || NEW.additional_info || ' Status' || NEW.status;
257 INSERT INTO LOGGER(operation, on_table, record_id, old_data, new_data, changed_at) VALUES(TYP_OPER, TG_TABLE_NAME, NEW.ID, v_OLD_DATA, v_NEW_DATA, CURRENT_DATE);
258 RETURN NEW;
259 ELSEIF (TG_OP = 'UPDATE') THEN
260 v_OLD_DATA := 'ID: ' || OLD.id || ' Customer_ID: ' || OLD.customer_id || ' Additional_info: ' || OLD.additional_info || ' Status' || OLD.status;
261 v_NEW_DATA := 'ID: ' || NEW.id || ' Customer_ID: ' || NEW.customer_id || ' Additional_info: ' || NEW.additional_info || ' Status' || NEW.status;
262 INSERT INTO LOGGER(operation, on_table, record_id, old_data, new_data, changed_at) VALUES(TYP_OPER, TG_TABLE_NAME, NEW.ID, v_OLD_DATA, v_NEW_DATA, CURRENT_DATE);
263 RETURN NEW;
264 ELSE
265 v_OLD_DATA := '';
266 v_NEW_DATA:= '';
267 INSERT INTO LOGGER(operation, on_table, record_id, old_data, new_data, changed_at) VALUES(TYP_OPER, TG_TABLE_NAME, NULL, v_OLD_DATA, v_NEW_DATA, CURRENT_DATE);
268 RETURN OLD;
269 END IF;
270END;
271$$ LANGUAGE plpgsql;
272
273
274CREATE TRIGGER orders_log_t
275AFTER
276--(BEFORE)
277--(INSTEAD OF)
278INSERT OR UPDATE OR DELETE
279ON orders
280FOR EACH ROW
281EXECUTE PROCEDURE orders_logger();
282*/
283
284/*
285CREATE OR REPLACE FUNCTION add_to_orders_logger()
286RETURNS TRIGGER AS $$
287DECLARE
288 TYP_OPER TEXT;
289 v_OLD_DATA TEXT;
290 v_NEW_DATA TEXT;
291BEGIN
292 TYP_OPER := TG_OP;
293 IF (TG_OP = 'INSERT') THEN
294 v_OLD_DATA := '';
295 v_NEW_DATA := 'ID:' || NEW.id || ' Orders_ID: ' || NEW.orders_id || ' Product_ID: ' || NEW.product_id || ' Quantity:' || NEW.quantity;
296 INSERT INTO LOGGER(operation, on_table, record_id, old_data, new_data, changed_at) VALUES(TYP_OPER, TG_TABLE_NAME, NEW.ID, v_OLD_DATA, v_NEW_DATA, CURRENT_DATE);
297 RETURN NEW;
298 ELSEIF (TG_OP = 'UPDATE') THEN
299 v_OLD_DATA := 'ID:' || OLD.id || ' Orders_ID: ' || OLD.orders_id || ' Product_ID: ' || OLD.product_id || ' Quantity:' || OLD.quantity;
300 v_NEW_DATA := 'ID:' || NEW.id || ' Orders_ID: ' || NEW.orders_id || ' Product_ID: ' || NEW.product_id || ' Quantity:' || NEW.quantity;
301 INSERT INTO LOGGER(operation, on_table, record_id, old_data, new_data, changed_at) VALUES(TYP_OPER, TG_TABLE_NAME, NEW.ID, v_OLD_DATA, v_NEW_DATA, CURRENT_DATE);
302 RETURN NEW;
303 ELSE
304 v_OLD_DATA := '';
305 v_NEW_DATA:= '';
306 INSERT INTO LOGGER(operation, on_table, record_id, old_data, new_data, changed_at) VALUES(TYP_OPER, TG_TABLE_NAME, NULL, v_OLD_DATA, v_NEW_DATA, CURRENT_DATE);
307 RETURN OLD;
308 END IF;
309END;
310$$ LANGUAGE plpgsql;
311
312
313CREATE TRIGGER add_to_orders_log_t
314AFTER
315--(BEFORE)
316--(INSTEAD OF)
317INSERT OR UPDATE OR DELETE
318ON orders_extended
319FOR EACH ROW
320EXECUTE PROCEDURE add_to_orders_logger();
321*/
322
323-- INSERT INTO orders_extended(orders_id, product_id, quantity) values(1, 1, 2);
324
325-- INSERT INTO orders_extended(orders_id, product_id, quantity) values(1, 3, 50);
326
327-- SELECT create_order(1,'LOL');
328-- SELECT add_to_order(2, 1, 1);
329
330-- SELECT showAvailableProducts();
331
332-- SELECT order_proceed(1);
333
334-- SELECT order_cancel(1);
335
336-- SELECT add_to_order(2, 1, 1);
337
338
339-- SELECT create_order(4, 'Test');
340
341-- SELECT add_to_order(4, 5, 20);
342
343-- SELECT create_order(1, 'Wiadomosc');
344
345-- SELECT add_to_order(1, 5, 5);