SQLize Online / PHPize Online  /  SQLtest Online

A A A
Share      Blog   Popular
Copy Format Clear
create extension if not exists hstore; create extension if not exists "uuid-ossp"; drop table if exists test_table cascade; create table test_table ( test_table_id uuid not null default public.uuid_generate_v4() constraint test_table_id_pk primary key ,name text ,code int ); drop table if exists test_table_log cascade; create table if not exists test_table_log( test_table_log_id uuid not null default public.uuid_generate_v4() constraint test_table_log_id_pk primary key ,h_operation_name text not null ,h_created_date timestamptz null default now() ,h_change_info json null ,test_table_id uuid null ,name text null ,code integer null ); drop function if exists test_table_trigger_create_log() cascade; create or replace function test_table_trigger_create_log() returns trigger language plpgsql as $$ declare _user_id uuid; _user_name text; _login_name text; _test_table_log_id uuid; _operation_type_id uuid; _message_log_id uuid; _change_info json; begin begin -- Список изменений, что конкретно поменялось with wChanges as ( select coalesce(old.test_table_id, new.test_table_id) as id ,hstore_to_json(hstore(old)- hstore(new)) as j_old -- список старых значений ,hstore_to_json(hstore(new)- hstore(old)) as j_new -- список новых значений where old is distinct from new ) ,wChangeList as ( select wdo.id as id ,o.key as column_name ,o.value as old_value ,n.value as new_value from wChanges wdo left join lateral (select * from json_each_text(wdo.j_old) j ) o on true left join lateral (select * from json_each_text(wdo.j_new) j ) n on n.key = o.key ) select json_agg( json_build_object( 'column', w.column_name ,'old', w.old_value ,'new', w.new_value ) ) into _change_info from wChangeList w; insert into test_table_log(h_operation_name, h_created_date, h_change_info,test_table_id,name,code) select tg_op ,now() ,_change_info ,old.test_table_id ,old.name ,old.code; exception when others then raise exception 'Error in test_table_trigger_create_log:%', SQLERRM; end; return null; end; $$; drop trigger if exists test_table_log_update on test_table; create trigger test_table_log_update after update on test_table for each row when (pg_trigger_depth() = 0) execute procedure test_table_trigger_create_log(); drop trigger if exists test_table_log_insert on test_table; create trigger test_table_log_insert after insert on test_table for each row when (pg_trigger_depth() = 0) execute procedure test_table_trigger_create_log(); drop trigger if exists test_table_log_delete on test_table; create trigger test_table_log_delete after delete on test_table for each row when (pg_trigger_depth() = 0) execute procedure test_table_trigger_create_log();
Stuck with a problem? Got Error? Ask ChatGPT!
Copy Clear