Re: Transition Tables doesn´t have OID

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@aklaver.com