Обсуждение: 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

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

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

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