0

I have to use triggers to log deletion in the database. The table in which to log in is identical to the original plus the date and time field.

The operation is identical for all tables except the name of the table in which it is logged

begin
    insert into log.table_name
    select old.*, now();

    return old;
end

I want to give the table name as a parameter. But of course, that does not work

begin
    insert into TG_ARGV[0]
    select old.*, now();

    return old;
end

To use this, you need to go through a string

begin
    execute format('insert into %L '
        'select old.*, now()', TG_ARGV[0]);

    return old;
end

But this does not work because of the use of OLD.*

My question is, is there a way to give the table name as a parameter and to keep the call of the old. I'm clear that I can use CASE but I do not want to do it because the trigger function will become very big and it will be very unpleasant to maintain in the future.

mr mcwolf
  • 2,574
  • 2
  • 14
  • 27
  • You might want to look at several of the ready made audit triggers, e.g. http://8kb.co.uk/blog/2015/01/19/copying-pavel-stehules-simple-history-table-but-with-the-jsonb-type/ or https://wiki.postgresql.org/wiki/Audit_trigger_91plus –  Dec 02 '18 at 08:40
  • a common table does not work for me, unfortunately – mr mcwolf Dec 02 '18 at 08:46

0 Answers0