Thread: modifying a timestamp in a C trigger

Hello,

I just finished a new C trigger that updated a "modified" column with
the current time upon an UPDATE event. It seems to work OK but I just
wanted to bounce this off you guys to check for some non-kosher stuff or
better way of doing it. Thanks in advance.

HeapTuple update_modified() {TupleDesc    tupdesc;HeapTuple    rettuple;bool isnull;TriggerData *trigdata =
CurrentTriggerData;
/* Get the current datetime. */Timestamp *tstamp = timestamp_in("now");Datum newdt = Float32GetDatum(tstamp);
CurrentTriggerData = NULL;
if (!trigdata)    elog(NOTICE, "bid_control.c: triggers are not initialized");
if (!TRIGGER_FIRED_BY_UPDATE(trigdata->tg_event))    elog(ERROR, "bid_control.c: trigger should only be called on
INSERT");
if (!TRIGGER_FIRED_BEFORE(trigdata->tg_event))    elog(ERROR, "bid_control.c: trigger should only be called BEFORE");
rettuple = trigdata->tg_trigtuple;tupdesc = trigdata->tg_relation->rd_att;
if ((i = SPI_connect()) < 0)    elog(NOTICE, "bid_control.c: SPI_connect returned %d", i);
i = SPI_fnumber(tupdesc, "modified");rettuple = SPI_modifytuple(        trigdata->tg_relation,        rettuple,
1,       &i,        &newdt,        NULL);
 
SPI_finish();return rettuple;
}

-- 
Louis-David Mitterrand - ldm@apartia.org - http://www.apartia.org

Lord, protect me from your followers.

At 04:47 PM 8/14/00 +0200, Louis-David Mitterrand wrote:
>Hello,
>
>I just finished a new C trigger that updated a "modified" column with
>the current time upon an UPDATE event. It seems to work OK but I just
>wanted to bounce this off you guys to check for some non-kosher stuff or
>better way of doing it. Thanks in advance.

This could easily done in PL/pgSQL.  Your C trigger will have to be modified
if the details of trigger or the function call protocol changes, while the
PL/pgSQL source will work forever without change.

And since the expense is in the "update" itself, I'd be surprised if you
could measure any speed difference between the two approaches.

Unless you're doing this to learn how to write C triggers for the heck
of it or to do stuff you can't do in PL/pgSQL, the PL/pgSQL approach is
much better.



- Don Baccus, Portland OR <dhogaza@pacifier.com> Nature photos, on-line guides, Pacific Northwest Rare Bird Alert
Serviceand other goodies at http://donb.photo.net.
 

On Mon, Aug 14, 2000 at 10:45:19AM -0700, Don Baccus wrote:
> At 04:47 PM 8/14/00 +0200, Louis-David Mitterrand wrote:
> >I just finished a new C trigger that updated a "modified" column with
> >the current time upon an UPDATE event. It seems to work OK but I just
> >wanted to bounce this off you guys to check for some non-kosher stuff or
> >better way of doing it. Thanks in advance.
> 
> This could easily done in PL/pgSQL.  Your C trigger will have to be modified
> if the details of trigger or the function call protocol changes, while the
> PL/pgSQL source will work forever without change.
> 
> And since the expense is in the "update" itself, I'd be surprised if you
> could measure any speed difference between the two approaches.
> 
> Unless you're doing this to learn how to write C triggers for the heck
> of it or to do stuff you can't do in PL/pgSQL, the PL/pgSQL approach is
> much better.

Yes, that's the main reason: being able to program triggers in C, to be
prepared for the moment when only C will cut it for certain features I
am thinking about. It's a kind of training as I don't have a programming
background.

PL/pgsql is very nice and quick, granted, but sometimes a bit hard to
debug: 'parser error near ""' messages sometimes occur and make you
wonder where in your code is the error.

Thanks for your input, cheers,

-- 
Louis-David Mitterrand - ldm@apartia.org - http://www.apartia.org

MACINTOSH == Most Applications Crash If Not The Operatings System Hangs