Re: feature request for Postgresql Rule system.

> 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
);