Re: handing created and updated fields

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