Skip site navigation (1) Skip section navigation (2)

Peripheral Links

Header And Logo

PostgreSQL
| The world's most advanced open source database.

Site Navigation

Search archives
  Advanced Search

variable table names in trigger functions


  • From: Hugo <mlist(at)nieuwenhuys(dot)com>
  • To: pgsql-general(at)postgresql(dot)org
  • Subject: variable table names in trigger functions
  • Date: Thu, 31 Jan 2008 20:16:48 +0100
  • Message-id: <47A21EA0.4030401@nieuwenhuys.com> <text/plain>

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





Home | Main Index | Thread Index

Privacy Policy | About PostgreSQL
Copyright © 1996 – 2012 PostgreSQL Global Development Group