I am facing a difficult problem with setting up a rule.
The rule is essentially designed to allow updates to a view made up of
data from two tables.
Here is a simplified example to reproduce the problem:
--
-- clear the table and view
--
DROP VIEW IF EXISTS full_view;
DROP TABLE IF EXISTS child_table;
DROP TABLE IF EXISTS base_table;
--
-- create the tables
--
CREATE TABLE base_table
(
id INTEGER,
base_data CHAR(1)
);
CREATE TABLE child_table
(
id INTEGER,
child_data CHAR(1)
);
--
-- create the view
--
CREATE VIEW full_view AS
(
SELECT
base.id,
base.base_data,
child.child_data
FROM base_table AS base
INNER JOIN child_table AS child ON child.id = base.id
);
--
-- create update rule
--
CREATE RULE view_update AS
ON UPDATE TO full_view
DO INSTEAD
(
UPDATE base_table
SET
id = NEW.id,
base_data = NEW.base_data
WHERE id = OLD.id;
UPDATE child_table
SET
id = NEW.id,
child_data = NEW.child_data
WHERE id = OLD.id;
);
--
-- insert some data
--
INSERT INTO base_table (id, base_data) VALUES (1, 'a');
INSERT INTO child_table (id, child_data) VALUES (1, 'b');
--
-- everything is OK so far
--
SELECT * FROM full_view;
--
-- this query works as expected
--
UPDATE full_view SET child_data = 'c' WHERE base_data = 'a';
--
-- child_data has been updated
--
SELECT * FROM full_view;
--
-- this query does not work as expected
--
UPDATE full_view SET child_data = 'd', base_data = 'e' WHERE base_data =
'a';
--
-- child_data has not been updated, even though base_data was
--
SELECT * FROM full_view;
I think I understand why this happens. It seems logical that the first
query of the update rule is being run and then the second matches no
rows for "base_data = 'a'" because it was updated by the first.
Can anyone think of a workaround that would let me achieve this
functionality? Like, perhaps "caching" the resulting changes from the
first update query and only applying them after the second is run?
---
Erin Millard
Systems Analyst
I-Nex Corporation Pty Ltd
