Skip site navigation (1) Skip section navigation (2)


From: David Fetter <david(at)fetter(dot)org>
To: Marko Tiikkaja <marko(dot)tiikkaja(at)cs(dot)helsinki(dot)fi>
Cc: PostgreSQL development <pgsql-hackers(at)postgresql(dot)org>
Date: 2009-08-28 18:52:23
Message-ID: (view raw or whole thread)
Lists: pgsql-hackers
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.

David Fetter <david(at)fetter(dot)org>
Phone: +1 415 235 3778  AIM: dfetter666  Yahoo!: dfetter
Skype: davidfetter      XMPP: david(dot)fetter(at)gmail(dot)com

Remember to vote!
Consider donating to Postgres:

In response to

pgsql-hackers by date

Next:From: Simon RiggsDate: 2009-08-28 19:07:32
Subject: LWLock Queue Jumping
Previous:From: Greg Sabino MullaneDate: 2009-08-28 18:16:12
Subject: Add YAML option to explain

Privacy Policy | About PostgreSQL
Copyright © 1996-2015 The PostgreSQL Global Development Group