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'