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.=20

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_dat=
e
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/ =
*
************************************************************ *************=
*





---------------------------(end of broadcast)---------------------------
TIP 1: subscribe and unsubscribe commands go to majordomo@postgresql.org

Re: Mod_date update technique

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



---------------------------(end of broadcast)---------------------------
TIP 5: Have you checked our extensive FAQ?

http://www.postgresql.org/docs/faqs/FAQ.html

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


---------------------------(end of broadcast)---------------------------
TIP 9: the planner will ignore your desire to choose an index scan if your
joining column's datatypes do not match