use a variable name for an insert in a trigger for an audit

[Raimon Fernandez <coder@xxxxxxxxx>]

Hello,

I have to audit all the changes for all rows of one database.

I have a trigger that executes BEFORE any update or delete, and simply copy the row (INSERT INTO) into the replicated table.

For example, every table has the same name plus '_audit' at the end and belongs to the schema audit:

table public.persons => audit.persons_audit

I don't want to create specific triggers/functions for every table, so I want to modify the table_name in the INSERT INTO, using the TG_TABLE_NAME, but I can't make it working.

Also I can't see a working solution in the archive, and some examples are quite messy to do, so maybe I have to rethink how I'm doing thinks or just create a specific trigger for each table.

Here is my function, and I'm only testing now the INSERT:

...
DECLARE
 tableRemote varchar;
BEGIN

IF TG_TABLE_NAME = 'assentaments' THEN
 tableRemote:='audit.'||TG_TABLE_NAME||'_audit';
END IF;

        --
        -- Create a row in table_audit to reflect the operation performed on emp,
        -- make use of the special variable TG_OP to work out the operation.
        --
	
		IF (TG_OP = 'DELETE') THEN
            EXECUTE 'INSERT INTO audit.assentaments_audit SELECT CURRVAL(''audit_id_seq''),5, OLD.*';
            RETURN OLD;
        ELSIF (TG_OP = 'UPDATE') THEN
            INSERT INTO tableRemote  SELECT CURRVAL('audit.audit_id_seq'),3,OLD.*;
            RETURN OLD;
        END IF;
        RETURN NULL; -- result is ignored since this is an AFTER trigger
    END;
...

thanks,

regards,




-- 
Sent via pgsql-general mailing list (pgsql-general@xxxxxxxxxxxxxx)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general