pg.ddx.io pgsql-admin@postgresql.org mailing list archive
help / color / mirror / Atom feedcolumn: on update update?
5+ messages / 4 participants
[nested] [flat]
* column: on update update?
@ 2008-05-07 08:50 Michael Monnerie <michael.monnerie@it-management.at>
0 siblings, 1 reply; 5+ messages in thread
From: Michael Monnerie @ 2008-05-07 08:50 UTC (permalink / raw)
To: pgsql-admin
Hi, I fear this is a beginners question but I'm no SQL guru and couldn't
find it out of the documentation.
For SpamAssassin, I have a table "awl" (auto white list), and there
should be one field "lastupdate". I added it like this:
ALTER TABLE awl add column lastupdate timestamp default now();
But I also need the "lastupdate" be updated every time a field in that
row is updated, so a statement like this:
ALTER TABLE awl add column lastupdate2 timestamp default now() on update
set now();
But it doesn't work. Do I need a trigger? If yes, what would be the
format? I never did triggers.
mfg zmi
--
// Michael Monnerie, Ing.BSc ----- http://it-management.at
// Tel: 0676/846 914 666 .network.your.ideas.
// PGP Key: "curl -s http://zmi.at/zmi.asc | gpg --import"
// Fingerprint: AC19 F9D5 36ED CD8A EF38 500E CE14 91F7 1C12 09B4
// Keyserver: www.keyserver.net Key-ID: 1C1209B4
Attachments:
signature.asc (application/pgp-signature, 193B) download
^ permalink raw reply [nested|flat] 5+ messages in thread
* Re: column: on update update?
@ 2008-05-07 13:23 Richard Broersma <richard.broersma@gmail.com>
parent: Michael Monnerie <michael.monnerie@it-management.at>
0 siblings, 1 reply; 5+ messages in thread
From: Richard Broersma @ 2008-05-07 13:23 UTC (permalink / raw)
To: Michael Monnerie <michael.monnerie@it-management.at>; +Cc: pgsql-admin
On Wed, May 7, 2008 at 1:50 AM, Michael Monnerie
<michael.monnerie@it-management.at> wrote:
> But I also need the "lastupdate" be updated every time a field in that
> row is updated, so a statement like this:
>
> But it doesn't work. Do I need a trigger? If yes, what would be the
> format? I never did triggers.
Well you have two options. The perferred method would be to have your
application include an update to your lastupdate field whenever
anyother rows are updated.
UPDATE awl
SET lastupdate = CURRENT_TIMESTAMP,
....,
WHERE ....;
the other option is to add an after update trigger, and manually set
lastupdate then.
--
Regards,
Richard Broersma Jr.
Visit the Los Angles PostgreSQL Users Group (LAPUG)
http://pugs.postgresql.org/lapug
^ permalink raw reply [nested|flat] 5+ messages in thread
* Re: column: on update update?
@ 2008-05-07 14:49 Michael Monnerie <michael.monnerie@it-management.at>
parent: Richard Broersma <richard.broersma@gmail.com>
0 siblings, 1 reply; 5+ messages in thread
From: Michael Monnerie @ 2008-05-07 14:49 UTC (permalink / raw)
To: pgsql-admin
On Mittwoch, 7. Mai 2008 Richard Broersma wrote:
> Well you have two options. The perferred method would be to have
> your application include an update to your lastupdate field whenever
> anyother rows are updated.
But as we speak about SpamAssassin, an external project, I do not want
to patch every new version that comes out. Maybe they include it once,
but it isn't currently so I need the db do it.
> the other option is to add an after update trigger, and manually set
> lastupdate then.
Would that be correct? I never did it and looked purely into docs:
CREATE FUNCTION awlupdate()
AS 'UPDATE awl SET lastupdate=CURRENT_TIMESTAMP;'
LANGUAGE SQL;
CREATE TRIGGER awlupdate AFTER UPDATE ON awl FOR EACH ROW
EXECUTE PROCEDURE awlupdate();
mfg zmi
--
// Michael Monnerie, Ing.BSc ----- http://it-management.at
// Tel: 0676/846 914 666 .network.your.ideas.
// PGP Key: "curl -s http://zmi.at/zmi.asc | gpg --import"
// Fingerprint: AC19 F9D5 36ED CD8A EF38 500E CE14 91F7 1C12 09B4
// Keyserver: www.keyserver.net Key-ID: 1C1209B4
Attachments:
signature.asc (application/pgp-signature, 193B) download
^ permalink raw reply [nested|flat] 5+ messages in thread
* Re: column: on update update?
@ 2008-05-07 14:52 CZUCZY Gergely <gergely.czuczy@harmless.hu>
parent: Michael Monnerie <michael.monnerie@it-management.at>
0 siblings, 1 reply; 5+ messages in thread
From: CZUCZY Gergely @ 2008-05-07 14:52 UTC (permalink / raw)
To: Michael Monnerie <michael.monnerie@it-management.at>; +Cc: pgsql-admin
On Wed, 7 May 2008 16:49:03 +0200
Michael Monnerie <michael.monnerie@it-management.at> wrote:
> > the other option is to add an after update trigger, and manually set
> > lastupdate then.
>
> Would that be correct? I never did it and looked purely into docs:
>
> CREATE FUNCTION awlupdate()
> AS 'UPDATE awl SET lastupdate=CURRENT_TIMESTAMP;'
> LANGUAGE SQL;
>
> CREATE TRIGGER awlupdate AFTER UPDATE ON awl FOR EACH ROW
> EXECUTE PROCEDURE awlupdate();
I'd prefer this version:
CREATE FUNCTION awlupdate RETURNS TRIGGER AS '
BEGIN
NEW.lastupdate = now();
RETURN NEW;
END' LANGUAGE plpgsql;
CREATE TRIGGER... the same.
But both methods are fine. The only thing is, the function must return TRIGGER
AFAIK.
--
Üdvölettel,
Czuczy Gergely
Harmless Digital Bt
mailto: gergely.czuczy@harmless.hu
Tel: +36-30-9702963
Attachments:
signature.asc (application/pgp-signature, 186B) download
^ permalink raw reply [nested|flat] 5+ messages in thread
* Re: column: on update update?
@ 2008-05-07 15:27 Tom Lane <tgl@sss.pgh.pa.us>
parent: CZUCZY Gergely <gergely.czuczy@harmless.hu>
0 siblings, 0 replies; 5+ messages in thread
From: Tom Lane @ 2008-05-07 15:27 UTC (permalink / raw)
To: CZUCZY Gergely <gergely.czuczy@harmless.hu>; +Cc: Michael Monnerie <michael.monnerie@it-management.at>; pgsql-admin
CZUCZY Gergely <gergely.czuczy@harmless.hu> writes:
> I'd prefer this version:
> CREATE FUNCTION awlupdate RETURNS TRIGGER AS '
> BEGIN
> NEW.lastupdate = now();
> RETURN NEW;
> END' LANGUAGE plpgsql;
> CREATE TRIGGER... the same.
No, the trigger command has to be BEFORE UPDATE not AFTER UPDATE for
this to work. This is definitely the better way though because the
row only gets updated once, not stored and then updated again
(in fact, I think you could get into an infinite loop if an AFTER
UPDATE trigger tries to update the row again).
regards, tom lane
^ permalink raw reply [nested|flat] 5+ messages in thread
end of thread, other threads:[~2008-05-07 15:27 UTC | newest]
Thread overview: 5+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2008-05-07 08:50 column: on update update? Michael Monnerie <michael.monnerie@it-management.at>
2008-05-07 13:23 ` Richard Broersma <richard.broersma@gmail.com>
2008-05-07 14:49 ` Michael Monnerie <michael.monnerie@it-management.at>
2008-05-07 14:52 ` CZUCZY Gergely <gergely.czuczy@harmless.hu>
2008-05-07 15:27 ` Tom Lane <tgl@sss.pgh.pa.us>
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