On Wed, 10 Jun 1998, Byron Nikolaidis wrote:
>
>
> Krasnow, Greg wrote:
>
> > I haven't looked at DATETIME stuff, but does Postgres not have something
> > similar to Oracle's SYSDATE? In Oracle you can set an Oracle DATE column to
> > have a default of SYSDATE. This way Oracle can fill in the column at the
> > time an insert is done.
> >
>
> Yes, you are right, and I noticed Jose' earlier mail about this on the 'sql'
> list.
>
> If you do:
>
> create table x (a timestamp DEFAULT CURRENT_TIMESTAMP, b varchar);
>
> It works AND it puts in the current time at INSERT of the new row. (I noticed
> if you use CURRENT_TIME instead, you get the time you created the table at, for
> every row, which is not very useful.)
>
> The only problem is that it doesn't change the value on an UPDATE!
>
> Any thoughts?
>
> Byron
>
Well, I think that it may be solved by creating a trigger, I've done this
and seem it works.
---------------------------------------------------------------------------
CREATE TABLE version_test (
username CHAR(10),
version TIMESTAMP
);
CREATE TRIGGER version
BEFORE INSERT OR UPDATE ON version_test
FOR EACH ROW
EXECUTE PROCEDURE version(version);
---------------------------------------------------------------------------
/*
* version.c
* $Modified: 9/6/98 by Jose' Soares Da Silva - inserito un campo timestamp.
*
* insert a value into a timestamp column in response to a trigger
* usage: version(data_time)
*/
#include "executor/spi.h" /* this is what you need to work with SPI */
#include "commands/trigger.h" /* -"- and triggers */
HeapTuple version(void);
HeapTuple version()
{
Trigger *trigger; /* to get trigger name */
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;
if (!CurrentTriggerData)
elog(ERROR, "version: i triggers non sono inizializati");
if (TRIGGER_FIRED_FOR_STATEMENT(CurrentTriggerData->tg_event))
elog(ERROR, "version: impossibile usare l'evento STATEMENT");
if (TRIGGER_FIRED_AFTER(CurrentTriggerData->tg_event))
elog(ERROR, "version: deve essere creata prima dell'evento");
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, "version: impossibile usare l'evento DELETE");
rel = CurrentTriggerData->tg_relation;
relname = SPI_getrelname(rel);
trigger = CurrentTriggerData->tg_trigger;
args = trigger->tgargs;
tupdesc = rel->rd_att;
CurrentTriggerData = NULL;
/* update the TIMESTAMP */
attnum = SPI_fnumber(tupdesc,args[1]);
newval = PointerGetDatum(now());
rettuple = SPI_modifytuple(rel, rettuple, 1, &attnum, &newval, NULL);
if (rettuple == NULL)
elog(ERROR, "version (%s): %d ritornato da SPI_modifytuple",
relname, SPI_result);
pfree(relname);
return (rettuple);
---------------------------------------------------------------------------
Ciao, Jose'