On 12/1/18 4:22 AM, PegoraroF10 wrote:
I´m trying to use transition tables for auditing purposes. create trigger MyTableAudit_UPD after update on MyTable referencing old table as Transition_old new table as Transition_new for each statement execute procedure AuditTable(); create or replace function AuditTable() returns trigger language plpgsql as $$ if (TG_OP = 'UPDATE') then insert into audittable(table_name, oid, audit_action, user_id, table_schema, values_old, values_new) select TG_TABLE_NAME, Transition_new.oid, TG_OP, CURRENT_USER, TG_TABLE_SCHEMA, row_to_json(Transition_old.*)::jsonb, row_to_json(Transition_new.*)::jsonb from Transition_new inner join Transition_old on Transition_new.OID = Transition_old.OID; elsif (TG_OP = 'DELETE') then insert into audittable(table_name, oid, audit_action, user_id, table_schema, values_old) select TG_TABLE_NAME, Transition_old.oid, TG_OP, CURRENT_USER, TG_TABLE_SCHEMA, row_to_json(Transition_old.*)::jsonb from Transition_old; end if; [42703] ERROR: column transition_new.oid does not exist Where: função PL/pgSQL audittable() linha 14 em comando SQL I would like to user OID value because we change our primary keys, sometimes, OID doesn´t. So, there is a way to get OID on transition tables ?
You will want to get away from using OID's as they are going away: https://git.postgresql.org/gitweb/?p=postgresql.git;a=commitdiff;h=578b229718e8f15fa779e20f086c4b6bb3776106
-- Sent from: http://www.postgresql-archive.org/PostgreSQL-general-f1843780.html
-- Adrian Klaver adrian.klaver@xxxxxxxxxxx
