Обсуждение: '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!

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) #