Re: Date Of Entry and Date Of Change

On Sat, Aug 30, 2008 at 10:43 PM, Dale Seaburg <kg5lt(at)verizon(dot)net> wrote:
>
> On Aug 30, 2008, at 8:56 PM, Sean Davis wrote:
>
>> On Sat, Aug 30, 2008 at 5:49 PM, Dale Seaburg <kg5lt(at)verizon(dot)net> wrote:
>>>
>>> I need to be able to establish the Date of Entry (INSERT) and Date of
>>> Change
>>> (UPDATE) of a row to a table. I have added to my table two columns,
>>> named
>>> 'doe' and 'doc' respectively. For sake of discussion, let's call the
>>> Table
>>> 'instr'. What would be the best method of added the current timestamp
>>> (date
>>> and time) to each of these two new columns. In my mind, the timestamp
>>> would
>>> need to be added just before the row was INSERTed or UPDATEd to prevent
>>> any
>>> "race" condition.
>>>
>>> I have looked at Triggers and Functions in the pgAdmin helps, but it is
>>> confusing at best, how to arrive at a solution. Any help would be
>>> appreciated. Perhaps, a simple example to get me headed in the right
>>> direction.
>>
>> You can set the default for those columns to current_timestamp, as a
>> start. Then, you can use an on update trigger for setting the on
>> update column. Alternatively, you can just use current_timestamp as
>> the value for updates to your update column.
>>
>> See here:
>>
>> http://www.postgresql.org/docs/8.3/static/functions-datetime.html
>>
>> And here:
>>
>> http://www.postgresql.org/docs/8.3/static/sql-createtable.html
>>
>> And, finally, here:
>>
>> http://www.postgresql.org/docs/8.3/static/plpgsql-trigger.html
>>
>> Hope that helps.
>>
>> Sean
>
> Yes, the default values should have been obvious, but I was attempting to
> make it too complicated.
>
> The last reference you gave, I found too in the pgAdmin III helps. But,
> when I attempt to create a Trigger Function in pgAdmin, to implement the
> UPDATE function, I get a message in the SQL tab of that window saying "--
> definition incomplete". When I try to get Help, I am pointed to a "404-like
> code" in the Help screen. No matter what I do in trying to create a Trigger
> Function, I get nowhere. My postgresql is 8.2 as reported by pgAdmin.
>
> I assume I can create Trigger Functions in pgAdmin III. Perhaps not.

Hi, Dale. Try pasting this into a pgAdminIII sql window:

create table abc (
id serial primary key,
doe timestamp default current_timestamp,
dou timestamp default current_timestamp,
val varchar
);

create or replace function abc_trig_fn() returns trigger as $$
BEGIN
NEW.dou=current_timestamp;
IF (TG_OP='INSERT') THEN
NEW.doe=current_timestamp;
END IF;
RETURN NEW;
END
$$ language plpgsql;

CREATE TRIGGER abc_trig
BEFORE INSERT OR UPDATE ON abc
FOR EACH ROW EXECUTE PROCEDURE abc_trig_fn();

BEGIN;
insert into abc(val) values ('abc');
insert into abc(val) values ('123');
insert into abc(val) values ('xyz');
END;
select * from abc;

update abc set val='def' where val='123';

select * from abc;

Sean