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 <da...@fetter.org> http://fetter.org/ Phone: +1 415 235 3778 AIM: dfetter666 Yahoo!: dfetter Skype: davidfetter XMPP: david.fet...@gmail.com Remember to vote! Consider donating to Postgres: http://www.postgresql.org/about/donate -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
