Thread: maintaining a field for every UPDATION.

Hello all,

My need is something like this..

Consider in my database i have around 200 tables. In all my tables i
would like to maintain below 2 mandatory fields.

create_time timestamp,
modify_time timestamp.

So, for every insertions i would like to maintain create_time and i
had it, by setting default value to now() to that column.

Well, now i would like to maintain modify_time for every updation. how
can i? Using common plpgsql function i cant even able to refer OID
even during UPDATION. The result is

ERROR:  record old has no field oid    or
ERROR:  record new has no field oid

One way: i can have seperate plpgsql function for every table and
apply conditions accordingly. But i dont think thats the consistent
way.

how else can i maintain modify_time field ?

Thankx in advance.

======================================================================Of course you have a purpose -- to find a
purpose.
======================================================================

Regards,
Bhuvaneswar.

On Fri, 4 Jan 2002, Bhuvan A wrote:

> Well, now i would like to maintain modify_time for every updation. how
> can i? Using common plpgsql function i cant even able to refer OID
> even during UPDATION. The result is
>
> ERROR:  record old has no field oid
>         or
> ERROR:  record new has no field oid
>
> One way: i can have seperate plpgsql function for every table and
> apply conditions accordingly. But i dont think thats the consistent
> way.
>
> how else can i maintain modify_time field ?

Would a before update trigger function something like:

beginNEW.modify_time=now();return NEW;
end;

do what you want?