From: | Helge Bahmann <bahmann(at)math(dot)tu-freiberg(dot)de> |
---|---|
To: | pgsql-novice(at)postgresql(dot)org |
Subject: | update logging |
Date: | 2000-10-19 16:45:29 |
Message-ID: | Pine.LNX.4.21.0010191810090.2745-100000@lothlorien.stunet2.tu-freiberg.de |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-novice |
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
From | Date | Subject | |
---|---|---|---|
Next Message | Tom Lane | 2000-10-19 17:22:48 | Re: Stupid question: concatenating strings |
Previous Message | bmccoy | 2000-10-19 16:32:31 | Re: changing data type |