Dear all,
Please see SQL below. I'm already satisfied with everything except I wish in
#4, mtime got automatically updated to NW() if not explicitly SET in UPDATE
statement. Is there a way to make the mtime column behave more like I
wanted? Thanks in advance.
create table t1 (
id int primary key,
t text,
ctime timestamp with time zone,
mtime timestamp with time zone
);
create or replace function update_times() returns trigger as $$
begin
if tg_op='INSERT' then
if NEW.ctime is null then NEW.ctime = NW(); end if;
if NEW.mtime is null then NEW.mtime = NW(); end if;
elsif tg_op='UPDATE' then
if NEW.ctime is null then NEW.ctime = LD.ctime; end if;
if NEW.mtime is null then NEW.mtime = NW(); end if;
end if;
return NEW;
end;
$$ language plpgsql;
create trigger update_times before update or insert on t1
for each row execute procedure trig1();
-- #1. mtime & ctime set to NW()
insert into t1 values (1,'text1',null,null);
-- #2. mtime & ctime set to '2001-01-01'
insert into t1 values (2,'text2','2000-01-01','2000-01-01');
-- #3. mtime and ctime set to '2006-06-06'
update t1 set t='new text1',ctime='2006-6-6',mtime='2006-6-6' where id=1;
-- #4. mtime and ctime unchanged
update t1 set t='new text1' where id=1;
-- #5. mtime automatically updated to NW()
update t1 set t='new text1',mtime=null where id=1;

"David Garamond" <davidgaramond (AT) gmail (DOT) comwrites:
8/4/06, Richard Huxton <dev (AT) archonet (DOT) comwrote:
>Just check for LD.mtime = NEW.mtime, or am I missing something here?

How do I differentiate between:
UPDATE t SET mtime=mtime ;
in which mtime is specifically set and should not change,

You don't. A trigger has no way to know the history of the row it's
looking at consider the possibility that it was already modified
by earlier triggers.

If you are really intent on having a way to suppress the mtime update
you could dedicate an additional field to the purpose, eg

UPDATE t SET foo=, bar=, keepmtime = true

and in the trigger something like

if new.keepmtime then
new.keepmtime = false;
else
new.mtime = now();

As long as nothing else ever touches keepmtime this would work.
Personally I'm dubious that it's worth the trouble do you
have a real use-case for suppressing mtime updates?

regards, tom lane

(end of broadcast)
TIP 2: Don't 'kill -9' the postmaster