From: | Erik Jones <erik(at)myemma(dot)com> |
---|---|
To: | Richard Broersma Jr <rabroersma(at)yahoo(dot)com> |
Cc: | SQL Postgresql List <pgsql-sql(at)postgresql(dot)org> |
Subject: | Re: Advice for generalizing trigger functions |
Date: | 2007-12-27 17:51:59 |
Message-ID: | 5DD016CF-7FDE-489F-B15A-58B6935B4FBD@myemma.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-sql |
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
From | Date | Subject | |
---|---|---|---|
Next Message | Richard Broersma Jr | 2007-12-27 18:03:13 | Re: Advice for generalizing trigger functions |
Previous Message | Pavel Stehule | 2007-12-27 04:55:55 | Re: how to use pgsql like mssql |