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.
---------------------------(end of broadcast)---------------------------
TIP 6: explain analyze is your friend
