Re: [PATCHES] extension for sql update

Hi,

here is a patch that extends update syntax following the sql standard.
The patch includes sgml documentation, too.

For example:

UPDATE table SET (col1, col2, ...) = (val1, val2, ...),
(colm, coln, ...) = (valm, valn, ...), ...;

Susanne

Susanne Ebrecht <miracee(at)miracee(dot)de> 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 statement: searched> ::=
UPDATE <target table>
SET <set clause list>
[ WHERE <search condition> ]

<set clause list> ::=
<set clause> [ { <comma> <set clause> }... ]

<set clause> ::=
<multiple column assignment>
| <set target> <equals operator> <update source>

<multiple column assignment> ::=
<set target list> <equals operator> <assigned row>

<set target list> ::=
<left paren> <set target> [ { <comma> <set target> } ... ] <right paren>

The patch doesn't do that, but it wouldn't be too hard to fix. The more
serious problem is that

<assigned row> ::= <contextually typed row value expression>

and <contextually typed row value expression> 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

Am Mittwoch, den 26.07.2006, 16:58 -0400 schrieb Tom Lane:
> Susanne Ebrecht <miracee(at)miracee(dot)de> 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
>
> <assigned row> ::= <contextually typed row value expression>
>
> and <contextually typed row value expression> 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

Susanne Ebrecht <miracee(at)miracee(dot)de> 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

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 bruce(at)momjian(dot)us
EnterpriseDB http://www.enterprisedb.com

+ If your life is a hard drive, Christ can be your backup. +

Bruce Momjian <bruce(at)momjian(dot)us> 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

Tom Lane wrote:
> Bruce Momjian <bruce(at)momjian(dot)us> 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 bruce(at)momjian(dot)us
EnterpriseDB http://www.enterprisedb.com

+ If your life is a hard drive, Christ can be your backup. +

On Jul 27, 2006, at 7:30 AM, Tom Lane wrote:
> Susanne Ebrecht <miracee(at)miracee(dot)de> 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 jnasby(at)pervasive(dot)com
Pervasive Software http://pervasive.com work: 512-231-6117
vcard: http://jim.nasby.net/pervasive.vcf cell: 512-569-9461

Tom Lane wrote:
> Susanne Ebrecht <miracee(at)miracee(dot)de> 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

Jim Nasby wrote:
> On Jul 27, 2006, at 7:30 AM, Tom Lane wrote:
> > Susanne Ebrecht <miracee(at)miracee(dot)de> 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 bruce(at)momjian(dot)us
EnterpriseDB http://www.enterprisedb.com

+ If your life is a hard drive, Christ can be your backup. +

Am Donnerstag, den 27.07.2006, 08:30 -0400 schrieb Tom Lane:
> Susanne Ebrecht <miracee(at)miracee(dot)de> 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

On Thursday 27 July 2006 09:28, Bruce Momjian wrote:
> Tom Lane wrote:
> > Bruce Momjian <bruce(at)momjian(dot)us> 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

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 <bruce(at)momjian(dot)us> 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.
--

Am Mittwoch, 26. Juli 2006 22:58 schrieb Tom Lane:
> 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);

I don't find any derivation in the standard that would permit this. The only
thing I could find are variations on

SET (a) = x -- no parentheses
SET (a, b) = (x, y)
SET (a, b) = ROW (x, y)

where x and y are some sort of value expression. I would have expected the
sort of thing that you describe, but if you know how to derive that, I'd like
to see it.

--
Peter Eisentraut
http://developer.postgresql.org/~petere/

On Mon, 2006-07-31 at 17:26 +0200, Peter Eisentraut wrote:
> Am Mittwoch, 26. Juli 2006 22:58 schrieb Tom Lane:
> > 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);
>
> I don't find any derivation in the standard that would permit this. The only
> thing I could find are variations on
>
> SET (a) = x -- no parentheses
> SET (a, b) = (x, y)
> SET (a, b) = ROW (x, y)
>
> where x and y are some sort of value expression. I would have expected the
> sort of thing that you describe, but if you know how to derive that, I'd like
> to see it.

I believe <contextually typed row value constructor element list> can be
one or more <value expressions> which includes a <row value expression>.
<row value expression> gives us the <row subquery> option.

For that matter the below portion of <contextually typed row value
constructor> gives us:
| <left paren> <contextually typed row value constructor element>
<comma>
<contextually typed row value constructor element list> <right
paren>

This breaks down into one or more comma separated <row subquery>s.

UPDATE tab SET (...) = ((SELECT foo, bar from a), (select bif,baz from
b));

--

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 <bruce(at)momjian(dot)us> 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 jnasby(at)pervasive(dot)com
Pervasive Software http://pervasive.com work: 512-231-6117
vcard: http://jim.nasby.net/pervasive.vcf cell: 512-569-9461

Peter Eisentraut <peter_e(at)gmx(dot)net> writes:
> Am Mittwoch, 26. Juli 2006 22:58 schrieb Tom Lane:
>> 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);

> I don't find any derivation in the standard that would permit this.

