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@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.


Dale Seaburg