Re: [SQL] Re: [INTERFACES] Re: M$-Access'97 and TIMESTAMPs

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'