Well, there are two ways to get there. SQL99 does not actually have the
syntax with parentheses on the left, but what it does have is SET ROW:

<set clause> ::=
<update target> <equals operator> <update source>
| <mutated set clause> <equals operator> <update source>

<update target> ::=
<object column>
| ROW
| <object column>
<left bracket or trigraph> <simple value specification> <right bracket or trigraph>

<update source> ::=
<value expression>
| <contextually typed value specification>

and you can derive (SELECT ...) from <value expression> via

<value expression> ::=
...
| <row value expression>

<row value expression> ::=
...
| <row value constructor>

<row value constructor> ::=
...
| <row subquery>

<row subquery> ::= <subquery>

<subquery> ::=
<left paren> <query expression> <right paren>

<query expression> ::=
[ <with clause> ] <query expression body>

<query expression body> ::=
<non-join query expression>

<non-join query expression> ::=
<non-join query term>

<non-join query term> ::=
<non-join query primary>

<non-join query primary> ::=
<simple table>

<simple table> ::=
<query specification>

<query specification> ::=
SELECT [ <set quantifier> ] <select list>
<table expression>

Another interesting restriction in SQL99 is

9) If an <update target> specifies ROW, then:

a) <set clause list> shall consist of exactly one <set clause>
SC.

SQL2003 seems to have dropped the ROW syntax entirely, but instead they
have

<set clause> ::= <multiple column assignment>

<multiple column assignment> ::=
<set target list> <equals operator> <assigned row>

<assigned row> ::= <contextually typed row value expression>

and from there it goes through just like before.

regards, tom lane

Are we sure we don't want the patch for a non-subquery version of SET
ROW for 8.2?

o Allow UPDATE tab SET ROW (col, ...) = (...) for updating multiple
columns

---------------------------------------------------------------------------

Tom Lane wrote:
> Peter Eisentraut <peter_e(at)gmx(dot)net> writes:
> > Am Mittwoch, 26. Juli 2006 22:58 schrieb Tom Lane:
> >> 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);
>
> > I don't find any derivation in the standard that would permit this.
>
> Well, there are two ways to get there. SQL99 does not actually have the
> syntax with parentheses on the left, but what it does have is SET ROW:
>
> <set clause> ::=
> <update target> <equals operator> <update source>
> | <mutated set clause> <equals operator> <update source>
>
> <update target> ::=
> <object column>
> | ROW
> | <object column>
> <left bracket or trigraph> <simple value specification> <right bracket or trigraph>
>
> <update source> ::=
> <value expression>
> | <contextually typed value specification>
>
> and you can derive (SELECT ...) from <value expression> via
>
> <value expression> ::=
> ...
> | <row value expression>
>
> <row value expression> ::=
> ...
> | <row value constructor>
>
> <row value constructor> ::=
> ...
> | <row subquery>
>
> <row subquery> ::= <subquery>
>
> <subquery> ::=
> <left paren> <query expression> <right paren>
>
> <query expression> ::=
> [ <with clause> ] <query expression body>
>
> <query expression body> ::=
> <non-join query expression>
>
> <non-join query expression> ::=
> <non-join query term>
>
> <non-join query term> ::=
> <non-join query primary>
>
> <non-join query primary> ::=
> <simple table>
>
> <simple table> ::=
> <query specification>
>
> <query specification> ::=
> SELECT [ <set quantifier> ] <select list>
> <table expression>
>
> Another interesting restriction in SQL99 is
>
> 9) If an <update target> specifies ROW, then:
>
> a) <set clause list> shall consist of exactly one <set clause>
> SC.
>
> SQL2003 seems to have dropped the ROW syntax entirely, but instead they
> have
>
> <set clause> ::= <multiple column assignment>
>
> <multiple column assignment> ::=
> <set target list> <equals operator> <assigned row>
>
> <assigned row> ::= <contextually typed row value expression>
>
> and from there it goes through just like before.
>
> regards, tom lane
>
> ---------------------------(end of broadcast)---------------------------
> TIP 4: Have you searched our list archives?
>
> http://archives.postgresql.org

--
Bruce Momjian bruce(at)momjian(dot)us
EnterpriseDB http://www.enterprisedb.com

+ If your life is a hard drive, Christ can be your backup. +

Bruce Momjian wrote:
> Are we sure we don't want the patch for a non-subquery version of SET
> ROW for 8.2?
>
> o Allow UPDATE tab SET ROW (col, ...) = (...) for updating
> multiple columns

It seems to be moderately useful as a notational convenience for now.

Is it too hard to rip it back out once the full row support arrives?
That seems speculation at best anyway.

--
Peter Eisentraut
http://developer.postgresql.org/~petere/

Peter Eisentraut wrote:
> Bruce Momjian wrote:
> > Are we sure we don't want the patch for a non-subquery version of SET
> > ROW for 8.2?
> >
> > o Allow UPDATE tab SET ROW (col, ...) = (...) for updating
> > multiple columns
>
> It seems to be moderately useful as a notational convenience for now.
>
> 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. ;-)

