From: Aron <auryn@wanadoo.es>
To: pgsql-sql@postgresql.org
Subject: Re: Using new.id in the WHERE condition of one UPDATE inside ON INSERT rule
Date: Mon, 1 Mar 2010 17:30:05 +0100
Message-ID: <201003011730.05471.auryn@wanadoo.es> (raw)
In-Reply-To: <3702.1267111736@sss.pgh.pa.us>
References: <201002251619.32048.auryn@wanadoo.es>
<3702.1267111736@sss.pgh.pa.us>
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 '
DECLARE
change_other BOOL;
BEGIN
IF (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();
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: <201003011730.05471.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