CREATE TABLE if not exists "HOOKAH"
(
hookah_id int NOT NULL,
hookah_nm varchar(20) NOT NULL,
hookah_price money NOT NULL,
hookah_desc text NOT NULL,
CONSTRAINT hookah_pk PRIMARY KEY (hookah_id),
CONSTRAINT chk_hookah CHECK (hookah_id > 0 and hookah_price::numeric::integer > 0)
);
CREATE TABLE if not exists "Costumer"
(
costumer_id int NOT NULL,
costumer_in timestamptz,
costumer_out timestamptz,
CONSTRAINT costumer_pk PRIMARY KEY (costumer_id)
);
CREATE TABLE if not exists "Office"
(
office_id int NOT NULL,
location varchar(50) NOT NULL,
phone_nb varchar(20) NOT NULL,
CONSTRAINT office_pk PRIMARY KEY (office_id),
CONSTRAINT chk_office CHECK (office_id > 0 and phone_nb not like '%[^0-9]%')
);
CREATE TABLE if not exists "Employer"
(
employer_id int NOT NULL,
office_id int NOT NULL,
employer_nm varchar(50) NOT NULL,
salary int NOT NULL,
phone_nb varchar(20) NOT NULL,
experience int NOT NULL,
CONSTRAINT employer_pk PRIMARY KEY (employer_id),
CONSTRAINT office_employer FOREIGN KEY (office_id) REFERENCES "Office" (office_id),
CONSTRAINT chk_employer CHECK (employer_id > 0 and phone_nb not like '%[^0-9]%' and salary > 10000 and experience >=0)
);
CREATE TABLE if not exists "Order"
(
order_id int NOT NULL,
office_id int NOT NULL,
costumer_id int NOT NULL,
employer_id int NOT NULL,
order_date date NOT NULL,
order_time time NOT NULL,
CONSTRAINT order_pk PRIMARY KEY (order_id),
CONSTRAINT order_costumer FOREIGN KEY (costumer_id) REFERENCES "Costumer" (costumer_id),
CONSTRAINT order_office FOREIGN KEY (office_id) REFERENCES "Office" (office_id),
CONSTRAINT order_employer FOREIGN KEY (employer_id) REFERENCES "Employer" (employer_id),
CONSTRAINT chk_order CHECK (order_id > 0)
);
INSERT INTO "Office" (office_id, location, phone_nb) VALUES (11, 'Ivanovskaya', '9254567889');
INSERT INTO "Office" (office_id, location, phone_nb) VALUES (12, 'Lozjevaya', '9457369864');
INSERT INTO "Office" (office_id, location, phone_nb) VALUES (13, 'Dirijabelnata', '9754329880');
INSERT INTO "Office" (office_id, location, phone_nb) VALUES (14, 'Lubimaya', '9257612847');
INSERT INTO "HOOKAH" (hookah_id, hookah_nm, hookah_price, hookah_desc) VALUES (1, 'Light', '1000', 'Usual');
INSERT INTO "HOOKAH" (hookah_id, hookah_nm, hookah_price, hookah_desc) VALUES (2, 'Mean', '1200', 'Usual');
INSERT INTO "HOOKAH" (hookah_id, hookah_nm, hookah_price, hookah_desc) VALUES (3, 'Hard', '1200', 'Difficult');
INSERT INTO "HOOKAH" (hookah_id, hookah_nm, hookah_price, hookah_desc) VALUES (4, 'Yagodniyi', '1000', 'Usual');
INSERT INTO "HOOKAH" (hookah_id, hookah_nm, hookah_price, hookah_desc) VALUES (5, 'Platina', '2000', 'Difficult');
INSERT INTO "HOOKAH" (hookah_id, hookah_nm, hookah_price, hookah_desc) VALUES (6, 'Mary', '1500', 'Difficult');
INSERT INTO "Costumer" (costumer_id) VALUES (1);
INSERT INTO "Costumer" (costumer_id) VALUES (2);
INSERT INTO "Costumer" (costumer_id) VALUES (3);
INSERT INTO "Costumer" (costumer_id) VALUES (4);
INSERT INTO "Costumer" (costumer_id) VALUES (5);
INSERT INTO "Employer" (employer_id, office_id, employer_nm, salary, phone_nb, experience) VALUES (1, 11, 'Andrey Kaznacheev', 18000, '9251285760', 3);
INSERT INTO "Employer" (employer_id, office_id, employer_nm, salary, phone_nb, experience) VALUES (2, 12, 'Evgeny Lubin', 35000, '9653452678', 2);
INSERT INTO "Employer" (employer_id, office_id, employer_nm, salary, phone_nb, experience) VALUES (3, 13, 'Vladislav Novikov', 17000, '9456789009', 0);
INSERT INTO "Employer" (employer_id, office_id, employer_nm, salary, phone_nb, experience) VALUES (4, 12, 'Anya Ivanova', 23000, '9051345267', 6);
INSERT INTO "Employer" (employer_id, office_id, employer_nm, salary, phone_nb, experience) VALUES (5, 14, 'Artur Eniseev', 100000, '9054789405', 8);
INSERT INTO "Employer" (employer_id, office_id, employer_nm, salary, phone_nb, experience) VALUES (6, 11, 'Yulia Zverskaya', 150000, '9654321962', 5);
INSERT INTO "Order" (order_id, office_id, costumer_id, employer_id, order_date, order_time) VALUES (1, 11, 2, 2, '2021-09-11', '21:21:21');
INSERT INTO "Order" (order_id, office_id, costumer_id, employer_id, order_date, order_time) VALUES (2, 13, 3, 4, '2021-09-11', '22:22:22');
INSERT INTO "Order" (order_id, office_id, costumer_id, employer_id, order_date, order_time) VALUES (3, 14, 4, 6, '2021-09-11', '23:23:23');
DELETE
FROM public."Order"
WHERE order_id = 5;
CREATE OR REPLACE function check_amount_order()
returns TRIGGER AS
$$
declare
orders_count integer;
BEGIN
SELECT count(*)
INTO orders_count
FROM public."Order"
WHERE "Order".order_id = new.order_id;
assert orders_count < 10, 'This customer has too many orders.';
return new;
end;
$$ language plpgsql;
drop TRIGGER if exists check_amount_trigger on public."Order";
CREATE TRIGGER check_amount_trigger
before INSERT
ON public."Order"
FOR each row
execute procedure check_amount_order();
-- Создадим заказ для эксперимента
INSERT INTO "Order" (order_id, office_id, costumer_id, employer_id, order_date, order_time) VALUES (5, 14, 4, 6, '2021-09-11', '23:23:23')
ON conflict do nothing;
-- Добавим туда 15 заказов за раз и убедимся, что запрос упал
INSERT INTO "Order" (order_id, office_id, costumer_id, employer_id, order_date, order_time)
(
SELECT 4, 10, 2, 1, '2022-05-10', '20:57:33'
FROM generate_series(1, 15)
);
SELECT count(*)
FROM public."Order"
WHERE order_id = 4;
Telegram: DB developers channel