[GENERAL] variable table names in trigger functions

[Hugo]

Hi all,

I've written a simple trigger function to store the old row in another table to 
log the data:

CREATE FUNCTION logusers() RETURNS trigger AS $$
   BEGIN
        INSERT INTO log.users SELECT FROM data.users WHERE id=OLD.id;
        RETURN NEW;
   END;
$$ LANGUAGE plpgsql;

CREATE TRIGGER log BEFORE INSERT OR UPDATE ON data.users
   FOR EACH ROW EXECUTE PROCEDURE logusers();

This works but I whould like to make a function without the table names 
hardcoded in it so it's usable for other tables.
I tried this:

CREATE FUNCTION log() RETURNS trigger AS $$
   DECLARE
        log_table name  := 'log.'||TG_TABLE_NAME;
        data_table name := TG_TABLE_SCHEMA||'.'||TG_TABLE_NAME;
    BEGIN
        INSERT INTO log_table SELECT FROM data_table WHERE id=OLD.id;
        RETURN NEW;
   END;
$$ LANGUAGE plpgsql;

CREATE TRIGGER log BEFORE INSERT OR UPDATE ON data.users
   FOR EACH ROW EXECUTE PROCEDURE log();

CREATE TRIGGER log BEFORE INSERT OR UPDATE ON data.posts
   FOR EACH ROW EXECUTE PROCEDURE log();

But this doesn't work.
Any ideas on how to do this?

Thanks,
Hugo



---------------------------(end of broadcast)---------------------------
TIP 4: Have you searched our list archives?

              http://archives.postgresql.org/