Обсуждение: Generalized trigger function
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');
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');
"Gang Cheng" <cg1101@gmail.com> writes:
> 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.
This is not possible in plpgsql, primarily because it's a strongly-typed
language and wants to know the datatypes of everything in advance.
You could do it in C for sure, and probably in some of the other PLs
that don't care about datatypes because they convert everything to
strings anyway. If this trigger is something you intend to apply to all
your tables, for performance reasons you might want to bite the bullet
and do it in C. There are some examples that do nearly this in
contrib/spi/.
regards, tom lane