Thread: Mod_date update technique

I have a column named mod_date on a particular table. It has a default
value of now() which is the create date of the row. However, I want to
update that field with the current timestamp whenever its record is
updated.

I know how to do this using PHP by including the date() in the data I'm
using to update the record. However, I would like to do this on the
PostgreSQL side of the transaction. I can't figure out how to write a
trigger or other technique to do this.

Simply asked: What is the easiest way to automatically update the mod_date
field when the record is updated?

Gary

**************************************************************************
* Gary B. Hoffman, Computing Services Manager  e-mail: ghoffman@ucsd.edu *
* Graduate School of International Relations and Pacific Studies (IR/PS) *
* University of California, San Diego (UCSD)       voice: (858) 534-1989 *
* 9500 Gilman Dr.  MC 0519                           fax: (858) 534-3939 *
* La Jolla, CA 92093-0519 USA                 web: http://irps.ucsd.edu/ *
**************************************************************************

Gary Hoffman wrote:

>I have a column named mod_date on a particular table. It has a default
>value of now() which is the create date of the row. However, I want to
>update that field with the current timestamp whenever its record is
>updated.
>
>I know how to do this using PHP by including the date() in the data I'm
>using to update the record. However, I would like to do this on the
>PostgreSQL side of the transaction. I can't figure out how to write a
>trigger or other technique to do this.
>
>
A trigger is the correct way.

>Simply asked: What is the easiest way to automatically update the mod_date
>field when the record is updated?
>
>
Check out the PostgreSQL documentation it is quite comprehensive.
http://www.postgresql.org/docs/7.4/interactive/triggers.html

Regards
Justin

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