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@triplenexus.org>
http://gene.triplenexus.org