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 ASON INSERT TO my_tableDO 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 =
(SELECTMAX(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
--