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(at)debis(dot)com (Jan Wieck) #