From: | Richard Broersma Jr <rabroersma(at)yahoo(dot)com> |
---|---|
To: | Richard Broersma Jr <rabroersma(at)yahoo(dot)com>, Bernd Helmle <mailings(at)oopsware(dot)de> |
Cc: | General PostgreSQL List <pgsql-general(at)postgresql(dot)org> |
Subject: | Re: Are updateable view as a linked table in ms-access a bad idea?[solved] |
Date: | 2006-12-14 18:00:51 |
Message-ID: | 454308.25720.qm@web31811.mail.mud.yahoo.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-general |
I think I've solved my problem with connecting an update-able view as a linked table in MS Access.
I invite all to comment on flaws or suggest improvements or other <possible> solutions.
This is a test case that I am eventually going to use to model a document hierarchy. This test
case is only modeling one branch of the hierarchy.
in this model I have three tables of increasing detail:
table foo(id, a)
table bar(id, b)
table baz(id, c)
Relationship between tables is:
foo <-(0,1) to (1,1)-> bar <-(0,1) to (1,1)-> baz
Here is how I created a working update-able view that PREFORMS UPDATES CONSISTENTLY WITHOUT
FAILURES so far.
Table, View, Rule Definitions:
-------------------------------------
create sequence public.foo_seq;
create table public.foo
(
id integer primary key
default nextval('public.foo_seq'),
a varchar(5)
);
alter sequence public.foo_seq owned by public.foo.id;
create table public.bar
(
id integer primary key
references public.foo(id)
on delete cascade,
b varchar(5)
);
create table public.baz
(
id integer primary key
references public.bar(id)
on delete cascade,
c varchar(5)
);
create view public.vbar (ctid,id,a,b) as
select b.ctid, a.id, a.a, b.b
from foo a join bar b on (a.id = b.id);
create rule vbar_insert as on insert to public.vbar
do instead
(
insert into foo(id,a) values (default,new.a);
insert into bar(id,b) values (currval('public.foo_seq'),new.b)
);
create rule vbar_update as on update to public.vbar
do instead
(
update foo set a=new.a where id=old.id;
update bar set b=new.b where (id,ctid)=(old.id,old.ctid)
);
create rule vbar_delete as on delete to public.vbar
do instead
(
delete from foo where id = old.id
--public.bar & public.baz are delete cascade
);
create view public.vbaz (ctid,id,a,b,c) as
select b.ctid, a.id, a.a, a.b, b.c
from vbar a join baz b on (a.id = b.id);
create rule vbaz_insert as on insert to public.vbaz
do instead
(
insert into vbar(a,b) values (new.a,new.b);
insert into baz(id,c) values (currval('public.foo_seq'),new.c)
);
create rule vbaz_update as on update to public.vbaz
do instead
(
update vbar set a=new.a, b=new.b where id = old.id;
update baz set c=new.c where (id,ctid)=(old.id,old.ctid)
);
create rule vbaz_delete as on delete to public.vbaz
do instead
(
delete from foo where id = old.id
--public.bar & public.baz are delete cascade
);
Regards,
Richard Broersma Jr.
From | Date | Subject | |
---|---|---|---|
Next Message | Martijn van Oosterhout | 2006-12-14 18:18:44 | Re: a question for the way-back machine |
Previous Message | Vanyel | 2006-12-14 17:57:29 | Re: How to check constraints before call of simple_heap_insert()? |