Transition Tables doesn´t have OID

From: PegoraroF10 <marcos(at)f10(dot)com(dot)br>
To: pgsql-general(at)postgresql(dot)org
Subject: Transition Tables doesn´t have OID
Date: 2018-12-01 12:22:18
Message-ID: 1543666938082-0.post@n3.nabble.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-general

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 ?

--
Sent from: http://www.postgresql-archive.org/PostgreSQL-general-f1843780.html

Responses

Browse pgsql-general by date

  From Date Subject
Next Message Ron 2018-12-01 12:37:05 Re: Transition Tables doesn´t have OID
Previous Message Ron 2018-12-01 11:24:45 Unused indexes