On 8/4/06,
Rodrigo De León <
rdeleonp@gmail.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