Skip site navigation (1) Skip section navigation (2)

Re: default CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP

From: "A(dot) Kretschmer" <andreas(dot)kretschmer(at)schollglas(dot)com>
To: pgsql-novice(at)postgresql(dot)org
Subject: Re: default CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP
Date: 2006-01-23 14:31:34
Message-ID: 20060123143134.GH18894@webserv.wug-glas.de (view raw or flat)
Thread:
Lists: pgsql-novice
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    === 

In response to

Responses

pgsql-novice by date

Next:From: Frederic_TherrienDate: 2006-01-23 16:41:41
Subject: Question about support for SUSE 10
Previous:From: f gDate: 2006-01-23 13:49:58
Subject: default CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group