Generalized trigger function

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');

Responses

Browse pgsql-novice by date

  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]