SQLize Online / PHPize Online  /  SQLtest Online

CREATE TABLE audit_log ( id SERIAL PRIMARY KEY, user_name TEXT, action TEXT, old_value JSONB, new_value JSONB, action_time TIMESTAMP DEFAULT NOW() ); CREATE TABLE clients ( id SERIAL PRIMARY KEY, name TEXT, surname TEXT, patronymic TEXT ); CREATE OR REPLACE FUNCTION audit_trigger() RETURNS TRIGGER AS $$ BEGIN -- Запись информации в таблицу аудита INSERT INTO audit_log (user_name, action, old_value, new_value) VALUES (current_user, TG_OP, row_to_json(OLD), row_to_json(NEW)); RETURN NEW; END; $$ LANGUAGE plpgsql; CREATE TRIGGER audit_insert AFTER INSERT ON clients FOR EACH ROW EXECUTE FUNCTION audit_trigger(); CREATE TRIGGER audit_update AFTER UPDATE ON clients FOR EACH ROW EXECUTE FUNCTION audit_trigger(); CREATE TRIGGER audit_delete AFTER DELETE ON clients FOR EACH ROW EXECUTE FUNCTION audit_trigger(); CREATE OR REPLACE FUNCTION insert_client(new_name text, new_surname text, new_patronymic text) RETURNS bigint LANGUAGE plpgsql AS $function$ DECLARE new_id int8; BEGIN INSERT INTO clients (name, surname, patronymic) VALUES (new_name, new_surname, new_patronymic) RETURNING id INTO new_id; RETURN new_id; END; $function$ ; -- INSERT INTO clients(name, surname, patronymic) VALUES ('Иван', 'Фролов', 'Николаевич') SELECT insert_client('Иван', 'Фролов', 'Николаевич'); SELECT * FROM audit_log