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$ ; CREATE OR REPLACE FUNCTION update_row(table_name text, column_name text, column_value text, condition_column text, condition_value text) RETURNS void LANGUAGE plpgsql AS $function$ BEGIN EXECUTE format('UPDATE %I SET %I = %L WHERE %I = %L', table_name, column_name, column_value, condition_column, condition_value); END; $function$ ; CREATE OR REPLACE FUNCTION delete_row(table_name text, condition_column text, condition_value text) RETURNS void LANGUAGE plpgsql AS $function$ BEGIN EXECUTE format('DELETE FROM %I WHERE %I = %L', table_name, condition_column, condition_value); END; $function$ ; SELECT insert_client('Иван', 'Фролов', 'Николаевич'); SELECT update_row('clients', 'name', 'Никита', 'id', '1'); SELECT * FROM audit_log