Re: I was spoiled by the MySQL timestamp field

FYI, I usually prefer triggers in cases where you want to modify/check
the row as it is being processed, and rules for modifying other
rows/tables as part of row processing, but both do work.

---------------------------------------------------------------------------

will trillich wrote:
> On Thu, Jan 23, 2003 at 04:26:31AM -0800, Alan T. Miller wrote:
> > As someone who is just getting started with PostygreSQL from
> > years working with MySQL, it appears that the timestamp data
> > type does not behave in the way it did with MySQL. I got used
> > to just defining a column as a timestamp and letting the
> > database throw the latest time stamp in there whenever a row
> > was updated. Is there anything simular in PosgreSQL? How can I
> > accomplish something simular inside the database, or am I stuck
> > populating the field in some manner as in the following example
> >
> > update blah blah blah timestamp = NOW()
>
> triggers or rules can do that with any timestamp (or timestamp(0)
> -- no partial-seconds) field. here's a "rules" approach:
>
> create table _something (
> id serial,
> dat text,
> freshened timestamp(0),
> primary key ( id )
> );
>
> create view something as
> select
> id,
> dat,
> freshened
> from
> _something;
>
> create rule something_add as
> on insert to something
> do instead (
> insert into _something (
> --id,
> dat,
> freshened
> ) values (
> --let id take care of itself,
> NEW.dat,
> current_timestamp
> );
> );
>
> create rule something_edit as
> on update to something
> do instead (
> update _something set
> --id = leave it alone,
> dat = NEW.dat,
> freshened = current_timestamp
> where
> id = NEW.id
> ;
> );
>
> then you can just
>
> insert into something (dat) values ('yada yada');
> update something set dat = 'here we go' where id = 23978;
>
> and "freshened" takes care of itself.
>
> --
> There are 10 kinds of people:
> ones that get binary, and ones that don't.
>
> will(at)serensoft(dot)com
> http://sourceforge.net/projects/newbiedoc -- we need your brain!
> http://www.dontUthink.com/ -- your brain needs us!
>
> Looking for a firewall? Do you think smoothwall sucks? You're
> probably right... Try the folks at http://clarkconnect.org/ !
>
> ---------------------------(end of broadcast)---------------------------
> TIP 5: Have you checked our extensive FAQ?
>
> http://www.postgresql.org/users-lounge/docs/faq.html
>

--
Bruce Momjian | http://candle.pha.pa.us
pgman(at)candle(dot)pha(dot)pa(dot)us | (610) 359-1001
+ If your life is a hard drive, | 13 Roberts Road
+ Christ can be your backup. | Newtown Square, Pennsylvania 19073