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
User-Agent: KMail/1.12.2 (Linux/2.6.31-19-generic; KDE/4.3.2; x86_64; ; )
References: <201002251619.32048.auryn@wanadoo.es>
	<3702.1267111736@sss.pgh.pa.us>
In-Reply-To: <3702.1267111736@sss.pgh.pa.us>
MIME-Version: 1.0
Content-Type: Text/Plain;
  charset="iso-8859-15"
Content-Transfer-Encoding: quoted-printable
Message-Id: <201003011730.05471.auryn@wanadoo.es>

On Jueves 25 Febrero 2010 16:28:56 usted escribi=F3:
> 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.
>=20
> "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.
>=20
> 			regards, tom lane
>=20
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 =3D ''UPDATE'') THEN
		IF (new.id_other <> old.id_other) THEN
			change_other =3D true;
		ELSE
			change_other =3D false;
		END IF;
	END IF;

	IF (tg_op =3D ''INSERT'' AND new.my_cost IS NULL)
	OR change_other) THEN
		new.my_cost =3D (
			SELECT my_other_cost
			FROM my_other_table
			WHERE id =3D 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();