Advice for generalizing trigger functions

From: Richard Broersma Jr <rabroersma(at)yahoo(dot)com>
To: SQL Postgresql List <pgsql-sql(at)postgresql(dot)org>
Subject: Advice for generalizing trigger functions
Date: 2007-12-26 00:20:53
Message-ID: 781842.52422.qm@web31813.mail.mud.yahoo.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-sql

I've created quite a few functions that log modifications to various history tables. (the history table has the same name as the base table but is prefixed by the 'History.' schema.) The only difference between functions I can find is the table name.

Is there any way to generalize these myriad of functions into one?

Below is a sample of a typical logging trigger function.

Regards,
Richard Broersma Jr.

CREATE OR REPLACE FUNCTION "project"."log_managers_ops"()
RETURNS trigger AS
$BODY$
BEGIN

IF TG_OP IN ( 'UPDATE', 'DELETE' ) THEN

UPDATE History.Managers AS M
SET endts = now()
WHERE M.manager_id = OLD.manager_id
AND now() BETWEEN M.startts AND M.endts;

end IF;


IF TG_OP IN ( 'UPDATE', 'INSERT' ) THEN

INSERT INTO History.Managers
VALUES ( now()::timestamptz, 'INFINITY'::timestamptz, NEW.*);

RETURN NEW;

END IF;

RETURN OLD;

END;
$BODY$
LANGUAGE 'plpgsql' VOLATILE SECURITY DEFINER;

Responses

Browse pgsql-sql by date

  From Date Subject
Next Message chester c young 2007-12-26 18:11:03 Re: Advice for generalizing trigger functions
Previous Message Rick Innis 2007-12-24 19:25:33 Re: Misnamed field in subquery does not cause error when field name exists in parent query