1

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;
2
  • "column "refno" of relation "t_history" does not exist". Check if there is such a column in the table. Commented Jun 1, 2020 at 9:28
  • Many thanks for your reply - some how I missed adding the column. It works fine now however the refno value is blank. Commented Jun 1, 2020 at 9:39

0

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.