Re: default CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP

am 23.01.2006, um 14:49:58 +0100 mailte f g folgendes:
> with mysql you can do:
>
> CREATE TABLE [table]
> ...
> stamp timestamp NOT NULL default CURRENT_TIMESTAMP on
> update CURRENT_TIMESTAMP,
> ...
>
> but with pgsql it seems you need to do a trigger:
>
> CREATE RULE [rule] AS ON UPDATE TO [table] DO UPDATE
> [table] SET stamp = CURRENT_TIMESTAMP \g

This isn't a trigger, this is a rule ;-)

>
> which give you:
>
> ERROR: infinite recursion detected in rules for
> relation [table]

Right, every UPDATE generates a UPDATE...

>
> what's the trick???

Create a TRIGGER instead a RULE. A simple example:

test=# create function update_stamp() returns trigger as $$begin new.stamp := now(); return new; end;$$ language plpgsql;
CREATE FUNCTION
test=# create trigger tg_update before update on ts for each row execute procedure update_stamp();
CREATE TRIGGER
test=# select * from ts;
id | stamp
----+-------------------------------
5 | 2006-01-23 15:26:45.921568+01
(1 row)

test=# update ts set id = 10 where id = 5;
UPDATE 1
test=# select * from ts;
id | stamp
----+-------------------------------
10 | 2006-01-23 15:29:12.759839+01
(1 row)

HTH, Andreas
--
Andreas Kretschmer (Kontakt: siehe Header)
Heynitz: 035242/47212, D1: 0160/7141639
GnuPG-ID 0x3FFF606C http://wwwkeys.de.pgp.net
=== Schollglas Unternehmensgruppe ===