SQLize Online / PHPize Online  /  SQLtest Online

A A A
Share      Blog   Popular
Copy Format Clear
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'); 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;
Stuck with a problem? Got Error? Ask ChatGPT!
Copy Clear