Re: autoupdating mtime column

On 8/4/06, Rodrigo De León <rdeleonp(at)gmail(dot)com> wrote:
>
> How about:
>
> create or replace function
> update_times()
> returns trigger as $$
> begin
> if TG_OP='INSERT' then
> new.ctime = coalesce(new.ctime,now());
> new.mtime = coalesce(new.mtime,now());
> elsif TG_OP='UPDATE' then
> new.ctime = old.ctime;
> new.mtime = now();
> end if;
> return new;
> end;
> $$ language plpgsql;
>

But that would disallow setting mtime and ctime to arbitrary values, which I
want to permit. But ctime and mtime should be set to current time if not
specified in INSERT, and mtime to current time if not specified in UPDATE. I
guess what I want is the MySQL's TIMESTAMP autoupdate behaviour, whichI
can't seem to be able to emulate in Postgres, because there's no information
given about which columns are specified in the SET clause, only the NEW and
OLD records.

--
dave