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

-----BEGIN PGP SIGNATURE-----
Version: GnuPG v2.0.4-svn0 (GNU/Linux)

iD8DBQBIIW1hzhSR9xwSCbQRAhXeAJ0RJXQFteNDOy6cBm2jcL GXlZDaBACfYVRe
qCZBVzOFzQv7dzK8hH/VzOI=
=1mHq
-----END PGP SIGNATURE-----

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

--
Sent via pgsql-admin mailing list (pgsql-admin@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/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

-----BEGIN PGP SIGNATURE-----
Version: GnuPG v2.0.4-svn0 (GNU/Linux)

iD8DBQBIIcFfzhSR9xwSCbQRAsMEAKCMHwhB8LjN8bFU2UU8B2 Yzcr2IFwCgoYo1
dkw6KWtNIf06cHK96Y/lde4=
=Kany
-----END PGP SIGNATURE-----

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

-----BEGIN PGP SIGNATURE-----
Version: GnuPG v2.0.3 (FreeBSD)

iD8DBQFIIcIszrC0WyuMkpsRAn3WAJ9y0GO8tStJ1zxeUegoEN 71Q4pAFQCcDgrs
u0IC6H2glTNir6MYQPBpKew=
=PIwA
-----END PGP SIGNATURE-----

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

--
Sent via pgsql-admin mailing list (pgsql-admin@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-admin

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

-----BEGIN PGP SIGNATURE-----
Version: GnuPG v2.0.4-svn0 (GNU/Linux)

iD8DBQBIIq6PzhSR9xwSCbQRAqWZAKCrjQxbSyuHO71ihPjjC0 SJVpaCOACfekzP
yVbq8tul3WDqnbY7DiQso6M=
=Qu08
-----END PGP SIGNATURE-----