Re: Date Of Entry and Date Of Change

On Sat, Aug 30, 2008 at 10:43 PM, Dale Seaburg <kg5lt@verizon.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@verizon.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