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/

---------------------------(end of broadcast)---------------------------
TIP 5: don't forget to increase your free space map settings

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));

--


---------------------------(end of broadcast)---------------------------
TIP 3: Have you checked our extensive FAQ?

http://www.postgresql.org/docs/faq

Peter Eisentraut <peter_e@gmx.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

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@gmx.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@momjian.us
EnterpriseDB http://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

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/

---------------------------(end of broadcast)---------------------------
TIP 1: if posting/reading through Usenet, please send an appropriate
subscribe-nomail command to majordomo@postgresql.org so that your
message can get through to the mailing list cleanly

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@momjian.us
EnterpriseDB http://www.enterprisedb.com

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

---------------------------(end of broadcast)---------------------------
TIP 4: Have you searched our list archives?

http://archives.postgresql.org

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@pervasive.com
Pervasive Software http://pervasive.com work: 512-231-6117
vcard: http://jim.nasby.net/pervasive.vcf cell: 512-569-9461

---------------------------(end of broadcast)---------------------------
TIP 1: if posting/reading through Usenet, please send an appropriate
subscribe-nomail command to majordomo@postgresql.org so that your
message can get through to the mailing list cleanly

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@fetter.org> http://fetter.org/
phone: +1 415 235 3778 AIM: dfetter666
Skype: davidfetter

Remember to vote!

---------------------------(end of broadcast)---------------------------
TIP 1: if posting/reading through Usenet, please send an appropriate
subscribe-nomail command to majordomo@postgresql.org so that your
message can get through to the mailing list cleanly

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

---------------------------(end of broadcast)---------------------------
TIP 1: if posting/reading through Usenet, please send an appropriate
subscribe-nomail command to majordomo@postgresql.org so that your
message can get through to the mailing list cleanly

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@momjian.us
EnterpriseDB http://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