Hosting Provided By

[GENERAL]

From: Erin Millard <Erin.Millard(at)i-nex.com.au>
Date: Thu Nov 01 2007 - 21:08:14 EDT

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)

);

Do you need help?

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;

);

Do you need more help?

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;

Can we help you?

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?

Can't find what you're looking for?

http://www.postgresql.org/docs/faq Received on Tue Nov 6 09:35:42 2007

This message: [ Message body ]
Next message: Sascha Bohnenkamp: "Re: [GENERAL] Copy the database.."
Previous message: Andy: "Re: [GENERAL] Populating large DB from Perl script"

Contemporary messages sorted: [ By Date ] [ By Thread ] [ By Subject ] [ By Author ] [ By messages with attachments ]

This archive was generated by hypermail 2.1.8 : Mon Jun 16 2008 - 19:23:25 EDT