From: Aron <auryn@wanadoo.es>
To: pgsql-sql@postgresql.org
Subject: Using new.id in the WHERE condition of one UPDATE inside ON INSERT
	rule
Date: Thu, 25 Feb 2010 16:19:32 +0100
User-Agent: KMail/1.12.2 (Linux/2.6.31-19-generic; KDE/4.3.2; x86_64; ; )
MIME-Version: 1.0
Content-Type: Text/Plain;
  charset="us-ascii"
Content-Transfer-Encoding: 7bit
Message-Id: <201002251619.32048.auryn@wanadoo.es>

I would like to avoid using "SELECT MAX(id) FROM my_table" (it doesn't seem a 
good method), but if I use "new.id", I get new id values, not the "id" 
inserted with the rule, and the condition is always false.

Complete example (it works because it doesn't use new.id inside condition):

DROP TABLE IF EXISTS my_table;
DROP TABLE IF EXISTS my_other_table;

CREATE TABLE my_other_table (
	id serial PRIMARY KEY,
	my_other_cost INTEGER
);

INSERT INTO my_other_table(my_other_cost) VALUES(155);
INSERT INTO my_other_table(my_other_cost) VALUES(277);

CREATE TABLE my_table (
	id serial PRIMARY KEY,
	id_other INTEGER,
	my_cost INTEGER
);

CREATE OR REPLACE RULE my_insert AS
	ON INSERT TO my_table
	DO ALSO
		UPDATE my_table SET my_cost = my_other_table.my_other_cost
		FROM my_other_table
		WHERE new.id_other = my_other_table.id
		AND my_table.id = (SELECT MAX(id) FROM my_table); -- I want " = new.id" 
here, but doesn't work as I expect

INSERT INTO my_table(id_other) VALUES(1);
INSERT INTO my_table(id_other) VALUES(2);

SELECT * FROM my_table;


Thanks
--