[SQL] autoupdating mtime column

[David Garamond]

Dear all,Please see SQL below. I'm already satisfied with everything except I wish in #4, mtime got automatically updated to NOW() 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 = NOW(); end if;    if NEW.mtime is null then NEW.mtime = NOW(); end if;  elsif tg_op='UPDATE' then     if NEW.ctime is null then NEW.ctime
 = OLD.ctime; end if;    if NEW.mtime is null then NEW.mtime = NOW(); 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 NOW()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 unchangedupdate t1 set t='new text1' where id=1;-- #5. mtime automatically updated to NOW()update t1 set t='new text1',mtime=null where id=1;

Re: [SQL] autoupdating mtime column

[Richard Huxton]

David Garamond wrote:

Dear all,

Please see SQL below. I'm already satisfied with everything except I 
wish in
#4, mtime got automatically updated to NOW() if not explicitly SET in 
UPDATE

statement. Is there a way to make the mtime column behave more like I
wanted? Thanks in advance.


Just check for OLD.mtime = NEW.mtime, or am I missing something here?

--
  Richard Huxton
  Archonet Ltd

---(end of broadcast)---
TIP 1: if posting/reading through Usenet, please send an appropriate
  subscribe-nomail command to [EMAIL PROTECTED] so that your
  message can get through to the mailing list cleanly

Re: [SQL] autoupdating mtime column

[David Garamond]

On 8/4/06, Richard Huxton  wrote:
David Garamond wrote:> Dear all,>> Please see SQL below. I'm already satisfied with everything except I> wish in> #4, mtime got automatically updated to NOW() if not explicitly SET in
> UPDATE> statement. Is there a way to make the mtime column behave more like I> wanted? Thanks in advance.Just check for OLD.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, andUPDATE t SET foo=bar ...;in which mtime is not mentioned and should be updated automagically to NOW().
--dave

Re: [SQL] autoupdating mtime column

[Rodrigo De León]

On 8/4/06, David Garamond <[EMAIL PROTECTED]> wrote:

On 8/4/06, Richard Huxton  wrote:
> David Garamond wrote:
> > Dear all,
> >
> > Please see SQL below. I'm already satisfied with everything except I
> > wish in
> > #4, mtime got automatically updated to NOW() if not explicitly SET in
> > UPDATE
> > statement. Is there a way to make the mtime column behave more like I
> > wanted? Thanks in advance.
>
> Just check for OLD.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, and

UPDATE t SET foo=bar ...;

in which mtime is not mentioned and should be updated automagically to
NOW().


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;

Regards,

Rodrigo

---(end of broadcast)---
TIP 6: explain analyze is your friend

Re: [SQL] autoupdating mtime column

[David Garamond]

On 8/4/06, Rodrigo De León <[EMAIL PROTECTED]> wrote:
How about:create or replace functionupdate_times()returns trigger as $$beginif TG_OP='INSERT' thennew.ctime = coalesce(new.ctime,now());
new.mtime = coalesce(new.mtime,now());elsif TG_OP='UPDATE' thennew.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

Re: [SQL] autoupdating mtime column

[Richard Huxton]

David Garamond wrote:

On 8/4/06, Richard Huxton  wrote:

David Garamond wrote:
> Dear all,
>
> Please see SQL below. I'm already satisfied with everything except I
> wish in
> #4, mtime got automatically updated to NOW() if not explicitly SET in
> UPDATE
> statement. Is there a way to make the mtime column behave more like I
> wanted? Thanks in advance.

Just check for OLD.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, and

UPDATE t SET foo=bar ...;

in which mtime is not mentioned and should be updated automagically to
NOW().


You can't. The trigger knows nothing about the original statement, just 
the old and new tuple values.


You could use SET foo=DEFAULT, but that's neither more or less 
convenient than now() in my eyes.


--
  Richard Huxton
  Archonet Ltd


---(end of broadcast)---
TIP 1: if posting/reading through Usenet, please send an appropriate
  subscribe-nomail command to [EMAIL PROTECTED] so that your
  message can get through to the mailing list cleanly

Re: [SQL] autoupdating mtime column

[Tom Lane]

"David Garamond" <[EMAIL PROTECTED]> writes:
> On 8/4/06, Richard Huxton  wrote:
>> Just check for OLD.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

Re: [SQL] autoupdating mtime column

[David Garamond]

On 8/4/06, Tom Lane <[EMAIL PROTECTED]> wrote:
If you are really intent on having a way to suppress the mtime updateyou could dedicate an additional field to the purpose, egUPDATE t SET foo=..., bar=..., keepmtime = true ...and in the trigger something like
if new.keepmtime thennew.keepmtime = false;elsenew.mtime = now();As long as nothing else ever touches keepmtime this would work.Personally I'm dubious that it's worth the trouble
Yeah, it's too expensive an overhead just for the sake of a slightly shorter UPDATE statement. 
 --- do youhave a real use-case for suppressing mtime updates?Syncing tables between databases (a la "rsync --times"). Btw, I'm considering temporarily disabling the update_times() trigger when sync-ing.
Thanks,--dave

Re: [SQL] autoupdating mtime column

[Richard Huxton]

David Garamond wrote:

On 8/4/06, Tom Lane <[EMAIL PROTECTED]> wrote:
--- do you

have a real use-case for suppressing mtime updates?



Syncing tables between databases (a la "rsync --times"). Btw, I'm
considering temporarily disabling the update_times() trigger when sync-ing.


I'd consider running the sync as a different (privileged) user and 
checking the current user in the trigger. Ordinary users always get 
now(), privileged users always get what they provide (and they are 
forced to provide some value). Does what you want and adds a safety 
catch too.


Alternatively, you could do something similar with views.

--
  Richard Huxton
  Archonet Ltd

---(end of broadcast)---
TIP 9: In versions below 8.0, the planner will ignore your desire to
  choose an index scan if your joining column's datatypes do not
  match

Re: [SQL] autoupdating mtime column

[David Garamond]

On 8/5/06, Richard Huxton  wrote:
>> have a real use-case for suppressing mtime updates?> Syncing tables between databases (a la "rsync --times"). Btw, I'm> considering temporarily disabling the update_times() trigger when sync-ing.
I'd consider running the sync as a different (privileged) user andchecking the current user in the trigger. Ordinary users always getnow(), privileged users always get what they provide (and they areforced to provide some value). Does what you want and adds a safety
catch too.Alternatively, you could do something similar with views.That's a nice idea indeed. Thanks!--dave