Thread: few questions about rules: timestamp, new.oid

Hi,

I am setting up rules to track changes to some tables, in PG 6.4.2-3 (RH
Linux 6.0) ... and have two questions:


1. I'm storing a datetime timestamp for when the change was entered into
my changes table, but the timestamp doesn't change with subsequent
tests:
   create rule users_modify_rule as on update to users do       insert into changes (table_name, change_time, ref_oid,
type)      values ('users', 'now', current.oid, 'update');
 

dmtest=> select * from changes;
table_name|change_time                 |ref_oid|ref_name|type  
----------+----------------------------+-------+--------+------
users     |Mon Aug 02 13:52:34 1999 PDT| 990398|        |update
users     |Mon Aug 02 13:52:34 1999 PDT| 990398|        |update
users     |Mon Aug 02 13:52:34 1999 PDT| 990398|        |update
users     |Mon Aug 02 13:52:34 1999 PDT| 990398|        |update
users     |Mon Aug 02 13:52:34 1999 PDT| 990398|        |update
(5 rows)


2. How can I get the oid of a record being inserted?  Using new.oid
isn't getting it for me... the field in my changes table remains
blank...
   create rule users_add_rule as on insert to users do       insert into changes (table_name, change_time, ref_oid,
type)      values ('users', 'now', new.oid, 'insert');
 

Getting some other field, like new.acctname, does work...


Thanks,
Michael

At 00:04 +0300 on 03/08/1999, Michael Olivier wrote:


> 1. I'm storing a datetime timestamp for when the change was entered into
> my changes table, but the timestamp doesn't change with subsequent
> tests:
>
>     create rule users_modify_rule as on update to users do
>         insert into changes (table_name, change_time, ref_oid, type)
>         values ('users', 'now', current.oid, 'update');

Better use a function than a constant - try now() instead. A constant
usually gets interpreted at the time of creation. A function remains to be
interpreted at the time of use.

Herouth

--
Herouth Maoz, Internet developer.
Open University of Israel - Telem project
http://telem.openu.ac.il/~herutma