I've got a table, view, and rules as below. The permissions are set up in
such a way that I can use it just fine as myself via psql. When I try to
access the data using an ms access interface via odbc, I get the first
record in the view, but any attempts to go to other records cause ms access
to tell me that they've been deleted (it's lying though, because I can still
see them through the psql interface).
I thought I had seen a mention on one of the web pages that said some
interfaces don't handle views very well, but can't seem to find that page
again (in hopes that it also had some suggestions to get around the
problem).
Here are my questions:
1) Is this a known problem?
2) Am I doing something wrong?
3) Are there work arounds?
-ron
create table log (
id serial not null,
whenentered timestamp,
username name not null default user,
class varchar(10),
entry varchar
);
create view myview as select id,whenentered,class,entry from log where
username=user;
create rule ins_mylog as on insert to myview do instead insert into log
(whenentered,class,entry) values (now(), NEW.class, NEW.entry);
-- create the rule that will actually do an update on the right record
create rule upd_mylog as on update to myview do instead update log set
whenentered=NEW.whenentered, class=NEW.class, entry=NEW.entry where
id=OLD.id;
-- create a rule that satisfies postgres' need for completeness
create rule upd_mylog0 as on update to myview do instead nothing;
create rule del_mylog0 as on delete to myview do instead nothing;
create rule del_mylog as on delete to myview do instead delete from log
where id=OLD.id;
