OK, so for those of you using postgresql that want a last modified / inserted time trigger, here it is. It only took me a couple hours to figure this out, and I want to thank Bruce Momkian for his excellent, if a bit dated, "Postgresql, Introduction and Concepts" book, which gave the examples I needed to figure out the rather obtuse plpgsql / trigger documentation.
First, you need the plpgsql language installed in your database to write the function in we'll be using. As a postgres superuser, do this:
createlang plgpsql dbname
Then, you'll need a table to play with, here's one:
-- TABLE --
CREATE TABLE dtest (
fluff text,
lm timestamp without time zone
);
-- FUNCTION --
CREATE FUNCTION modtime () RETURNS opaque AS '
BEGIN
new.lm :=''now'';
RETURN new;
END;
' LANGUAGE 'plpgsql';
--TRIGGER --
CREATE TRIGGER dtest
BEFORE UPDATE or INSERT ON dtest FOR EACH ROW EXECUTE PROCEDURE
modtime(lm);
What this does is that everytime you update or insert a row into the dtest table, the trigger we created on that table will "intercept" the row on the way into the table, and change the value of the lm (last modified) field to now(), the current transaction time.
