Re: Mod_date update technique

On Thu, 2004-08-26 at 00:52, Justin Wyer wrote:
> A trigger is the correct way.
>
> Check out the PostgreSQL documentation it is quite comprehensive.
> http://www.postgresql.org/docs/7.4/interactive/triggers.html

Justin is right.

What you'll need to do is create a function that modifies that mod_date
column and the create a trigger to call it whenever that record is
updated.

Here's an example that I think might work for you:

CREATE FUNCTION my_date_modified() RETURNS trigger AS '
BEGIN
NEW.mod_date := ''now'';
RETURN NEW;
END;
' LANGUAGE plpgsql;

CREATE TRIGGER my_date_mod BEFORE INSERT OR UPDATE ON table
FOR EACH ROW EXECUTE PROCEDURE my_date_modified();

For this example, you'd need to have the plpgsql language installed on
your database if it isn't already:

shell> createlang plgsql dbname

--
Gene Stevens <gene(at)triplenexus(dot)org>
http://gene.triplenexus.org