From: Tom Lane <tgl@sss.pgh.pa.us>
To: Aron <auryn@wanadoo.es>
Cc: pgsql-sql@postgresql.org
Subject: Re: Using new.id in the WHERE condition of one UPDATE inside ON INSERT rule
Date: Thu, 25 Feb 2010 10:28:56 -0500
Message-ID: <3702.1267111736@sss.pgh.pa.us> (raw)
In-Reply-To: <201002251619.32048.auryn@wanadoo.es>
References: <201002251619.32048.auryn@wanadoo.es>
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
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: tgl@sss.pgh.pa.us, auryn@wanadoo.es
Subject: Re: Using new.id in the WHERE condition of one UPDATE inside ON INSERT rule
In-Reply-To: <3702.1267111736@sss.pgh.pa.us>
* 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