pg.ddx.io pgsql-sql@postgresql.org mailing list archive
help / color / mirror / Atom feedUpdate timestamp on update
9+ messages / 7 participants
[nested] [flat]
* Update timestamp on update
@ 2005-10-13 01:12 Jeff Williams <jeffw@globaldial.com>
2005-10-13 01:44 ` Re: Update timestamp on update Tom Lane <tgl@sss.pgh.pa.us>
0 siblings, 1 reply; 9+ messages in thread
From: Jeff Williams @ 2005-10-13 01:12 UTC (permalink / raw)
To: pgsql-sql
I have a table like:
CREATE TABLE products (
id int,
status int,
last_status_change timestamp DEFAULT now()
);
What I would like is that whenever the status is changed the
last_status_change timestamp is updated to the current time. I have had
a look at the rules and what I want would be similar to:
CREATE RULE last_status_change AS ON UPDATE
TO products WHERE NEW.status <> OLD.status
DO UPDATE products SET last_status_change = now() WHERE id = OLD.id;
Except of course that the above is recursive and doesn't work.
How can I do this?
Jeff
^ permalink raw reply [nested|flat] 9+ messages in thread
* Re: Update timestamp on update
2005-10-13 01:12 Update timestamp on update Jeff Williams <jeffw@globaldial.com>
@ 2005-10-13 01:44 ` Tom Lane <tgl@sss.pgh.pa.us>
2005-10-13 02:42 ` Re: Update timestamp on update Jeff Williams <jeffw@globaldial.com>
0 siblings, 1 reply; 9+ messages in thread
From: Tom Lane @ 2005-10-13 01:44 UTC (permalink / raw)
To: Jeff Williams <jeffw@globaldial.com>; +Cc: pgsql-sql
Jeff Williams <jeffw@globaldial.com> writes:
> last_status_change timestamp DEFAULT now()
> What I would like is that whenever the status is changed the
> last_status_change timestamp is updated to the current time.
For this you use an ON UPDATE trigger; rules are not a good way to solve
it. See the documentation about triggers. The first example on this
page does it along with a few other things:
http://developer.postgresql.org/docs/postgres/plpgsql-trigger.html
regards, tom lane
^ permalink raw reply [nested|flat] 9+ messages in thread
* Re: Update timestamp on update
2005-10-13 01:12 Update timestamp on update Jeff Williams <jeffw@globaldial.com>
2005-10-13 01:44 ` Re: Update timestamp on update Tom Lane <tgl@sss.pgh.pa.us>
@ 2005-10-13 02:42 ` Jeff Williams <jeffw@globaldial.com>
2005-10-13 02:52 ` Re: [SQL] Update timestamp on update Tom Lane <tgl@sss.pgh.pa.us>
0 siblings, 1 reply; 9+ messages in thread
From: Jeff Williams @ 2005-10-13 02:42 UTC (permalink / raw)
To: Tom Lane <tgl@sss.pgh.pa.us>; +Cc: pgsql-sql
Tom Lane wrote:
>Jeff Williams <jeffw@globaldial.com> writes:
>
>
>> last_status_change timestamp DEFAULT now()
>>
>>
>
>
>
>>What I would like is that whenever the status is changed the
>>last_status_change timestamp is updated to the current time.
>>
>>
>
>For this you use an ON UPDATE trigger; rules are not a good way to solve
>it. See the documentation about triggers. The first example on this
>page does it along with a few other things:
>http://developer.postgresql.org/docs/postgres/plpgsql-trigger.html
>
>
Thanks. Triggers was my first thought, but chapter 35 on Triggers didn't
really indicate a way I could do this easily and scared me with a lot of
c code. Maybe it is a good idea to present some of the more common
things you would want to do with triggers in the triggers chapter?
Jeff
^ permalink raw reply [nested|flat] 9+ messages in thread
* Re: [SQL] Update timestamp on update
2005-10-13 01:12 Update timestamp on update Jeff Williams <jeffw@globaldial.com>
2005-10-13 01:44 ` Re: Update timestamp on update Tom Lane <tgl@sss.pgh.pa.us>
2005-10-13 02:42 ` Re: Update timestamp on update Jeff Williams <jeffw@globaldial.com>
@ 2005-10-13 02:52 ` Tom Lane <tgl@sss.pgh.pa.us>
2005-10-13 03:07 ` Re: Update timestamp on update Michael Glaesemann <grzm@myrealbox.com>
2005-10-13 14:22 ` Re: Update timestamp on update Andrew Sullivan <ajs@crankycanuck.ca>
2005-10-13 17:20 ` Re: [SQL] Update timestamp on update Jim C. Nasby <jnasby@pervasive.com>
0 siblings, 3 replies; 9+ messages in thread
From: Tom Lane @ 2005-10-13 02:52 UTC (permalink / raw)
To: Jeff Williams <jeffw@globaldial.com>; +Cc: pgsql-sql; pgsql-docs@postgresql.org
Jeff Williams <jeffw@globaldial.com> writes:
> Thanks. Triggers was my first thought, but chapter 35 on Triggers didn't
> really indicate a way I could do this easily and scared me with a lot of
> c code.
Yeah. This is a documentation issue that's bothered me for awhile.
The problem is that we treat the PL languages as add-ons and therefore
the documentation of the "core" system shouldn't rely on them ... but
that leaves us presenting C-code triggers as the only examples in
chapter 35. There is a paragraph in there suggesting you go look at
the PL languages first, but obviously it's not getting the job done.
Anybody have a better idea?
regards, tom lane
^ permalink raw reply [nested|flat] 9+ messages in thread
* Re: Update timestamp on update
2005-10-13 01:12 Update timestamp on update Jeff Williams <jeffw@globaldial.com>
2005-10-13 01:44 ` Re: Update timestamp on update Tom Lane <tgl@sss.pgh.pa.us>
2005-10-13 02:42 ` Re: Update timestamp on update Jeff Williams <jeffw@globaldial.com>
2005-10-13 02:52 ` Re: [SQL] Update timestamp on update Tom Lane <tgl@sss.pgh.pa.us>
@ 2005-10-13 03:07 ` Michael Glaesemann <grzm@myrealbox.com>
2 siblings, 0 replies; 9+ messages in thread
From: Michael Glaesemann @ 2005-10-13 03:07 UTC (permalink / raw)
To: Tom Lane <tgl@sss.pgh.pa.us>; +Cc: pgsql-sql; Jeff Williams <jeffw@globaldial.com>
On Oct 13, 2005, at 11:52 , Tom Lane wrote:
> Jeff Williams <jeffw@globaldial.com> writes:
>
>> Thanks. Triggers was my first thought, but chapter 35 on Triggers
>> didn't
>> really indicate a way I could do this easily and scared me with a
>> lot of
>> c code.
>>
>
> Yeah. This is a documentation issue that's bothered me for awhile.
> The problem is that we treat the PL languages as add-ons and therefore
> the documentation of the "core" system shouldn't rely on them ... but
> that leaves us presenting C-code triggers as the only examples in
> chapter 35. There is a paragraph in there suggesting you go look at
> the PL languages first, but obviously it's not getting the job done.
I think examples in PL/pgSQL would perhaps be most appropriate, as
it's a PostgreSQL-specific language, arguably easy to read, and a PL
that doesn't rely on any external dependencies, so it should be
relatively easy for people to install it if they want. (IIRC, there's
even been discussion in the past of whether or not PL/pgSQL should be
installed by default.)
Michael Glaesemann
grzm myrealbox com
^ permalink raw reply [nested|flat] 9+ messages in thread
* Re: Update timestamp on update
2005-10-13 01:12 Update timestamp on update Jeff Williams <jeffw@globaldial.com>
2005-10-13 01:44 ` Re: Update timestamp on update Tom Lane <tgl@sss.pgh.pa.us>
2005-10-13 02:42 ` Re: Update timestamp on update Jeff Williams <jeffw@globaldial.com>
2005-10-13 02:52 ` Re: [SQL] Update timestamp on update Tom Lane <tgl@sss.pgh.pa.us>
@ 2005-10-13 14:22 ` Andrew Sullivan <ajs@crankycanuck.ca>
2 siblings, 0 replies; 9+ messages in thread
From: Andrew Sullivan @ 2005-10-13 14:22 UTC (permalink / raw)
To: pgsql-sql
On Wed, Oct 12, 2005 at 10:52:04PM -0400, Tom Lane wrote:
> the documentation of the "core" system shouldn't rely on them ... but
> that leaves us presenting C-code triggers as the only examples in
> chapter 35. There is a paragraph in there suggesting you go look at
> the PL languages first, but obviously it's not getting the job done.
>
> Anybody have a better idea?
It could just be made a little friendlier, I think. At the beginning
of the trigger chapter is this:
--snip--
This chapter describes how to write trigger functions. Trigger
functions can be written in C or in some of the available procedural
languages. It is not currently possible to write a SQL-language
trigger function.
--snip--
We could just add a little note by way of modification. Here's a
(somewhat verbose, I fear) suggestion:
--snip--
This chapter describes how to write trigger functions. Trigger
functions can be written in C or in some of the available procedural
languages. This chapter deals only with functions that are written in
C. If you are unfamiliar with C, you may want also to look at the
chapters on procedural languages, because there are some examples
there that may be easier for you to understand. To use a procedural
language for a trigger, you will need to install that language; see
the relevant chapter for instructions on how to do so. It is not
currently possible to write a SQL-language trigger function.
--snip--
A
--
Andrew Sullivan | ajs@crankycanuck.ca
The plural of anecdote is not data.
--Roger Brinner
^ permalink raw reply [nested|flat] 9+ messages in thread
* Re: [SQL] Update timestamp on update
2005-10-13 01:12 Update timestamp on update Jeff Williams <jeffw@globaldial.com>
2005-10-13 01:44 ` Re: Update timestamp on update Tom Lane <tgl@sss.pgh.pa.us>
2005-10-13 02:42 ` Re: Update timestamp on update Jeff Williams <jeffw@globaldial.com>
2005-10-13 02:52 ` Re: [SQL] Update timestamp on update Tom Lane <tgl@sss.pgh.pa.us>
@ 2005-10-13 17:20 ` Jim C. Nasby <jnasby@pervasive.com>
2005-10-13 17:45 ` Re: [DOCS] Update timestamp on update Mike Diehl <jdiehl@sandia.gov>
2 siblings, 1 reply; 9+ messages in thread
From: Jim C. Nasby @ 2005-10-13 17:20 UTC (permalink / raw)
To: Tom Lane <tgl@sss.pgh.pa.us>; +Cc: Jeff Williams <jeffw@globaldial.com>; pgsql-sql; pgsql-docs@postgresql.org
On Wed, Oct 12, 2005 at 10:52:04PM -0400, Tom Lane wrote:
> Jeff Williams <jeffw@globaldial.com> writes:
> > Thanks. Triggers was my first thought, but chapter 35 on Triggers didn't
> > really indicate a way I could do this easily and scared me with a lot of
> > c code.
>
> Yeah. This is a documentation issue that's bothered me for awhile.
> The problem is that we treat the PL languages as add-ons and therefore
> the documentation of the "core" system shouldn't rely on them ... but
> that leaves us presenting C-code triggers as the only examples in
> chapter 35. There is a paragraph in there suggesting you go look at
> the PL languages first, but obviously it's not getting the job done.
Chapter 35 is plpgsql.. do you mean chapter 32.4?
> Anybody have a better idea?
What about a See Also section ala man pages that links to trigger info
for other languages?
--
Jim C. Nasby, Sr. Engineering Consultant jnasby@pervasive.com
Pervasive Software http://pervasive.com work: 512-231-6117
vcard: http://jim.nasby.net/pervasive.vcf cell: 512-569-9461
^ permalink raw reply [nested|flat] 9+ messages in thread
* Re: [DOCS] Update timestamp on update
2005-10-13 01:12 Update timestamp on update Jeff Williams <jeffw@globaldial.com>
2005-10-13 01:44 ` Re: Update timestamp on update Tom Lane <tgl@sss.pgh.pa.us>
2005-10-13 02:42 ` Re: Update timestamp on update Jeff Williams <jeffw@globaldial.com>
2005-10-13 02:52 ` Re: [SQL] Update timestamp on update Tom Lane <tgl@sss.pgh.pa.us>
2005-10-13 17:20 ` Re: [SQL] Update timestamp on update Jim C. Nasby <jnasby@pervasive.com>
@ 2005-10-13 17:45 ` Mike Diehl <jdiehl@sandia.gov>
0 siblings, 0 replies; 9+ messages in thread
From: Mike Diehl @ 2005-10-13 17:45 UTC (permalink / raw)
To: pgsql-sql
Is a working example something that people would like to see? Or is this
considered a good use of research time?
On Thursday 13 October 2005 11:20 am, Jim C. Nasby wrote:
> On Wed, Oct 12, 2005 at 10:52:04PM -0400, Tom Lane wrote:
> > Jeff Williams <jeffw@globaldial.com> writes:
> > > Thanks. Triggers was my first thought, but chapter 35 on Triggers
> > > didn't really indicate a way I could do this easily and scared me with
> > > a lot of c code.
> >
> > Yeah. This is a documentation issue that's bothered me for awhile.
> > The problem is that we treat the PL languages as add-ons and therefore
> > the documentation of the "core" system shouldn't rely on them ... but
> > that leaves us presenting C-code triggers as the only examples in
> > chapter 35. There is a paragraph in there suggesting you go look at
> > the PL languages first, but obviously it's not getting the job done.
>
> Chapter 35 is plpgsql.. do you mean chapter 32.4?
>
> > Anybody have a better idea?
>
> What about a See Also section ala man pages that links to trigger info
> for other languages?
--
Mike Diehl,
Network Monitoring Tool Devl.
SAIC at Sandia National Laboratories.
(505) 284-3137
^ permalink raw reply [nested|flat] 9+ messages in thread
* Re: Update timestamp on update
@ 2005-10-13 02:56 Stewart Ben (RBAU/EQS4) * <Ben.Stewart@au.bosch.com>
0 siblings, 0 replies; 9+ messages in thread
From: Stewart Ben (RBAU/EQS4) * @ 2005-10-13 02:56 UTC (permalink / raw)
To: pgsql-sql
> The problem is that we treat the PL languages as add-ons and therefore
> the documentation of the "core" system shouldn't rely on them ... but
> that leaves us presenting C-code triggers as the only examples in
> chapter 35. There is a paragraph in there suggesting you go look at
> the PL languages first, but obviously it's not getting the job done.
>
> Anybody have a better idea?
Include a sample trigger for each PL that is shipped with the standard
distribution, along with comments to the effect that "it is suggested
that the documentation for $PL is read before creating triggers"?
It may also be worth including a comment that explicitly states that
all(?) PLs are supported in triggers.
Best regards,
Ben Stewart
--
Robert Bosch (Australia) Pty. Ltd.
Engineering Quality Services, Student Software Engineer (RBAU/EQS4)
Locked Bag 66 - Clayton South, VIC 3169 - AUSTRALIA
mailto:ben.stewart@au.bosch.com
http://www.bosch.com.au/
^ permalink raw reply [nested|flat] 9+ messages in thread
end of thread, other threads:[~2005-10-13 17:45 UTC | newest]
Thread overview: 9+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2005-10-13 01:12 Update timestamp on update Jeff Williams <jeffw@globaldial.com>
2005-10-13 01:44 ` Tom Lane <tgl@sss.pgh.pa.us>
2005-10-13 02:42 ` Jeff Williams <jeffw@globaldial.com>
2005-10-13 02:52 ` Tom Lane <tgl@sss.pgh.pa.us>
2005-10-13 03:07 ` Michael Glaesemann <grzm@myrealbox.com>
2005-10-13 14:22 ` Andrew Sullivan <ajs@crankycanuck.ca>
2005-10-13 17:20 ` Jim C. Nasby <jnasby@pervasive.com>
2005-10-13 17:45 ` Mike Diehl <jdiehl@sandia.gov>
2005-10-13 02:56 Re: Update timestamp on update Stewart Ben (RBAU/EQS4) * <Ben.Stewart@au.bosch.com>
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