Skip site navigation (1) Skip section navigation (2)

Re: Date Of Entry and Date Of Change

From: "Sean Davis" <sdavis2(at)mail(dot)nih(dot)gov>
To: "Dale Seaburg" <kg5lt(at)verizon(dot)net>
Cc: pgsql-novice(at)postgresql(dot)org
Subject: Re: Date Of Entry and Date Of Change
Date: 2008-08-31 09:39:00
Message-ID: 264855a00808310239y1e4099edr405502173f50f95d@mail.gmail.com (view raw or flat)
Thread:
Lists: pgsql-novice
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

In response to

pgsql-novice by date

Next:From: Bob McConnellDate: 2008-08-31 13:03:24
Subject: Converting a table from SQL Server
Previous:From: Robert WimmerDate: 2008-08-31 08:23:05
Subject: Re: Date Of Entry and Date Of Change

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group