From: Erik Jones <erik@myemma.com>
To: Richard Broersma Jr <rabroersma@yahoo.com>
Cc: SQL Postgresql List <pgsql-sql@postgresql.org>
Subject: Re: Advice for generalizing trigger functions
Date: Thu, 27 Dec 2007 11:51:59 -0600
Message-ID: <5DD016CF-7FDE-489F-B15A-58B6935B4FBD@myemma.com> (raw)
In-Reply-To: <781842.52422.qm@web31813.mail.mud.yahoo.com>
References: <781842.52422.qm@web31813.mail.mud.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
> 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
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-sql@postgresql.org
Cc: erik@myemma.com, rabroersma@yahoo.com
Subject: Re: Advice for generalizing trigger functions
In-Reply-To: <5DD016CF-7FDE-489F-B15A-58B6935B4FBD@myemma.com>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox