> At no point did you show us details, but I suppose that this rule is
> relying on a join view?
Yes, the view is a join between two tables as a test case. I provided the
details of my test case
below. However, I could see the use of joining as many as four tables in an
updatable view.
> Once you update one side of the join with a
> different join key value, the join row in question no longer exists in
> the view ... so the second update doesn't find a row to update. This
> has nothing to do with ACID.
I see, ACID wasn't the correct word choice to use. I realize that the rule
system can have many
uses and allowing views to become updatable is just one of its many uses. But
if a view is going
to be updatable, shouldn't behave exactly as a table would to at least for
single tuple insert,
update, and delete statements?
Regards,
Richard Broersma Jr.
-- Table Definitions
CREATE SEQUENCE public.person_seq
INCREMENT BY 1
START WITH 1;
CREATE TABLE public.person
( id integer primary key not null
default nextval('public.person_seq'),
name varchar(30) unique not null);
ALTER SEQUENCE public.person_seq OWNED BY public.person.id;
CREATE TABLE public.husband
( id integer primary key
references person(id)
on delete cascade,
tiesize integer not null);
CREATE TABLE public.wife
( id integer primary key
references person(id)
on delete cascade,
dresssize integer not null);
-- view definitions
CREATE OR REPLACE VIEW public.vwife (id, name, dresssize) AS
SELECT A.id, A.name, B.dresssize
FROM public.person as A
INNER JOIN public.wife as B
ON A.id = B.ID;
CREATE OR REPLACE RULE vwife_insert
AS ON INSERT TO public.vwife
DO INSTEAD
(
INSERT INTO public.person ( id, name )
VALUES ( DEFAULT, NEW.name);
INSERT INTO public.wife ( id, dresssize )
VALUES ( currval('public.person_seq'), NEW.dresssize )
);
CREATE OR REPLACE RULE vwife_update
AS ON UPDATE TO public.vwife
DO INSTEAD
(
UPDATE public.person SET name = NEW.name
WHERE id = OLD.id;
UPDATE public.wife SET dresssize = NEW.dresssize
WHERE id = OLD.id
);
CREATE OR REPLACE RULE vwife_delete
AS ON DELETE TO public.vwife
DO INSTEAD
(
DELETE FROM public.person
WHERE id = OLD.id
);
CREATE OR REPLACE VIEW public.vhusband (id, name, tiesize) AS
SELECT A.id, A.name, B.tiesize
FROM public.person as A
INNER JOIN public.husband as B
ON A.id = B.ID;
CREATE OR REPLACE RULE vhusband_insert
AS ON INSERT TO public.vhusband
DO INSTEAD
(
INSERT INTO
public.person ( id, name )
VALUES ( DEFAULT, NEW.name);
INSERT INTO public.husband ( id, tiesize )
VALUES ( currval('public.person_seq'), NEW.tiesize)
);
CREATE OR REPLACE RULE vhusband_update_person
AS ON UPDATE TO public.vhusband
DO INSTEAD
(
UPDATE public.person
SET name = NEW.name
WHERE id = OLD.id;
UPDATE public.husband
SET tiesize = NEW.tiesize
WHERE id = OLD.id
);
CREATE OR REPLACE RULE vhusband_delete
AS ON DELETE TO public.vhusband
DO INSTEAD
(
DELETE FROM public.person
WHERE id = OLD.id
);
---------------------------(end of broadcast)---------------------------
TIP 2: Don't 'kill -9' the postmaster
