Re: Date Of Entry and Date Of Change

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.

Dale Seaburg