Postgres Pro
Downloads
Home   >   mailing lists

Thread: General trigger function

General trigger function

From
"GRIMOIS Eric"
Date:
Hi all

I read in the doc than a same function can be used for several triggers.
I tried to create a function who updates a field with a timestamp and
another field with the user name. This function is fired by triggers in
several tables.
It only works if updated fields have same names in all tables. I failed to
create a function where fields names are parameters of the function.

What's the way to do that ?
Thanks.

Eric GRIMOIS
Analyste programmeur
SEI - CPAM du Val d'Oise

Re: General trigger function

From
Jan Wieck
Date:
GRIMOIS Eric wrote:
> 
> Hi all
> 
> I read in the doc than a same function can be used for several triggers.
> I tried to create a function who updates a field with a timestamp and
> another field with the user name. This function is fired by triggers in
> several tables.
> It only works if updated fields have same names in all tables. I failed to
> create a function where fields names are parameters of the function.
   You could do that with the EXECUTE functionality. But that   causes *every* execution of these statemets not beeing
cacheable, so it'll have an impact on performance. 
 


Jan

> 
> What's the way to do that ?
> Thanks.
> 
> Eric GRIMOIS
> Analyste programmeur
> SEI - CPAM du Val d'Oise
> 
> ---------------------------(end of broadcast)---------------------------
> TIP 1: subscribe and unsubscribe commands go to majordomo@postgresql.org

-- 

#======================================================================#
# It's easier to get forgiveness for being wrong than for being right. #
# Let's break this rule - forgive me.                                  #
#================================================== JanWieck@Yahoo.com #

Re: General trigger function

From
Richard Huxton
Date:
On Wednesday 12 Jun 2002 9:32 pm, Jan Wieck wrote:
> GRIMOIS Eric wrote:
> > Hi all
> >
> > I read in the doc than a same function can be used for several triggers.
> > I tried to create a function who updates a field with a timestamp and
> > another field with the user name. This function is fired by triggers in
> > several tables.
> > It only works if updated fields have same names in all tables. I failed
> > to create a function where fields names are parameters of the function.
>
>     You could do that with the EXECUTE functionality. But that
>     causes *every* execution of these statemets not beeing
>     cacheable, so it'll have an impact on performance.

Or look in the cookbook linked from techdocs.postgresql.org - there's an
example in there of a "trigger maker" which builds last-changed tracking
triggers and functions. You do something like:

select lastchg_addto('TABLE-NAME','TIMESTAMP-COLUMN-NAME')

And it creates the relevant function and trigger code then EXECUTEs it.
Shouldn't take much to alter it for your purposes

- Richard Huxton