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    ===