In-Reply-To: <781842.52422.qm@web31813.mail.mud.yahoo.com>
References: <781842.52422.qm@web31813.mail.mud.yahoo.com>
Mime-Version: 1.0 (Apple Message framework v752.3)
Content-Type: text/plain; charset=ISO-8859-1; delsp=yes; format=flowed
Message-Id: <5DD016CF-7FDE-489F-B15A-58B6935B4FBD@myemma.com>
Cc: SQL Postgresql List <pgsql-sql@postgresql.org>
Content-Transfer-Encoding: quoted-printable
From: Erik Jones <erik@myemma.com>
Subject: Re: Advice for generalizing trigger functions
Date: Thu, 27 Dec 2007 11:51:59 -0600
To: Richard Broersma Jr <rabroersma@yahoo.com>


On Dec 25, 2007, at 6:20 PM, Richard Broersma Jr wrote:

> I've created quite a few functions that log modifications to =20
> various history tables. (the history table has the same name as the =20=

> base table but is prefixed by the 'History.' schema.) The only =20
> 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 =3D now()
>            WHERE M.manager_id =3D 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, =20
> NEW.*);
>
>           RETURN NEW;
>
>      END IF;
>
>      RETURN OLD;
>
> END;
> $BODY$
> LANGUAGE 'plpgsql' VOLATILE SECURITY DEFINER;

TG_TABLE_NAME will have the name of the table the trigger was fired =20
on.  With that and using EXECUTE for your INSERT statements, you'll =20
probably be set.

Erik Jones

Software Developer | Emma=AE
erik@myemma.com
800.595.4401 or 615.292.5888
615.292.0777 (fax)

Emma helps organizations everywhere communicate & market in style.
Visit us online at http://www.myemma.com