From: | Daniel Martini <dmartini(at)uni-hohenheim(dot)de> |
---|---|
To: | "Jim C(dot) Nasby" <decibel(at)decibel(dot)org> |
Cc: | pgsql-general(at)postgresql(dot)org |
Subject: | Re: handing created and updated fields |
Date: | 2005-01-10 14:45:09 |
Message-ID: | 1105368309.41e294f591115@webmail.uni-hohenheim.de |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-general |
Hi,
Citing "Jim C. Nasby" <decibel(at)decibel(dot)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
From | Date | Subject | |
---|---|---|---|
Next Message | Thomas Chille | 2005-01-10 15:20:36 | speaks psql unicode? |
Previous Message | Alban Hertroys | 2005-01-10 14:14:57 | Re: Transaction size |