I am very new to PostgreSQL.
I currently using the following code (from Use a Postgres trigger to record the JSON of only the modified fields) to log changes to tables
declare
json_new jsonb;
json_old jsonb;
begin
if tg_op = 'INSERT' then
json_new:= to_jsonb(new);
elsif tg_op = 'DELETE' then
json_old:= to_jsonb(old);
else
select jsonb_object_agg(new_key, new_value), jsonb_object_agg(old_key, old_value)
into json_new, json_old
from jsonb_each(to_jsonb(new)) as n(new_key, new_value)
join jsonb_each(to_jsonb(old)) as o(old_key, old_value)
on new_key = old_key and new_value <> old_value;
end if;
insert into logging.t_history(tabname, schemaname, operation, old_val, new_val)
values (tg_relname, tg_table_schema, tg_op, json_old, json_new);
return null;
end;
I'd like to be able to also record the unique 'refno' of the row that's been changed.
I have tried declaring the value, probably incorrect....
declare
json_new jsonb;
json_old jsonb;
refno varchar;
begin
if tg_op = 'INSERT' then
json_new:= to_jsonb(new);
elsif tg_op = 'DELETE' then
json_old:= to_jsonb(old);
else
select jsonb_object_agg(new_key, new_value), jsonb_object_agg(old_key, old_value)
into json_new, json_old
from jsonb_each(to_jsonb(new)) as n(new_key, new_value)
join jsonb_each(to_jsonb(old)) as o(old_key, old_value)
on new_key = old_key and new_value <> old_value;
end if;
insert into logging.t_history(tabname, schemaname, operation, old_val, new_val, refno)
values (tg_relname, tg_table_schema, tg_op, json_old, json_new, refno);
return null;
end;
However I get the following error:-
Could not commit changes to layer
Errors: ERROR: 1 attribute value change(s) not applied.
Provider errors: PostGIS error while changing attributes: ERROR: column "refno" of relation "t_history" does not exist LINE 1: ...ory(tabname, schemaname, operation, old_val, new_val, refno) ^ QUERY: insert into logging.t_history(tabname, schemaname, operation, old_val, new_val, refno) values (tg_relname, tg_table_schema, tg_op, json_old, json_new, refno) CONTEXT: PL/pgSQL function change_trigger2() line 17 at SQL statement
The changes are being made through PostGIS - first trigger works fine but second fails.
Any assistance would be really appreciated! Thank you in advance.
EDIT:-
I managed to declare the value of refno by using the old and new values and adding old_refno and new_refno columns to the logging table. However my preferred option would be a single refno column that held old and new depending on which one was present
declare
json_new jsonb;
json_old jsonb;
old_refno varchar := old.refno;
new_refno varchar := new.refno;
begin
if tg_op = 'INSERT' then
json_new:= to_jsonb(new);
elsif tg_op = 'DELETE' then
json_old:= to_jsonb(old);
else
select jsonb_object_agg(new_key, new_value), jsonb_object_agg(old_key, old_value)
into json_new, json_old
from jsonb_each(to_jsonb(new)) as n(new_key, new_value)
join jsonb_each(to_jsonb(old)) as o(old_key, old_value)
on new_key = old_key and new_value <> old_value;
end if;
insert into logging.t_history(tabname, schemaname, operation, old_val, new_val, old_refno, new_refno)
values (tg_relname, tg_table_schema, tg_op, json_old, json_new, old_refno, new_refno);
return null;
end;