rules and rows affected - Mailing list pgsql-general

Hi all... I'm experimenting for the first time with rules to make a view
updatable, but I've found a strange "effect"...
I'll try to explain what I'm trying to achieve

Suppose I have a table "friends" and a table "hobbies". Each friend can be
interested in one or more hobbies:

CREATE TABLE friends(

friend_id varchar(5) NOT NULL,

friend_name varchar(40),

CONSTRAINT "friends_pkey" PRIMARY KEY (friend_id));


CREATE TABLE hobbies

(

hobby_id varchar(5) NOT NULL,

hobby_desc varchar(40),

CONSTRAINT "hobbies_pkey" PRIMARY KEY (hobby_ib));



CREATE TABLE friends_hobbies

(

friend_id varchar(5) NOT NULL,

hobby_id varchar(5) NOT NULL,

CONSTRAINT "friends_hobbies_pk" PRIMARY KEY (friend_id, hobby_id);



Now I use this view to show one friend with all hobbies and a boolean value
that means "interested" (if present in friends_hobbies table) or

"not interested" (if record is not present).





CREATE OR REPLACE VIEW test_rule (friend_id, friend_name, hobby_id,
hobby_desc, intersted)

AS

SELECT a.friend_id, b.friend_name, a.hobby_id, c.hobby_desc, true

FROM friends_hobbies a

JOIN friends b ON a.friend_id = b.friend_id

JOIN hobbies c ON a.hobby_id = c.hobby_id

UNION

SELECT a.friend_id, a.friend_name, b.hobby_id, b.hobby_desc, false

FROM friends a, hobbies b

WHERE NOT b.hobby_id IN ( SELECT friends_hobbies.hobby_id

FROM friends_hobbies

WHERE friends_hobbies.friend_id = a.friend_id);

So far so good. Now I'd like to make this view updatable, so that acting on
the boolean flag you can insert a row in friends_hobbies (flag from false to
true) or delete a row (flag from true to false); any other action on view
(inserting or deleting or updating something different than flag) is
refused.



CREATE RULE test_rule_del AS ON DELETE TO test_rule

DO INSTEAD NOTHING;

CREATE RULE test_rule_ins AS ON INSERT TO test_rule

DO INSTEAD NOTHING;



CREATE RULE test_rule_upd AS ON UPDATE TO test_rule

DO INSTEAD NOTHING;







CREATE RULE test_rule_upd1 AS ON UPDATE TO test_rule

WHERE new.interested <> old.interested AND new.interested = False DO INSTEAD

DELETE FROM friends_hobbies WHERE friend_id = new.friend_id and

hobby_id = new.hobby_id;







CREATE RULE test_rule_upd2 AS ON UPDATE TO test_rule

WHERE new.interested <> old.interested and new.interested = True DO INSTEAD

INSERT INTO friends_hobbies (friend_id, hobby_id) VALUES (new.friend_id,
new.hobby_id);



Everything works perfectly... BUT the query returns always 0 rows affected,
even if one record is inserted or deleted in friends_hobbies...why??

My development environment complaints that as there are no rows affected
could be a potential concurrency conflict and rollbacks my update....

Is there a way to overcome this and obtain the "real" number of rows
affected???

Thank you.