Re: feature request for Postgresql Rule system.

[Richard Broersma Jr <rabroersma@xxxxxxxxx>]

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