[no subject]

["Erin Millard" <Erin.Millard@xxxxxxxxxxxx>]

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

---------------------------(end of broadcast)---------------------------
TIP 3: Have you checked our extensive FAQ?

               http://www.postgresql.org/docs/faq