Thread: modifying a timestamp in a C trigger

modifying a timestamp in a C trigger

From
Louis-David Mitterrand
Date:
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.


Re: modifying a timestamp in a C trigger

From
Don Baccus
Date:
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.
 


Re: modifying a timestamp in a C trigger

From
Louis-David Mitterrand
Date:
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