Re: I was spoiled by the MySQL timestamp field

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@serensoft.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/ !