DomainKey-Signature: a=rsa-sha1; q=dns; c=nofws; s=s1024; d=yahoo.com;
	h=X-YMail-OSG:Received:X-Mailer:Date:From:Subject:To:MIME-Version:Content-Type:Message-ID;
	b=6sWMhEKjMQVEJHPmD0kuNsDklxG+t2eE/7Bk3iFhyNndNlKmw3WvWg5XBf8VtfcIkOJzJDusEJgrzkonDZcU/UVsitLV7DApWcGrjaMjvIznajAjJbMfXPZMJax3Wgj6o5+9Q1Dv4bOs5kjV9Qskc2I0dezo4BXMX4ncx6xjVfY=;
Date: Tue, 25 Dec 2007 16:20:53 -0800 (PST)
From: Richard Broersma Jr <rabroersma@yahoo.com>
Subject: Advice for generalizing trigger functions
To: SQL Postgresql List <pgsql-sql@postgresql.org>
MIME-Version: 1.0
Content-Type: text/plain; charset=us-ascii
Message-ID: <781842.52422.qm@web31813.mail.mud.yahoo.com>

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;