Thread: 'C' function (spi?) for datetime UPDATE trigger - HOW?
Hi All
I sent this in a few days ago, but got no reply, so now here it is again:)
Need: Update a datetime field to the current time upon a record being
UPDATE'd. (To keep track of modification times)
This seems pertty basic, has any one already done this?
I am very open to any better approaches/ideas.
How I tryed to do it: (see comments below)
Starting with /usr/src/pgsql/contrib/spi/insert_username.c
I turned it into dtstamp.c as follows:
/*
* dtstamp.c (DateTimeSTAMP.c) was insert_username.c
* $Modified: Thu Oct 16 08:13:42 1997 by brook $
* $Modified: Sun Aug 16 1998 by Terry Mackintosh $
*
* update a datetime colum in response to a trigger
* usage: dtstamp(column_name)
*/
#include "executor/spi.h" /* this is what you need to work with SPI */
#include "commands/trigger.h" /* -"- and triggers */
#include <time.h>
/* #include "miscadmin.h" /* Prob. not needed? for GetPgUserName() */
HeapTuple dtstamp(void);
HeapTuple dtstamp(void)
{
Trigger *trigger; /* to get trigger name */
int nargs; /* # of arguments */
/* Datum newval; /* new value of column */
char **args; /* arguments */
char *relname; /* triggered relation name */
Relation rel; /* triggered relation */
HeapTuple rettuple = NULL;
TupleDesc tupdesc; /* tuple description */
int attnum;
/* New, create a datetime string as m/d/y h:m */
time_t runningtime; /* time in seconds */
struct tm *tptr; /* a time structure */
char datetime[20]; /* will have the finished p. */
time( &runningtime );
tptr = localtime(&runningtime);
sprintf( datetime, "%d/%d/%d %d:%d", tptr->tm_mon, tptr->tm_mday, tptr->tm_year + 1900, tptr->tm_hour, tptr->tm_min
);
/* End New for now, more at the bottom */
/* sanity checks from dtstamp.c */
if (!CurrentTriggerData)
elog(ERROR, "dtstamp: triggers are not initialized");
if (TRIGGER_FIRED_FOR_STATEMENT(CurrentTriggerData->tg_event))
elog(ERROR, "dtstamp: can't process STATEMENT events");
if (TRIGGER_FIRED_AFTER(CurrentTriggerData->tg_event))
elog(ERROR, "dtstamp: must be fired before event");
if (TRIGGER_FIRED_BY_INSERT(CurrentTriggerData->tg_event))
rettuple = CurrentTriggerData->tg_trigtuple;
else if (TRIGGER_FIRED_BY_UPDATE(CurrentTriggerData->tg_event))
rettuple = CurrentTriggerData->tg_newtuple;
else
elog(ERROR, "dtstamp: can't process DELETE events");
rel = CurrentTriggerData->tg_relation;
relname = SPI_getrelname(rel);
trigger = CurrentTriggerData->tg_trigger;
nargs = trigger->tgnargs;
if (nargs != 1)
elog(ERROR, "dtstamp (%s): one argument was expected", relname);
args = trigger->tgargs;
tupdesc = rel->rd_att;
CurrentTriggerData = NULL;
attnum = SPI_fnumber(tupdesc, args[0]);
if (attnum < 0)
elog(ERROR, "dtstamp (%s): there is no attribute %s", relname, args[0]);
/* More NEW stuff
if (SPI_gettypeid(tupdesc, attnum) != DATETIMEOID)
The problem here is, what should this -----^^^^^^^^^^^ be for a
datetime field?
elog(ERROR, "dtstamp (%s): attribute %s must be of DATETIME type.", relname, args[0] );
*/
/* construct new tuple */
/* The next problem is, what is the 'Datum' type?? can it some how be passed
a datetime??
*/
rettuple = SPI_modifytuple(rel, rettuple, 1, &attnum, (Datum *) &datetime, NULL);
if (rettuple == NULL)
elog(ERROR, "dtstamp (%s): %d returned by SPI_modifytuple", relname, SPI_result);
pfree(relname);
return (rettuple);
}
Any (better:) ideas?
Thanks, and have a great day
Terry Mackintosh <terry@terrym.com> http://www.terrym.com
sysadmin/owner Please! No MIME encoded or HTML mail, unless needed.
Proudly powered by R H Linux 4.2, Apache 1.3, PHP 3, PostgreSQL 6.3
-------------------------------------------------------------------
Success Is A Choice ... book by Rick Patino, get it, read it!
Re: [ADMIN] 'C' function (spi?) for datetime UPDATE trigger - HOW?
From
jwieck@debis.com (Jan Wieck)
Date:
Hi Terry,
>
> Hi All
>
> I sent this in a few days ago, but got no reply, so now here it is again:)
>
> Need: Update a datetime field to the current time upon a record being
> UPDATE'd. (To keep track of modification times)
>
> This seems pertty basic, has any one already done this?
> I am very open to any better approaches/ideas.
>
> How I tryed to do it: (see comments below)
> Starting with /usr/src/pgsql/contrib/spi/insert_username.c
> I turned it into dtstamp.c as follows:
>
> [...]
>
> Any (better:) ideas?
>
Two ideas :-)
I don't know if PL/pgSQL from the current 6.4 CVS will
compile and run with the 6.3 you're actually running. But I
think 6.3 already had loadable PL support (don't remember
when we enabled that in the function manager). And even if,
it will not be a generic solution for a table independent
trigger where you can specify the column name as trigger
argument.
Anyway, if you succeed in installing PL/pgSQL you can setup
an individual trigger procedure for one table in the
following way:
CREATE TABLE t1 (a int4, b text, mtime datetime);
CREATE FUNCTION t1_stamp () RETURNS opaque AS '
BEGIN
new.mtime := ''now'';
RETURN new;
END;
' LANGUAGE 'plpgsql';
CREATE TRIGGER t1_stamp BEFORE INSERT OR UPDATE ON t1
FOR EACH ROW EXECUTE PROCEDURE t1_stamp();
Second idea: Again I don't know if it will compile and run
with your current 6.3 installation. But PL/Tcl has the power
to create such a generic trigger procedure that can be fired
for different tables and the column to set specified in an
arg. Example:
CREATE TABLE t1 (a int4, b text, mtime datetime);
CREATE TABLE t2 (a int4, b text, last_update datetime);
CREATE FUNCTION dtstamp () RETURNS opaque AS '
set NEW($1) "now"
return [array get NEW]
' LANGUAGE 'pltcl';
CREATE TRIGGER t1_stamp BEFORE INSERT OR UPDATE ON t1
FOR EACH ROW EXECUTE PROCEDURE dtstamp('mtime');
CREATE TRIGGER t2_stamp BEFORE INSERT OR UPDATE ON t2
FOR EACH ROW EXECUTE PROCEDURE dtstamp('last_update');
You would need a shared library version of Tcl 8.0 installed
on your system to use this method.
I sent down a patch today that fixes a bug in PL/Tcl in the
hackers list. Apply that one on a fresh CVS tree before
picking out the pl subdirectory.
I could send you the latest versions if you can't cvsup.
Jan
--
#======================================================================#
# It's easier to get forgiveness for being wrong than for being right. #
# Let's break this rule - forgive me. #
#======================================== jwieck@debis.com (Jan Wieck) #