Thread: Uniform UPDATE queries

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

On Fri, 2012-04-13 at 20:42 +0200, 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.
> 

I guess you didn't send it to the right mailing list. This list is about
pgAdmin support.


-- 
Guillaume
http://blog.guillaume.lelarge.info
http://www.dalibo.com