Thread: handing created and updated fields

I think I saw something posted about this recently, but I can't find it
in the archives now. :(

I want to have created and updated fields in a table that are kept
up-to-date by the database and can't be changed accidentally. I think
this can be done with rules, but I'm not sure of the best way to do it.
Basically:

ON INSERT: force created and updated to be current_timestamp
ON UPDATE: deny updated created. force updated to be set to
current_timestamp

I first thought of doing an ON INSERT INSTEAD rule that would ignore
NEW.created and NEW.updated, but it seems inconvenient to have to change
the rule every time the table definition, and I'm not sure if this would
properly handle the SERIAL that I have defined (the rule would need to
include the serial in the insert, but then would the default work?). So
now I'm thinking of doing an ON INSERT INSTEAD UPDATE SET created =
current_timestamp WHERE id = NEW.id, though again I'm not sure if the
serial field (id) would be handled properly.

Does anyone have an example of the best way to handle this scenario?
--
Jim C. Nasby, Database Consultant               decibel@decibel.org
Give your computer some brain candy! www.distributed.net Team #1828

Windows: "Where do you want to go today?"
Linux: "Where do you want to go tomorrow?"
FreeBSD: "Are you guys coming, or what?"

On Mon, Jan 10, 2005 at 05:28:47AM -0600, Jim C. Nasby wrote:
> I think I saw something posted about this recently, but I can't find it
> in the archives now. :(
>
> I want to have created and updated fields in a table that are kept
> up-to-date by the database and can't be changed accidentally. I think
> this can be done with rules, but I'm not sure of the best way to do it.
> Basically:
>
> ON INSERT: force created and updated to be current_timestamp
> ON UPDATE: deny updated created. force updated to be set to
> current_timestamp

Nope, you want triggers. I don't remember the syntax, but the basic
structure would be...

ON INSERT DO TRIGGER set_timestamp
ON UPDATE DO TRIGGER update_timestamp

set_timestamp()
  NEW.created = now()
  NEW.updated = now()

update_timestamp()
  if OLD.created <> NEW.created then ERROR
  NEW.updated = now()

Hope this helps,
--
Martijn van Oosterhout   <kleptog@svana.org>   http://svana.org/kleptog/
> Patent. n. Genius is 5% inspiration and 95% perspiration. A patent is a
> tool for doing 5% of the work and then sitting around waiting for someone
> else to do the other 95% so you can sue them.

Hi,

Citing "Jim C. Nasby" <decibel@decibel.org>:
> ON INSERT: force created and updated to be current_timestamp
> ON UPDATE: deny updated created. force updated to be set to
> current_timestamp
[snip]
> Does anyone have an example of the best way to handle this scenario?

Something along the lines of the following should work (but test first
anyways, though I have copied smaller parts of this from the definitions
in one of my databases here, I have made modifications to fit your
specific task, so typos/errors might have sneaked in):

create function update_trigger() returns trigger as
'begin
new.created := old.created;
new.updated := CURRENT_TIMESTAMP;
return new;
end;'
language 'plpgsql';

create trigger update_trigger BEFORE UPDATE ON your_table_name
FOR EACH ROW EXECUTE PROCEDURE update_trigger();

create function insert_trigger() returns trigger as
'begin
new.created := CURRENT_TIMESTAMP;
new.updated := CURRENT_TIMESTAMP;
return new;
end;'
language 'plpgsql';

create trigger insert_trigger BEFORE INSERT ON your_table_name
FOR EACH ROW EXECUTE PROCEDURE insert_trigger();

HTH,
Regards,
Daniel

On Mon, 2005-01-10 at 15:45 +0100, Daniel Martini wrote:
> Hi,
>
> Citing "Jim C. Nasby" <decibel@decibel.org>:
> > ON INSERT: force created and updated to be current_timestamp
> > ON UPDATE: deny updated created. force updated to be set to
> > current_timestamp
> [snip]
> > Does anyone have an example of the best way to handle this scenario?
>
> Something along the lines of the following should work (but test first
> anyways, though I have copied smaller parts of this from the definitions
> in one of my databases here, I have made modifications to fit your
> specific task, so typos/errors might have sneaked in):
>
> create function update_trigger() returns trigger as
> 'begin
> new.created := old.created;
> new.updated := CURRENT_TIMESTAMP;
> return new;
> end;'
> language 'plpgsql';
>
> create trigger update_trigger BEFORE UPDATE ON your_table_name
> FOR EACH ROW EXECUTE PROCEDURE update_trigger();
>
> create function insert_trigger() returns trigger as
> 'begin
> new.created := CURRENT_TIMESTAMP;
> new.updated := CURRENT_TIMESTAMP;
> return new;
> end;'
> language 'plpgsql';
>
> create trigger insert_trigger BEFORE INSERT ON your_table_name
> FOR EACH ROW EXECUTE PROCEDURE insert_trigger();
>
> HTH,
> Regards,
> Daniel

These could also be combined into one trigger since they are nearly
identical anyway:

CREATE FUNCTION combined_trigger() RETURNS TRIGGER AS '
BEGIN
   NEW.update := CURRENT_TIMESTAMP;
   IF TG_OP = ''INSERT'' THEN
      NEW.created := CURRENT_TIMESTAMP;
   ELSE
      NEW.created := OLD.created;
   END IF;
   RETURN NEW;
END;
' LANGUAGE plpgsql;

CREATE TRIGGER combined_trigger BEFORE INSERT OR UPDATE on
your_table_name FOR EACH ROW EXECUTE PROCEDURE combined_trigger();

Sven

On Mon, Jan 10, 2005 at 11:16:03AM -0500, Sven Willenberger wrote:
> These could also be combined into one trigger since they are nearly
> identical anyway:
>
> CREATE FUNCTION combined_trigger() RETURNS TRIGGER AS '
> BEGIN
>    NEW.update := CURRENT_TIMESTAMP;
>    IF TG_OP = ''INSERT'' THEN
>       NEW.created := CURRENT_TIMESTAMP;
>    ELSE
>       NEW.created := OLD.created;
>    END IF;
>    RETURN NEW;
> END;
> ' LANGUAGE plpgsql;

Excellent; any idea which would perform better (combined v. separate
trigger function)?
--
Jim C. Nasby, Database Consultant               decibel@decibel.org
Give your computer some brain candy! www.distributed.net Team #1828

Windows: "Where do you want to go today?"
Linux: "Where do you want to go tomorrow?"
FreeBSD: "Are you guys coming, or what?"