Thread: Using new.id in the WHERE condition of one UPDATE inside ON INSERT rule

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
-- 

Aron <auryn@wanadoo.es> writes:
> 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.

"new.id" is a macro, which in this example will be expanded into a
nextval() function call, which is why it doesn't work --- the nextval()
in the WHERE condition will produce a different value from the one in
the original INSERT.  You would be far better off using a trigger here
instead of a rule.
        regards, tom lane

On Jueves 25 Febrero 2010 16:28:56 usted escribió:
> Aron <auryn@wanadoo.es> writes:
> > 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.
>
> "new.id" is a macro, which in this example will be expanded into a
> nextval() function call, which is why it doesn't work --- the nextval()
> in the WHERE condition will produce a different value from the one in
> the original INSERT.  You would be far better off using a trigger here
> instead of a rule.
>
>             regards, tom lane
>
Thank you very much.
I've used this trigger succesfully:

CREATE OR REPLACE FUNCTION copy_cost RETURNS "trigger" AS '
DECLAREchange_other BOOL;
BEGINIF (tg_op = ''UPDATE'') THEN    IF (new.id_other <> old.id_other) THEN        change_other = true;    ELSE
change_other= false;    END IF;END IF; 
IF (tg_op = ''INSERT'' AND new.my_cost IS NULL)OR change_other) THEN    new.my_cost = (        SELECT my_other_cost
  FROM my_other_table        WHERE id = new.id_other    );END IF;RETURN NEW; 
END;
' LANGUAGE 'plpgsql';

CREATE TRIGGER copy_cost__trigger    BEFORE INSERT OR UPDATE ON my_table    FOR EACH ROW    EXECUTE PROCEDURE
copy_cost();