Re: [PATCHES] extension for sql update
Am Donnerstag, den 27.07.2006, 08:30 -0400 schrieb Tom Lane: > 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. Look at my intention, why I wrote this patch: In recent years I migrated many customers applications from oracle or informix to postgresql. Every time it was a very painful and annoying job to grep through the code of functions and the whole software, to find all updates and change them manually. Far ago at university, I learned both syntax as standard syntax. Example: set a=1, b=2, c=3 and set (a,b,c)=(1,2,3) I admit, I prefered the second form, too, when I only used informix and it seems also my customers do so. Still now, I never found this syntax with select statement. I am not sure if this is possible with informix or oracle. regards Susanne > > regards, tom lane signature.asc Description: This is a digitally signed message part
Re: [PATCHES] extension for sql update
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. Agreed. My guess is that a soluion that allows SELECT to return multiple values is going to be in another area of the code, and will require us to remove this code once that is done. -- Bruce Momjian [EMAIL PROTECTED] EnterpriseDBhttp://www.enterprisedb.com + If your life is a hard drive, Christ can be your backup. + ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [PATCHES] extension for sql update
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. regards, tom lane ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [PATCHES] extension for sql update
Tom Lane wrote: > much anything that can generate a row. The patch as you have it > provides nothing more than syntactic sugar for something people can do > anyway. The reason people want this syntax is that they expect to be > able to write, say, > > UPDATE mytab SET (foo, bar, baz) = > (SELECT alpha, beta, gamma FROM othertab WHERE key = mytab.key); > > and with something like that you can't break apart the row-valued > expression in the grammar. So in reality the feature has to propagate > much further into the backend than this. 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. -- Bruce Momjian [EMAIL PROTECTED] EnterpriseDBhttp://www.enterprisedb.com + If your life is a hard drive, Christ can be your backup. + ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [PATCHES] extension for sql update
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. regards, tom lane ---(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: [PATCHES] extension for sql update
Am Mittwoch, den 26.07.2006, 16:58 -0400 schrieb Tom Lane: > Susanne Ebrecht <[EMAIL PROTECTED]> writes: > This is a cute hack, but it does only a small part of what I think the > spec says. Thank you for compliment. > > In the first place, the SQL syntax is pretty clear that you can combine > simple and multiple assignment in the same UPDATE: Ups, I asked about mixed syntax and I missunderstood the answer (I thougt there is nothing spezified about mixed syntax). But fixing this, seems not to be difficult. > The patch doesn't do that, but it wouldn't be too hard to fix. The more > serious problem is that > > ::= > > and is supposed to be pretty > much anything that can generate a row. The patch as you have it > provides nothing more than syntactic sugar for something people can do > anyway. The reason people want this syntax is that they expect to be > able to write, say, > > UPDATE mytab SET (foo, bar, baz) = > (SELECT alpha, beta, gamma FROM othertab WHERE key = mytab.key); > > and with something like that you can't break apart the row-valued > expression in the grammar. So in reality the feature has to propagate > much further into the backend than this. This seems to be difficult and I'm not sure this could be done until feature freeze. We could provide the mixed update syntax and leave the typed row value expression for the next release. Do you agree? regards Susanne Ebrecht signature.asc Description: This is a digitally signed message part
Re: [PATCHES] extension for sql update
Susanne Ebrecht <[EMAIL PROTECTED]> writes:
> here is a patch that extends update syntax following the sql standard.
> The patch includes sgml documentation, too.
> UPDATE table SET (col1, col2, ...) = (val1, val2, ...),
> (colm, coln, ...) = (valm, valn, ...), ...;
This is a cute hack, but it does only a small part of what I think the
spec says.
In the first place, the SQL syntax is pretty clear that you can combine
simple and multiple assignment in the same UPDATE:
::=
UPDATE
SET
[ WHERE ]
::=
[ { }... ]
::=
|
::=
::=
[ { } ... ]
The patch doesn't do that, but it wouldn't be too hard to fix. The more
serious problem is that
::=
and is supposed to be pretty
much anything that can generate a row. The patch as you have it
provides nothing more than syntactic sugar for something people can do
anyway. The reason people want this syntax is that they expect to be
able to write, say,
UPDATE mytab SET (foo, bar, baz) =
(SELECT alpha, beta, gamma FROM othertab WHERE key = mytab.key);
and with something like that you can't break apart the row-valued
expression in the grammar. So in reality the feature has to propagate
much further into the backend than this.
regards, tom lane
---(end of broadcast)---
TIP 3: Have you checked our extensive FAQ?
http://www.postgresql.org/docs/faq
