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;
SET search_path TO public;
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();
insert into test_table(name, code)
values('test_1',1);
update test_table set name = 'new_name', code = 100;
delete from test_table where name ilike 'new_name';
table test_table_log;
Telegram: DB developers channel