forked from alexkitasov/Assessment
-
Notifications
You must be signed in to change notification settings - Fork 0
/
postgreSQL_commands.txt
113 lines (77 loc) · 2.38 KB
/
postgreSQL_commands.txt
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
-- Login to postgres via psql
psql -U postgres
GRANT ALL PRIVILEGES ON ALL TABLES IN SCHEMA replays TO admins;
-- Do a quick version check
SELECT version();
-- Test create Database
CREATE DATABASE Testfirst
ENCODING = 'UTF8'
LC_COLLATE = 'en_US.UTF-8'
LC_CTYPE = 'en_US.UTF-8'
TEMPLATE = template0;
-- Test drop database
DROP DATABASE if exists Testfirst;
-- Confirm database 'Testfirst' dropped
\l
-- Create Part Database
CREATE DATABASE Part
ENCODING = 'UTF8'
LC_COLLATE = 'en_US.UTF-8'
LC_CTYPE = 'en_US.UTF-8'
TEMPLATE = template0;
-- Create item table
CREATE TABLE item (
id INT NOT NULL PRIMARY KEY,
item_name VARCHAR(50) NOT NULL,
parent_item INT REFERENCES item(id),
cost INT NOT NULL,
req_date DATE NOT NULL
);
-- Confirm table creation
\dt
-- Insert database
INSERT INTO item(id, item_name, cost, req_date)
VALUES(1, 'Item1', 500, '2024-02-24');
INSERT INTO item(id, item_name, parent_item, cost, req_date)
VALUES(2, 'Sub1', 1, 200, '2024-02-10');
INSERT INTO item(id, item_name, parent_item, cost, req_date)
VALUES(3, 'Sub2', 1, 300, '2024-01-05');
INSERT INTO item(id, item_name, parent_item, cost, req_date)
VALUES(4, 'Sub3', 2, 300, '2024-01-02');
INSERT INTO item(id, item_name, parent_item, cost, req_date)
VALUES(5, 'Sub4', 2, 400, '2024-01-02');
INSERT INTO item(id, item_name, cost, req_date)
VALUES(6, 'Item2', 600, '2024-03-15');
INSERT INTO item(id, item_name, parent_item, cost, req_date)
VALUES(7, 'Sub1', 6, 200, '2024-02-25');
-- Make sure data is correct
SELECT * from item
SELECT COST FROM item WHERE item_name = 'Sub4';
-- Declare Total Cost function
CREATE OR REPLACE FUNCTION Get_Total_Cost(itm_name VARCHAR) RETURNS INT AS $$
DECLARE
total_cost INT := 0;
parentID INT;
BEGIN
SELECT id from item
INTO parentID
WHERE itm_name = item_name;
IF NOT FOUND then
RETURN NULL;
END IF;
WITH RECURSIVE item_hierarchy AS (
SELECT id, item_name, parent_item, cost
FROM item
WHERE id = parentID
UNION ALL
SELECT t.id, t.item_name, t.parent_item, t.cost
FROM item t
JOIN item_hierarchy h ON t.parent_item = h.id
)
SELECT INTO total_cost SUM(cost) FROM item_hierarchy;
RETURN total_cost;
END;
$$ LANGUAGE plpgsql;
-- Test function:
SELECT Get_Total_Cost('Item1') from item WHERE item_name = 'Item1';
SELECT Get_Total_Cost('Item2') from item WHERE item_name = 'Item2';