Обсуждение: Trigger question

I want to create a generic update trigger for all
tables in a DB using plpgsql. I'm thinking that having
one big trigger is more efficient than having a table
fire many triggers for each update.

To do this I need to know if an attribute exists in
the current table. For example, most of my tables
have an updated (datetime) field. I'd like to do
something like:

if (AttributeExists(new.updated))
   { new.updated = 'now'::datetime;}


I'd like to be able to use this trigger on
all tables - even if they don't have an
'updated' attribute.

Is there a way to do this?

-Pat Marchant

Pat Marchant <patmarchant@lvcablemodem.com> writes:
> if (AttributeExists(new.updated))
>    { new.updated = 'now'::datetime;}

I don't know any good way to do this in plpgsql.  It'd be moderately
straightforward in C, however, since you could look at the tuple
descriptor for the relation to see if there is a field named updated.

Dunno if you want to get involved with writing your triggers in C,
but if you want to try see
http://developer.postgresql.org/docs/postgres/trigger-manager.html

Also, there's an example that's pretty darn close to what you want to do
in contrib/spi/moddatetime.c.

            regards, tom lane

Thanks for the reply.

I was hoping to avoid using C. Am I correct about
my assumption that having, say, 30 IF..END IF blocks
in one trigger is more efficient (in terms of
execution speed and server load) than 5 update
triggers per table?

I'm just wondering if it's worth the effort to do it
in C or just use lots of plpgsql triggers.

Pat Marchant

On Thu, Dec 20, 2001 at 06:31:47PM -0500, Tom Lane wrote:
> Pat Marchant <patmarchant@lvcablemodem.com> writes:
> > if (AttributeExists(new.updated))
> >    { new.updated = 'now'::datetime;}
>
> I don't know any good way to do this in plpgsql.  It'd be moderately
> straightforward in C, however, since you could look at the tuple
> descriptor for the relation to see if there is a field named updated.
>