Re: [ADMIN] 'C' function (spi?) for datetime UPDATE trigger - HOW?

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