Hello,
I have been working with a great database system called PostgreSQL for
many years ;-) but never had to use any RULEs. I now have to use and
update through a view and have written a few rules to make this possible
as per the manual.
My insert rules seems to work fine, but I can't make the update rule do
what I want and as I believe stated in the manual.
In the attached schema I have 2 basic tables and a view combining
those. I have 2 rules for insert on the view and these works fine. The
update rule, however, doesn't do what I want. The manual states that the
origsal query tree (where) is added to the rule qualification,
so I would exspect the update statement below would only update 1 row of
the ganntinfo table, but it updates all 5 ?
What am I doing wrong ?
projtaskdb=# SELECT * FROM ganntinfo ;
id | seq | category | name
-----+-----+----------+---------
46 | 0 | SRC | 2WWE
172 | 0 | SRC | RKD60
138 | 0 | SRC | Diverse
34 | 0 | SRC | VF
87 | 0 | SRC | 2WWE
(5 rows)
projtaskdb=# UPDATE tasks SET id = 87, category = 'SRC', name = '2WWE',
customer = 'Custm', description = 'test' WHERE id = 87 and category =
'SRC';
UPDATE 5
projtaskdb=# SELECT * FROM ganntinfo ;
id | seq | category | name
----+-----+----------+------
87 | 0 | SRC | 2WWE
87 | 0 | SRC | 2WWE
87 | 0 | SRC | 2WWE
87 | 0 | SRC | 2WWE
87 | 0 | SRC | 2WWE
(5 rows)
projtaskdb=#
I tried to put an 'explain' in front of the update within the update
rule, but got a syntax error. Why is that ?
Please help,
Leif
SET SESSION AUTHORIZATION 'dba';
CREATE TABLE ganntinfo (
id integer NOT NULL,
seq smallint,
category character varying(20),
name character varying(40)
-- PRIMARY KEY( id, seq )
);
CREATE TABLE taskshead (
id integer PRIMARY KEY,
category character varying(20),
subject character varying(40),
customer character varying(40),
description character varying(400)
);
CREATE VIEW tasks (
id,
seq,
category,
name,
subject,
customer,
description
) AS
SELECT g.id, g.seq, g.category, g.name, h.subject, h.customer, h.description
FROM taskshead h LEFT OUTER JOIN ganntinfo g USING ( id, category )
-- WHERE g.seq = 0 AND g.id = h.id AND g.category = h.category
;
CREATE RULE insert_tasks2taskshead AS
ON INSERT TO tasks WHERE NEW.seq = 0
DO (
insert into taskshead values (
NEW.id, NEW.category, NEW.subject, NEW.customer, NEW.description
);
)
;
CREATE RULE insert_tasks2ganntinfo AS
ON INSERT TO tasks
DO INSTEAD (
insert into ganntinfo values (
NEW.id, NEW.seq, NEW.category, NEW.name
);
)
;
CREATE RULE update_tasks2taskshead AS
ON UPDATE TO tasks WHERE NEW.seq = 0
DO NOTHING
;
CREATE RULE update_tasks2ganntinfo AS
ON UPDATE TO tasks
DO INSTEAD (
update ganntinfo set
id = NEW.id, seq = NEW.seq, category = NEW.category, name = NEW.name
-- WHERE id = NEW.id AND seq = NEW.seq AND category = NEW.category
;
)
;
---------------------------(end of broadcast)---------------------------
TIP 9: the planner will ignore your desire to choose an index scan if your
joining column's datatypes do not match
