SQLize Online / PHPize Online  /  SQLtest Online

CREATE TABLE kassiri( id SERIAL PRIMARY KEY, fio_kassira VARCHAR(50) NOT NULL, age INT NOT NULL, job varchar(50) NOT NULL ); CREATE TABLE cheki( id SERIAL PRIMARY KEY, kas_id int, number_chek SERIAL, date_create TIMESTAMP(0) DEFAULT NOW(), FOREIGN KEY (kas_id) REFERENCES kassiri(id) ON DELETE CASCADE ); INSERT INTO kassiri(fio_kassira, age, job) VALUES ('Романов', 44, 'Кассир'), ('Грачёв', 19, 'Кассир'), ('Иванов', 53, 'Главный кассир'); INSERT INTO cheki(kas_id) SELECT id FROM kassiri ORDER BY RANDOM() LIMIT 1; INSERT INTO cheki(kas_id) SELECT id FROM kassiri ORDER BY RANDOM() LIMIT 1; CREATE OR REPLACE VIEW my_view AS SELECT ch.number_chek, ch.id, ch.kas_id, k.fio_kassira, k.age, k.job, ch.date_create FROM cheki ch JOIN kassiri k ON ch.kas_id = k.id ORDER BY ch.number_chek; SELECT*FROM my_view DROP VIEW my_view CREATE OR REPLACE PROCEDURE Update_my_view(_fio_kassira VARCHAR(50), _job VARCHAR(50)) LANGUAGE plpgsql AS $$ DECLARE _kas_id INT; BEGIN SELECT id INTO _kas_id FROM kassiri WHERE fio_kassira = _fio_kassira and job = _job LIMIT 1; UPDATE my_view SET kas_id = _kas_id ; IF (_kas_id IS NULL) THEN RAISE EXCEPTION 'Кассира не существует'; END IF; END; $$; CREATE OR REPLACE FUNCTION insert_row_view() RETURNS trigger AS $$ BEGIN INSERT INTO cheki(kas_id) VALUES (NEW.kas_id); RETURN NEW; END; $$ LANGUAGE 'plpgsql'; CREATE OR REPLACE TRIGGER insert_view INSTEAD OF UPDATE ON my_view FOR EACH ROW EXECUTE PROCEDURE insert_row_view(); CALL Update_my_view('Романов', 'Кассир');