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(at)myemma(dot)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