pg.ddx.io pgsql-php@postgresql.org mailing list archive
help / color / mirror / Atom feedMod_date update technique
3+ messages / 3 participants
[nested] [flat]
* Mod_date update technique
@ 2004-08-26 05:16 Gary Hoffman <ghoffman@ucsd.edu>
2004-08-26 06:52 ` Re: Mod_date update technique Justin Wyer <justin@isogo.co.za>
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 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 ` Re: Mod_date update technique Gene Stevens <gene@triplenexus.org>
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 05:16 Mod_date update technique Gary Hoffman <ghoffman@ucsd.edu>
2004-08-26 06:52 ` Re: Mod_date update technique Justin Wyer <justin@isogo.co.za>
@ 2004-08-26 16:28 ` Gene Stevens <gene@triplenexus.org>
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