pg.ddx.io pgsql-sql@postgresql.org mailing list archive
help / color / mirror / Atom feedUniform UPDATE queries
5+ messages / 3 participants
[nested] [flat]
* Uniform UPDATE queries
@ 2012-04-18 10:11 Dennis <dennis.verbeek@victorem.com>
2012-04-18 15:10 ` Re: Uniform UPDATE queries Rob Sargent <robjsargent@gmail.com>
2012-04-18 16:24 ` Re: Uniform UPDATE queries Tom Lane <tgl@sss.pgh.pa.us>
0 siblings, 2 replies; 5+ messages in thread
From: Dennis @ 2012-04-18 10:11 UTC (permalink / raw)
To: pgsql-sql
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
^ permalink raw reply [nested|flat] 5+ messages in thread
* Re: Uniform UPDATE queries
2012-04-18 10:11 Uniform UPDATE queries Dennis <dennis.verbeek@victorem.com>
@ 2012-04-18 15:10 ` Rob Sargent <robjsargent@gmail.com>
1 sibling, 0 replies; 5+ messages in thread
From: Rob Sargent @ 2012-04-18 15:10 UTC (permalink / raw)
To: pgsql-sql
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
^ permalink raw reply [nested|flat] 5+ messages in thread
* Re: Uniform UPDATE queries
2012-04-18 10:11 Uniform UPDATE queries Dennis <dennis.verbeek@victorem.com>
@ 2012-04-18 16:24 ` Tom Lane <tgl@sss.pgh.pa.us>
2012-04-19 10:55 ` Re: Uniform UPDATE queries Dennis <dennis.verbeek@victorem.com>
1 sibling, 1 reply; 5+ messages in thread
From: Tom Lane @ 2012-04-18 16:24 UTC (permalink / raw)
To: Dennis <dennis.verbeek@victorem.com>; +Cc: pgsql-sql
Dennis <dennis.verbeek@victorem.com> writes:
> 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.
No it isn't. Just write the name of the column, eg
update mytable set x = x, y = <new value>, z = z where ...
There's no reason to invent nonstandard syntax for this.
regards, tom lane
^ permalink raw reply [nested|flat] 5+ messages in thread
* Re: Uniform UPDATE queries
2012-04-18 10:11 Uniform UPDATE queries Dennis <dennis.verbeek@victorem.com>
2012-04-18 16:24 ` Re: Uniform UPDATE queries Tom Lane <tgl@sss.pgh.pa.us>
@ 2012-04-19 10:55 ` Dennis <dennis.verbeek@victorem.com>
2012-04-19 18:32 ` Re: Uniform UPDATE queries Rob Sargent <robjsargent@gmail.com>
0 siblings, 1 reply; 5+ messages in thread
From: Dennis @ 2012-04-19 10:55 UTC (permalink / raw)
To: Tom Lane <tgl@sss.pgh.pa.us>; +Cc: pgsql-sql
Hello Tom,
The example you have given is EXACTLY why something like CURRENT is needed to limit the number of
unique queries or prepared statements. (or to do a selection of all values before an update meaning
two executed queries.)
regards,.
Dennis
On 04/18/2012 06:24 PM, Tom Lane wrote:
> Dennis<dennis.verbeek@victorem.com> writes:
>> 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.
> No it isn't. Just write the name of the column, eg
>
> update mytable set x = x, y =<new value>, z = z where ...
>
> There's no reason to invent nonstandard syntax for this.
>
> regards, tom lane
>
>
>
^ permalink raw reply [nested|flat] 5+ messages in thread
* Re: Uniform UPDATE queries
2012-04-18 10:11 Uniform UPDATE queries Dennis <dennis.verbeek@victorem.com>
2012-04-18 16:24 ` Re: Uniform UPDATE queries Tom Lane <tgl@sss.pgh.pa.us>
2012-04-19 10:55 ` Re: Uniform UPDATE queries Dennis <dennis.verbeek@victorem.com>
@ 2012-04-19 18:32 ` Rob Sargent <robjsargent@gmail.com>
0 siblings, 0 replies; 5+ messages in thread
From: Rob Sargent @ 2012-04-19 18:32 UTC (permalink / raw)
To: pgsql-sql
On 04/19/2012 04:55 AM, Dennis wrote:
> Hello Tom,
>
> The example you have given is EXACTLY why something like CURRENT is
> needed to limit the number of unique queries or prepared statements. (or
> to do a selection of all values before an update meaning two executed
> queries.)
>
> regards,.
>
> Dennis
>
> On 04/18/2012 06:24 PM, Tom Lane wrote:
>> Dennis<dennis.verbeek@victorem.com> writes:
>>> 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.
>> No it isn't. Just write the name of the column, eg
>>
>> update mytable set x = x, y =<new value>, z = z where ...
>>
>> There's no reason to invent nonstandard syntax for this.
>>
>> regards, tom lane
>>
>>
>>
>
>
Not if you have all the old and new values:
update mytable set x= xval, y=yval, z=zval where yval happens to be a
new value and xval, zval are current.
Seems your callers knows which have changed. Does it not know which have
not (and what their values are)?
^ permalink raw reply [nested|flat] 5+ messages in thread
end of thread, other threads:[~2012-04-19 18:32 UTC | newest]
Thread overview: 5+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2012-04-18 10:11 Uniform UPDATE queries Dennis <dennis.verbeek@victorem.com>
2012-04-18 15:10 ` Rob Sargent <robjsargent@gmail.com>
2012-04-18 16:24 ` Tom Lane <tgl@sss.pgh.pa.us>
2012-04-19 10:55 ` Dennis <dennis.verbeek@victorem.com>
2012-04-19 18:32 ` Rob Sargent <robjsargent@gmail.com>
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