From: | Hugo <mlist(at)nieuwenhuys(dot)com> |
---|---|
To: | pgsql-general(at)postgresql(dot)org |
Subject: | variable table names in trigger functions |
Date: | 2008-01-31 19:16:48 |
Message-ID: | 47A21EA0.4030401@nieuwenhuys.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-general |
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
From | Date | Subject | |
---|---|---|---|
Next Message | Erik Jones | 2008-01-31 19:24:47 | Re: warm standby examples. |
Previous Message | Adam Rich | 2008-01-31 18:55:37 | Re: Oracle Analytical Functions |