Thread: column: on update update?
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
Attachment
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
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
Attachment
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
Attachment
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
On Mittwoch, 7. Mai 2008 Tom Lane wrote: > 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). OK, here's the final correct syntax (examples missed the () at the function name): CREATE FUNCTION awlupdate() RETURNS TRIGGER AS ' BEGIN NEW.lastupdate = now(); RETURN NEW; END' LANGUAGE plpgsql; CREATE TRIGGER awlupdate BEFORE UPDATE ON awl FOR EACH ROW EXECUTE PROCEDURE awlupdate(); Thanks for your help! It's a bit complex just for an update, but it works. :-) 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