Thread: default CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP

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

which give you:

ERROR:  infinite recursion detected in rules for
relation [table]

what's the trick???






___________________________________________________________________________
Nouveau : téléphonez moins cher avec Yahoo! Messenger ! Découvez les tarifs exceptionnels pour appeler la France et
l'international.
Téléchargez sur http://fr.messenger.yahoo.com

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

yes thx

it seems a big montain just for that
plus had to do
(shell) createlang -d [db] plpgsql
and replace $$ by simple quote

and do you know why timestamp is only date instead of
date+time ?








___________________________________________________________________________
Nouveau : téléphonez moins cher avec Yahoo! Messenger ! Découvez les tarifs exceptionnels pour appeler la France et
l'international.
Téléchargez sur http://fr.messenger.yahoo.com

I there, do we have switch(){ case:} in pl/pgsql. Tks

On Tue, Jan 24, 2006 at 05:51:37PM +0000, Luis Silva wrote:
> I there, do we have switch(){ case:} in pl/pgsql. Tks

See the PL/pgSQL documentation, in particular the "Control Structures"
section.  Here's the link for 8.1 (but use the documentation for
whatever version you're running):

http://www.postgresql.org/docs/8.1/interactive/plpgsql-control-structures.html

The documentation shows no switch/case structure but you should be
able to do the same thing with IF-THEN-ELSIF.  However, SQL does
have a CASE expression that you might be able to use:

http://www.postgresql.org/docs/8.1/interactive/functions-conditional.html

--
Michael Fuhr

On Mon, Jan 23, 2006 at 06:24:43PM +0100, f g wrote:
> it seems a big montain just for that

Not really -- it's just that MySQL has a shortcut for a particular
special case and PostgreSQL has a more powerful generalized mechanism
(as does MySQL 5.0 with its "rudimentary triggers").

> plus had to do
> (shell) createlang -d [db] plpgsql

That's because PL/pgSQL isn't installed by default unless you've
created it in your template database.

> and replace $$ by simple quote

Dollar quotes are available in PostgreSQL 8.0 and later; if you're
running an earlier version then you'll have to use single quotes.

> and do you know why timestamp is only date instead of
> date+time ?

Timestamps contain both date and time.  Can you show an example of
what you're doing to get only dates?

--
Michael Fuhr