Re: [HACKERS] [PATCHES] extension for sql update
Bruce Momjian wrote: Susanne Ebrecht wrote: Is it too hard to rip it back out once the full row support arrives? That seems speculation at best anyway. That's what I was thinking. Glad someone else replied. ;-) If you're looking for votes, +1. I'll gladly take a subset of the SQL standard UPDATE table SET (...) = (...) over having nothing. +1 here, too. :) +1 I am working now to get this into 8.2. I am glad to read this. But what does it mean to me? Shall I change the patch someway? I have merged your patch into current CVS and applied it; attached. There was quite a bit of code drift. One drift area was the new RETURNING clause; that was easy to fix. A more complex case is the code no longer has values as ResTargets --- it is a simple a_expr list, so I changed the critical assignment in gram.y from: res_col-val = (Node *)copyObject(res_val-val); to: res_col-val = (Node *)copyObject(res_val); Hope that is OK. Without that fix, it crashed. I also merged your SGML syntax and grammer addition into the exiting UPDATE main entry. Of course it is ok. Many thanks. Susanne ---(end of broadcast)--- TIP 9: In versions below 8.0, the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
Re: [HACKERS] [PATCHES] extension for sql update
Susanne Ebrecht wrote:
Is it too hard to rip it back out once the full row support
arrives? That seems speculation at best anyway.
That's what I was thinking. Glad someone else replied. ;-)
If you're looking for votes, +1. I'll gladly take a subset of the
SQL standard UPDATE table SET (...) = (...) over having nothing.
+1 here, too. :)
+1
I am working now to get this into 8.2.
I am glad to read this. But what does it mean to me? Shall I change the
patch someway?
I have merged your patch into current CVS and applied it; attached.
There was quite a bit of code drift. One drift area was the new
RETURNING clause; that was easy to fix. A more complex case is the
code no longer has values as ResTargets --- it is a simple a_expr list,
so I changed the critical assignment in gram.y from:
res_col-val = (Node *)copyObject(res_val-val);
to:
res_col-val = (Node *)copyObject(res_val);
Hope that is OK. Without that fix, it crashed. I also merged your SGML
syntax and grammer addition into the exiting UPDATE main entry.
--
Bruce Momjian [EMAIL PROTECTED]
EnterpriseDBhttp://www.enterprisedb.com
+ If your life is a hard drive, Christ can be your backup. +
Index: doc/src/sgml/ref/update.sgml
===
RCS file: /cvsroot/pgsql/doc/src/sgml/ref/update.sgml,v
retrieving revision 1.38
retrieving revision 1.39
diff -c -r1.38 -r1.39
*** doc/src/sgml/ref/update.sgml 12 Aug 2006 02:52:03 - 1.38
--- doc/src/sgml/ref/update.sgml 2 Sep 2006 20:34:47 - 1.39
***
*** 21,27
refsynopsisdiv
synopsis
UPDATE [ ONLY ] replaceable class=PARAMETERtable/replaceable [ [ AS ] replaceable class=parameteralias/replaceable ]
! SET replaceable class=PARAMETERcolumn/replaceable = { replaceable class=PARAMETERexpression/replaceable | DEFAULT } [, ...]
[ FROM replaceable class=PARAMETERfromlist/replaceable ]
[ WHERE replaceable class=PARAMETERcondition/replaceable ]
[ RETURNING * | replaceable class=parameteroutput_expression/replaceable [ AS replaceable class=parameteroutput_name/replaceable ] [, ...] ]
--- 21,28
refsynopsisdiv
synopsis
UPDATE [ ONLY ] replaceable class=PARAMETERtable/replaceable [ [ AS ] replaceable class=parameteralias/replaceable ]
! [ SET replaceable class=PARAMETERcolumn/replaceable = { replaceable class=PARAMETERexpression/replaceable | DEFAULT } [, ...] |
! SET ( replaceable class=PARAMETERcolumn/replaceable [, ...] ) = ( { replaceable class=PARAMETERexpression/replaceable | DEFAULT } [, ...] ) [, ...] ]
[ FROM replaceable class=PARAMETERfromlist/replaceable ]
[ WHERE replaceable class=PARAMETERcondition/replaceable ]
[ RETURNING * | replaceable class=parameteroutput_expression/replaceable [ AS replaceable class=parameteroutput_name/replaceable ] [, ...] ]
***
*** 251,256
--- 252,261
UPDATE weather SET temp_lo = temp_lo+1, temp_hi = temp_lo+15, prcp = DEFAULT
WHERE city = 'San Francisco' AND date = '2003-07-03';
/programlisting
+ programlisting
+ UPDATE weather SET (temp_lo, temp_hi, prcp) = (temp_lo+1, temp_lo+15, DEFAULT)
+ WHERE city = 'San Francisco' AND date = '2003-07-03';
+ /programlisting
/para
para
Index: src/backend/parser/gram.y
===
RCS file: /cvsroot/pgsql/src/backend/parser/gram.y,v
retrieving revision 2.560
retrieving revision 2.562
diff -c -r2.560 -r2.562
*** src/backend/parser/gram.y 2 Sep 2006 18:17:17 - 2.560
--- src/backend/parser/gram.y 2 Sep 2006 20:52:01 - 2.562
***
*** 237,243
name_list from_clause from_list opt_array_bounds
qualified_name_list any_name any_name_list
any_operator expr_list attrs
! target_list update_target_list insert_column_list
values_list def_list indirection opt_indirection
group_clause TriggerFuncArgs select_limit
opt_select_limit opclass_item_list
--- 237,244
name_list from_clause from_list opt_array_bounds
qualified_name_list any_name any_name_list
any_operator expr_list attrs
! target_list update_col_list update_target_list
! update_value_list set_opt insert_column_list
values_list def_list indirection opt_indirection
group_clause TriggerFuncArgs select_limit
opt_select_limit opclass_item_list
***
*** 308,314
%type jexpr joined_table
%type range relation_expr
%type range relation_expr_opt_alias
! %type target target_el update_target_el insert_column_item
%type typnam Typename SimpleTypename ConstTypename
GenericType Numeric opt_float
--- 309,316
%type jexpr joined_table
%type range relation_expr
%type range relation_expr_opt_alias
! %type target target_el update_target_el update_col_list_el insert_column_item
! %type list update_target_lists_list
Re: [HACKERS] [PATCHES] extension for sql update
bruce wrote: I have merged your patch into current CVS and applied it; attached. There was quite a bit of code drift. One drift area was the new RETURNING clause; that was easy to fix. A more complex case is the code no longer has values as ResTargets --- it is a simple a_expr list, so I changed the critical assignment in gram.y from: res_col-val = (Node *)copyObject(res_val-val); to: res_col-val = (Node *)copyObject(res_val); Hope that is OK. Without that fix, it crashed. I also merged your SGML syntax and grammer addition into the exiting UPDATE main entry. The copyObject() is not required. Removed. -- Bruce Momjian [EMAIL PROTECTED] EnterpriseDBhttp://www.enterprisedb.com + If your life is a hard drive, Christ can be your backup. + ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [HACKERS] [PATCHES] extension for sql update
On Sun, Jul 30, 2006 at 08:38:30PM -0400, Rod Taylor wrote: On Sun, 2006-07-30 at 20:20 -0400, Robert Treat wrote: On Thursday 27 July 2006 09:28, Bruce Momjian wrote: Tom Lane wrote: Bruce Momjian [EMAIL PROTECTED] writes: Tom Lane wrote: UPDATE mytab SET (foo, bar, baz) = (SELECT alpha, beta, gamma FROM othertab WHERE key = mytab.key); That UPDATE example is interesting because I remember when using Informix that I had to do a separate SELECT statement for each UPDATE column I wanted to update. I didn't realize that you could group columns and assign them from a single select --- clearly that is a powerful syntax we should support some day. No question. The decision at hand is whether we want to look like we support it, when we don't yet. I'd vote not, because I think the main use-case for the row-on-the-left syntax is exactly this, and so I fear people will just get frustrated if they see it in the syntax synopsis and try to use it. I'm not a big fan of implementing partial solutions (remember left-joins are not implemented messages :-) way back when) , however in my experience with this form of the update command, the primary usage is not to use a subselect to derive the values, but to make it easier to generate sql, using a single I disagree. UPDATE mytab SET (foo, bar, baz) =(SELECT ...) is the specifications way of doing an update with a join. That is its primary purpose. UPDATE ... FROM is a PostgreSQL alternative to the above. An alternative that people have been using without complaint for years (probably because a number of other databases do the same thing). Perhaps a good compromise would be to allow UPDATE ... (SELECT) where it would meet the current requirements for UPDATE ... FROM. -- Jim C. Nasby, Sr. Engineering Consultant [EMAIL PROTECTED] Pervasive Software http://pervasive.comwork: 512-231-6117 vcard: http://jim.nasby.net/pervasive.vcf cell: 512-569-9461 ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [HACKERS] [PATCHES] extension for sql update
On Thursday 27 July 2006 09:28, Bruce Momjian wrote:
Tom Lane wrote:
Bruce Momjian [EMAIL PROTECTED] writes:
Tom Lane wrote:
UPDATE mytab SET (foo, bar, baz) =
(SELECT alpha, beta, gamma FROM othertab WHERE key = mytab.key);
That UPDATE example is interesting because I remember when using
Informix that I had to do a separate SELECT statement for each UPDATE
column I wanted to update. I didn't realize that you could group
columns and assign them from a single select --- clearly that is a
powerful syntax we should support some day.
No question. The decision at hand is whether we want to look like
we support it, when we don't yet. I'd vote not, because I think the
main use-case for the row-on-the-left syntax is exactly this, and
so I fear people will just get frustrated if they see it in the
syntax synopsis and try to use it.
I'm not a big fan of implementing partial solutions (remember left-joins are
not implemented messages :-) way back when) , however in my experience with
this form of the update command, the primary usage is not to use a subselect
to derive the values, but to make it easier to generate sql, using a single
update statement, based on an array of passed in values (in languages like
perl/php/etc...). This solution would solve that problem for us, so I would
lean toward including it. I would be interested in hearing from actual users
who really need the subselect version though, but right now my thinking is
that group is a small minority of who would benefit from this version of the
update command.
--
Robert Treat
Build A Brighter LAMP :: Linux Apache {middleware} PostgreSQL
---(end of broadcast)---
TIP 4: Have you searched our list archives?
http://archives.postgresql.org
Re: [HACKERS] [PATCHES] extension for sql update
On Sun, 2006-07-30 at 20:20 -0400, Robert Treat wrote: On Thursday 27 July 2006 09:28, Bruce Momjian wrote: Tom Lane wrote: Bruce Momjian [EMAIL PROTECTED] writes: Tom Lane wrote: UPDATE mytab SET (foo, bar, baz) = (SELECT alpha, beta, gamma FROM othertab WHERE key = mytab.key); That UPDATE example is interesting because I remember when using Informix that I had to do a separate SELECT statement for each UPDATE column I wanted to update. I didn't realize that you could group columns and assign them from a single select --- clearly that is a powerful syntax we should support some day. No question. The decision at hand is whether we want to look like we support it, when we don't yet. I'd vote not, because I think the main use-case for the row-on-the-left syntax is exactly this, and so I fear people will just get frustrated if they see it in the syntax synopsis and try to use it. I'm not a big fan of implementing partial solutions (remember left-joins are not implemented messages :-) way back when) , however in my experience with this form of the update command, the primary usage is not to use a subselect to derive the values, but to make it easier to generate sql, using a single I disagree. UPDATE mytab SET (foo, bar, baz) =(SELECT ...) is the specifications way of doing an update with a join. That is its primary purpose. UPDATE ... FROM is a PostgreSQL alternative to the above. -- ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [HACKERS] [PATCHES] extension for sql update
On Jul 27, 2006, at 7:30 AM, Tom Lane wrote: Susanne Ebrecht [EMAIL PROTECTED] writes: ... We could provide the mixed update syntax and leave the typed row value expression for the next release. Do you agree? I don't really see the point --- the patch won't provide any new functionality in anything like its current form, because you can always just write the separate expressions in the simple one to one way. If we do offer the row-on-the-left syntax then people will try to put sub-selects on the right, and won't get anything beyond an unhelpful syntax error message. So my vote would be to leave it alone until we have a more complete implementation. While the patch doesn't provide any new functionality, I would still welcome it simply because I find it a lot easier and cleaner to group fields together when updating multiple fields at once. Even if we would have to rip this patch back out in order to fully support the spec, we've got a (mostly) working patch right now, and it sounds like it would take minimal effort to finish it. In any case, it sounds like there should be a TODO item out of this. -- Jim C. Nasby, Sr. Engineering Consultant [EMAIL PROTECTED] Pervasive Software http://pervasive.comwork: 512-231-6117 vcard: http://jim.nasby.net/pervasive.vcf cell: 512-569-9461 ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [HACKERS] [PATCHES] extension for sql update
Tom Lane wrote: Susanne Ebrecht [EMAIL PROTECTED] writes: ... We could provide the mixed update syntax and leave the typed row value expression for the next release. Do you agree? I don't really see the point --- the patch won't provide any new functionality in anything like its current form, because you can always just write the separate expressions in the simple one to one way. If we do offer the row-on-the-left syntax then people will try to put sub-selects on the right, and won't get anything beyond an unhelpful syntax error message. So my vote would be to leave it alone until we have a more complete implementation. It has the advantage that inserts and updates look more alike. If your sql statements are generated by code, then that removes the need of a special case for updates. greetings, Florian Pflug ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
Re: [HACKERS] [PATCHES] extension for sql update
Jim Nasby wrote: On Jul 27, 2006, at 7:30 AM, Tom Lane wrote: Susanne Ebrecht [EMAIL PROTECTED] writes: ... We could provide the mixed update syntax and leave the typed row value expression for the next release. Do you agree? I don't really see the point --- the patch won't provide any new functionality in anything like its current form, because you can always just write the separate expressions in the simple one to one way. If we do offer the row-on-the-left syntax then people will try to put sub-selects on the right, and won't get anything beyond an unhelpful syntax error message. So my vote would be to leave it alone until we have a more complete implementation. While the patch doesn't provide any new functionality, I would still welcome it simply because I find it a lot easier and cleaner to group fields together when updating multiple fields at once. Even if we would have to rip this patch back out in order to fully support the spec, we've got a (mostly) working patch right now, and it sounds like it would take minimal effort to finish it. In any case, it sounds like there should be a TODO item out of this. We already had it on the TODO list, but I didn't realize about the subselect issue. I added a sentence to clarify that: o Allow UPDATE tab SET ROW (col, ...) = (...) for updating multiple columns new--A subselect can also be used as the value source. -- Bruce Momjian [EMAIL PROTECTED] EnterpriseDBhttp://www.enterprisedb.com + If your life is a hard drive, Christ can be your backup. + ---(end of broadcast)--- TIP 6: explain analyze is your friend
