Hi,
I want to keep track of modifications to the records in a table.
I tried the following (see example SQL below):
- Table "t" contains my data
- View "v" presents the relevant data to a given group of users; rewrite
rules on "v" carry the changes over to "t" (actually, I have a
bunch of views)
- Table "log" logs the previews values of the records in "t"; rewrite
rules on "t" save the records in "t" about to be modified into "log"
Problem is, I have to grant select permissions on "t" to my users for the
logging rule to operate properly. A similiar problem goes for triggers.
Can someone make a suggestion how I would go about implementing
modification logging?
Thanks for any help,
Helge
create table t (id serial, value int);
create table log (id int, value int, who name default current_user,
when timestamp default current_time);
create rule upd_log as on update to t do
insert into log(id, value) values(old.id, old.value);
create view v as select id, value from t;
create rule upd as on update to v do instead
update t set value=new.value where id=old.id;
A user modifying a row via the view "v" needs read permissions on "t"
for the rule "upd_log" to be able to retrieve the old record values from
"t".
--
Hi! I'm a .signature virus! Put me into your .signature and help me spread!
% rm * .o
rm: cannot remove '.o': No such file or directory