From: | "Gang Cheng" <cg1101(at)gmail(dot)com> |
---|---|
To: | pgsql-novice(at)postgresql(dot)org |
Subject: | Generalized trigger function |
Date: | 2006-06-17 13:08:54 |
Message-ID: | ef844a360606170608v64012e59j300b2cac6c72474e@mail.gmail.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-novice |
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');
From | Date | Subject | |
---|---|---|---|
Next Message | Tom Lane | 2006-06-17 16:16:59 | Re: Generalized trigger function |
Previous Message | Tom Lane | 2006-06-17 00:21:21 | Re: libpq Describe Extension [WAS: Bytea and perl] |