From: Rob Sargent <robjsargent@gmail.com>
To: pgsql-sql@postgresql.org
Subject: Re: Uniform UPDATE queries
Date: Wed, 18 Apr 2012 09:10:43 -0600
Message-ID: <4F8ED973.8050305@gmail.com> (raw)
In-Reply-To: <4F8E936A.9050303@victorem.com>
References: <4F8E936A.9050303@victorem.com>
On 04/18/2012 04:11 AM, Dennis wrote:
> When a query is written to update a table, the usual process is to list
> all the columns that need updating. This could imply the creation of
> many possible queries for many columns. In an effort to keep the UPDATE
> queries more uniform, less number of unique queries, a keyword similar
> to DEFAULT, let's say CURRENT, is required to indicate that the current
> value must not change.
>
> Examples:
>
> update mytable set ( d ) = ("newvalue")
>
> This is the usual way to change values in column "d" and requires
> writing a new query for updating every column.
>
> update mytable set ( a, b, c, d ) = ( a, b, c, "newvalue" )
>
> This sort of works to change only column "d", but requires explicit
> naming of the columns on the value side.
>
> My suggestion is to introduce the CURRENT keyword:
>
> update mytable set ( a, b, c, d ) = ( CURRENT, CURRENT, CURRENT,
> "newvalue" )
>
> This could then lead to the uniform prepared JDBC statement:
>
> update mytable set ( a, b, c, d ) = ( ?, ?, ?, ? ) where id = ( ? );
>
> And then the JDBC driver could be improved to accept stmt.setString( 4,
> "newvalue" ) and automagically substitute the first three parameters
> with CURRENT when the query is executed. Note the added WHERE clause?
> The parameter for id is always on the same index. This makes the
> bookkeeping a lot easier and should reduce the need for generating
> UPDATE queries or even client JDBC code.
>
> -- Dennis Verbeek
>
Isn't this sort of shenanigans best left "one level up"? The client/app
code construct the requisite update statement since it knows which
actual columns need updating (i.e. have dirty values). This is actually
quite straight forward when using O/R mapping tools such as hibernate or
toplink (or whatever oracle calls it now).
rjs
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: robjsargent@gmail.com
Subject: Re: Uniform UPDATE queries
In-Reply-To: <4F8ED973.8050305@gmail.com>
* 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