UPDATE .. RETURNING OLD.*
Hi everyone,
Today I needed a feature like $subject. The use case was: UPDATE foo SET
bar = bar + 1 WHERE id=$1, but I wanted to only do it when bar was 0. In
order to give the user an informative error message, I also needed to
distinguish the two cases: a row with id = $1 doesn't exist, and bar was
0, so I couldn't put bar != 0 into the WHERE clause. This time I got
around it by using RETURNING bar and checking that it was 1 on the
client side, but I can come up with other cases where you can't do that.
Comments?
Regards,
Marko Tiikkaja
On Fri, Aug 28, 2009 at 06:12:30PM +0300, Marko Tiikkaja wrote:
Hi everyone,
Today I needed a feature like $subject. The use case was: UPDATE
foo SET bar = bar + 1 WHERE id=$1, but I wanted to only do it when
bar was 0. In order to give the user an informative error message,
I also needed to distinguish the two cases: a row with id = $1
doesn't exist, and bar was 0, so I couldn't put bar != 0 into the
WHERE clause. This time I got around it by using RETURNING bar and
checking that it was 1 on the client side, but I can come up with
other cases where you can't do that.Comments?
We talked about this briefly in IRC last night, and since that's not
recorded, I'd like to mention a few things here:
* OLD is already a reserved word. We could use it without fear of a
badly named database object.
* Having access to both the old and new row could make debugging
complex UPDATE queries much easier.
* There's some interesting use cases if the UPDATE...RETURNING can
also be used as a subquery. Auditing would be one.
Cheers,
David.
--
David Fetter <david@fetter.org> http://fetter.org/
Phone: +1 415 235 3778 AIM: dfetter666 Yahoo!: dfetter
Skype: davidfetter XMPP: david.fetter@gmail.com
Remember to vote!
Consider donating to Postgres: http://www.postgresql.org/about/donate