--
Bruce Momjian bruce(at)momjian(dot)us
EnterpriseDB http://www.enterprisedb.com

+ If your life is a hard drive, Christ can be your backup. +

On Fri, Aug 11, 2006 at 10:59:45AM -0400, Bruce Momjian wrote:
> Peter Eisentraut wrote:
> > Bruce Momjian wrote:
> > > Are we sure we don't want the patch for a non-subquery version of SET
> > > ROW for 8.2?
> > >
> > > o Allow UPDATE tab SET ROW (col, ...) = (...) for updating
> > > multiple columns
> >
> > It seems to be moderately useful as a notational convenience for now.
> >
> > 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.
--
Jim C. Nasby, Sr. Engineering Consultant jnasby(at)pervasive(dot)com
Pervasive Software http://pervasive.com work: 512-231-6117
vcard: http://jim.nasby.net/pervasive.vcf cell: 512-569-9461

On Fri, Aug 11, 2006 at 05:11:03PM -0500, Jim C. Nasby wrote:
> On Fri, Aug 11, 2006 at 10:59:45AM -0400, Bruce Momjian wrote:
> > Peter Eisentraut wrote:
> > > Bruce Momjian wrote:
> > > > Are we sure we don't want the patch for a non-subquery version of SET
> > > > ROW for 8.2?
> > > >
> > > > o Allow UPDATE tab SET ROW (col, ...) = (...) for updating
> > > > multiple columns
> > >
> > > It seems to be moderately useful as a notational convenience for
> > > now.
> > >
> > > 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. :)

Cheers,
D
--
David Fetter <david(at)fetter(dot)org> http://fetter.org/
phone: +1 415 235 3778 AIM: dfetter666
Skype: davidfetter

Remember to vote!

On Saturday 12 August 2006 16:16, David Fetter wrote:
> On Fri, Aug 11, 2006 at 05:11:03PM -0500, Jim C. Nasby wrote:
> > On Fri, Aug 11, 2006 at 10:59:45AM -0400, Bruce Momjian wrote:
> > > Peter Eisentraut wrote:
> > > > Bruce Momjian wrote:
> > > > > Are we sure we don't want the patch for a non-subquery version of
> > > > > SET ROW for 8.2?
> > > > >
> > > > > o Allow UPDATE tab SET ROW (col, ...) = (...) for updating
> > > > > multiple columns
> > > >
> > > > It seems to be moderately useful as a notational convenience for
> > > > now.
> > > >
> > > > 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

--
Robert Treat
Build A Brighter LAMP :: Linux Apache {middleware} PostgreSQL

Robert Treat wrote:
> On Saturday 12 August 2006 16:16, David Fetter wrote:
> > On Fri, Aug 11, 2006 at 05:11:03PM -0500, Jim C. Nasby wrote:
> > > On Fri, Aug 11, 2006 at 10:59:45AM -0400, Bruce Momjian wrote:
> > > > Peter Eisentraut wrote:
> > > > > Bruce Momjian wrote:
> > > > > > Are we sure we don't want the patch for a non-subquery version of
> > > > > > SET ROW for 8.2?
> > > > > >
> > > > > > o Allow UPDATE tab SET ROW (col, ...) = (...) for updating
> > > > > > multiple columns
> > > > >
> > > > > It seems to be moderately useful as a notational convenience for
> > > > > now.
> > > > >
> > > > > 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.

--
Bruce Momjian bruce(at)momjian(dot)us
EnterpriseDB http://www.enterprisedb.com

+ If your life is a hard drive, Christ can be your backup. +

Bruce Momjian wrote:
> Robert Treat wrote:
>
>> On Saturday 12 August 2006 16:16, David Fetter wrote:
>>
>>> On Fri, Aug 11, 2006 at 05:11:03PM -0500, Jim C. Nasby wrote:
>>>
>>>> On Fri, Aug 11, 2006 at 10:59:45AM -0400, Bruce Momjian wrote:
>>>>
>>>>> Peter Eisentraut wrote:
>>>>>
>>>>>> Bruce Momjian wrote:
>>>>>>
>>>>>>> Are we sure we don't want the patch for a non-subquery version of
>>>>>>> SET ROW for 8.2?
>>>>>>>
>>>>>>> o Allow UPDATE tab SET ROW (col, ...) = (...) for updating
>>>>>>> multiple columns
>>>>>>>
>>>>>> It seems to be moderately useful as a notational convenience for
>>>>>> now.
>>>>>>
>>>>>> 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?

Susanne

Susanne Ebrecht wrote:
> >>>>> 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?

Well, it doesn't apply to CVS HEAD. I assume it was made for 8.1.X. I
can adjust it and apply it, or you can make the changes and send in a
new version.

--
Bruce Momjian bruce(at)momjian(dot)us
EnterpriseDB http://www.enterprisedb.com

+ If your life is a hard drive, Christ can be your backup. +

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 bruce(at)momjian(dot)us
EnterpriseDB http://www.enterprisedb.com

+ If your life is a hard drive, Christ can be your backup. +

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