Hi, everyone,
I'm trying to implement a generalized trigger function that can take as arguments the names of columns to be updated with username and timestamp of the INSERT/UPDATE event. Just as described in the last paragraph of section 35.1 (Chapter 35. Triggers. 35.1 Manuals 7.4.13)
http://www.postgresql.org/docs/7.4/static/triggers.html I want to implement the trigger as a row-level before trigger. But I don't know how to update NEW without making the column names hard-wired in the code. Can somebody give me an example? Thanks in advance!
CREATE TABLE sample_table (
id serial NOT NULL,
some_data text,
cr_user varchar(20), --created by userid
cr_time timestamp, --created timestamp
mo_user varchar(20), -- last updated by userid
mo_time timestamp
);
CREATE OR REPLACE FUNCTION generic_tg_func() RETURNS trigger AS '
DECLARE
BEGIN
--do something like
--NEW.TG_ARGV[0] := current_user;
--NEW.TG_ARGV[1] := ''now'';
RETURN NEW;
END;
' LANGUAGE 'plpgsql';
CREATE TRIGGER tg_sample_insert BEFORE INSERT
ON sample_table FOR EACH ROW
EXECUTE PROCEDURE tgfunc_update_timestamp ('cr_user', 'cr_time');
CREATE TRIGGER tg_sample_update BEFORE UPDATE
ON sample_table FOR EACH ROW
EXECUTE PROCEDURE tgfunc_update_timestamp ('cr_user', 'cr_time');
