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
Message-ID: <201002251619.32048.auryn@wanadoo.es> (raw)
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
--
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-sql@postgresql.org
Cc: auryn@wanadoo.es
Subject: Re: Using new.id in the WHERE condition of one UPDATE inside ON INSERT rule
In-Reply-To: <201002251619.32048.auryn@wanadoo.es>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox