[SQL] 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
--
Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-sql
Re: [SQL] Uniform UPDATE queries
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
--
Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-sql
Re: [SQL] Uniform UPDATE queries
Dennis 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 = , z = z where ... There's no reason to invent nonstandard syntax for this. regards, tom lane -- Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql
[SQL] plpgsql : adding record variable to table
hi, say I have the following (simplified for discussion) pl/pgsql function: FUNCTION myfunction ( ...) RETURNS TABLE ( elem1 integer, elem2 text, ...) DECLARE g RECORD BEGIN FOR g in SELECT colum1, column2 FROM someTable LOOP -- Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql
[SQL] plpgsql : adding record variable to table
(sorry my previous email was truncated) hi, Here is what I want to do : I want to check each row of a table against some conditions (this check needs some processing stuff I can easily code with pl/pgsql). If the row is OK, I want to add it in a "resulting table", else I just ignore the current row and go to next one. My function looks like this : (simplified) FUNCTION myfunction (...) RETURNS TABLE ( elem1 , elem2, elem3 ...) DECLARE g RECORD BEGIN FOR g in SELECT colum1, column2, ... FROM someTable LOOP -- do some processing on "g", then decide wheter I want to select it or not IF (g is selected) THEN >>add g to resulting_table<< END LOOP RETURN resulting_table How should I write the "add g to resulting table" part ? thanks, Tom -- Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql
Re: [SQL] plpgsql : adding record variable to table
Hello please try: postgres=# create or replace function foo() returns void as $$ declare r x; begin for r in select * from x loop insert into y values(r.*); end loop; end; $$ language plpgsql; Regards Pavel 2012/4/18 thomas veymont : > (sorry my previous email was truncated) > > hi, > > Here is what I want to do : > > I want to check each row of a table against some conditions (this > check needs some > processing stuff I can easily code with pl/pgsql). > > If the row is OK, I want to add it in a "resulting table", > else I just ignore the current row and go to next one. > > My function looks like this : (simplified) > > FUNCTION myfunction (...) RETURNS TABLE ( elem1 , elem2, elem3 ...) > DECLARE > g RECORD > BEGIN > FOR g in SELECT colum1, column2, ... FROM someTable > LOOP > -- do some processing on "g", then decide wheter I want to > select it or not > IF (g is selected) THEN >>add g to resulting_table<< > END LOOP > RETURN resulting_table > > How should I write the "add g to resulting table" part ? > > thanks, > Tom > > -- > Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org) > To make changes to your subscription: > http://www.postgresql.org/mailpref/pgsql-sql -- Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql
