pg.ddx.io  pgsql-php@postgresql.org mailing list archive  
help / color / mirror / Atom feed
Mod_date update technique
3+ messages / 3 participants
[nested] [flat]

* Mod_date update technique
@ 2004-08-26 05:16  Gary Hoffman <ghoffman@ucsd.edu>
  0 siblings, 1 reply; 3+ messages in thread

From: Gary Hoffman @ 2004-08-26 05:16 UTC (permalink / raw)
  To: pgsql-php

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/ *
**************************************************************************







^ permalink  raw  reply  [nested|flat] 3+ messages in thread

* Re: Mod_date update technique
@ 2004-08-26 06:52  Justin Wyer <justin@isogo.co.za>
  parent: Gary Hoffman <ghoffman@ucsd.edu>
  0 siblings, 1 reply; 3+ messages in thread

From: Justin Wyer @ 2004-08-26 06:52 UTC (permalink / raw)
  To: Gary Hoffman <ghoffman@ucsd.edu>; pgsql-php

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





^ permalink  raw  reply  [nested|flat] 3+ messages in thread

* Re: Mod_date update technique
@ 2004-08-26 16:28  Gene Stevens <gene@triplenexus.org>
  parent: Justin Wyer <justin@isogo.co.za>
  0 siblings, 0 replies; 3+ messages in thread

From: Gene Stevens @ 2004-08-26 16:28 UTC (permalink / raw)
  To: pgsql-php; +Cc: Gary Hoffman <ghoffman@ucsd.edu>

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




^ permalink  raw  reply  [nested|flat] 3+ messages in thread


end of thread, other threads:[~2004-08-26 16:28 UTC | newest]

Thread overview: 3+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2004-08-26 05:16 Mod_date update technique Gary Hoffman <ghoffman@ucsd.edu>
2004-08-26 06:52 ` Justin Wyer <justin@isogo.co.za>
2004-08-26 16:28   ` Gene Stevens <gene@triplenexus.org>

This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox