Re: Advice for generalizing trigger functions

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

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

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

Erik Jones

Software Developer | Emma®
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