Re: request for sql3 compliance for the update command

Simple script:
CREATE TABLE test(a int);
\copy test from '/tmp/wow'
select * from test;

now produce error:
\copy: COPY state must be terminated first

I missed something?

--
Teodor Sigaev
teodor(at)stack(dot)net

I have a large customer who is converting from informix to postgres and
they have made extensive use of

update table set (col...) = ( val...)

as a first pass would it be possible to translate this in the parser to

update table set col=val

It would appear that this is SQL3 compliant

<set clause> ::=
<update target> <equals operator> <row value designator>

<update target> ::=
<object column>
| <left paren> <object column list> <right paren>

or can someone think of another way?
--
Dave Cramer <dave(at)fastcrypt(dot)com>
Cramer Consulting

Bruce,

Can you chime in with your support here?

Dave

I have a large customer who is converting from informix to postgres and
they have made extensive use of

update table set (col...) = ( val...)

as a first pass would it be possible to translate this in the parser to

update table set col=val

It would appear that this is SQL3 compliant

<set clause> ::=
<update target> <equals operator> <row value designator>

<update target> ::=
<object column>
| <left paren> <object column list> <right paren>

or can someone think of another way?
--
Dave Cramer <dave(at)fastcrypt(dot)com>
Cramer Consulting

Dave Cramer writes:

> update table set (col...) = ( val...)

> It would appear that this is SQL3 compliant
>
> <set clause> ::=
> <update target> <equals operator> <row value designator>
>
> <update target> ::=
> <object column>
> | <left paren> <object column list> <right paren>

That's not what my copy says.

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

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

<object column> ::= <column name>

<mutated set clause> ::=
<mutated target> <period> <method name>

<mutated target> ::=
<object column>
| <mutated set clause>

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

(And I'm pretty sure I have the right version of the standard.)

--
Peter Eisentraut peter_e(at)gmx(dot)net

Dave Cramer <dave(at)fastcrypt(dot)com> writes:
> It would appear that this is SQL3 compliant

> <set clause> ::=
> <update target> <equals operator> <row value designator>

> <update target> ::=
> <object column>
> | <left paren> <object column list> <right paren>

I see no parentheses allowed there in the SQL99 spec. Encourage your
customer to use standard syntax.

regards, tom lane

Peter,

Referring to

http://src.doc.ic.ac.uk/packages/dbperl/refinfo/sql3/sql3bnf.sep93.txt

the following grammar exists

is the reference above valid?

as for tom's reply there are left paren, and right paren.

Dave
On Wed, 2003-02-19 at 10:37, Peter Eisentraut wrote:
> Dave Cramer writes:
>
> > update table set (col...) = ( val...)
>
> > It would appear that this is SQL3 compliant
> >
> > <set clause> ::=
> > <update target> <equals operator> <row value designator>
> >
> > <update target> ::=
> > <object column>
> > | <left paren> <object column list> <right paren>
>
> That's not what my copy says.
>
> <set clause list> ::=
> <set clause> [ { <comma> <set clause> }... ]
>
> <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>
>
> <object column> ::= <column name>
>
>
> <mutated set clause> ::=
> <mutated target> <period> <method name>
>
> <mutated target> ::=
> <object column>
> | <mutated set clause>
>
> <update source> ::=
> <value expression>
> | <contextually typed value specification>
>
> (And I'm pretty sure I have the right version of the standard.)
--
Dave Cramer <dave(at)fastcrypt(dot)com>
Cramer Consulting

Dave Cramer <dave(at)fastcrypt(dot)com> writes:
> Referring to
> http://src.doc.ic.ac.uk/packages/dbperl/refinfo/sql3/sql3bnf.sep93.txt
> the following grammar exists
> is the reference above valid?

Sep 93? That would be an extremely early draft of what eventually became
SQL99. Looks like the parens got lost again by the time of the final
spec.

Given that there's no visible functionality gain from allowing parens
here, I'm not surprised that the spec authors decided it wasn't such
a hot idea after all... too bad Informix didn't get the word :-(

regards, tom lane

Ok, if a patch were submitted to the parser to allow the syntax in
question would it be considered?

Dave
On Wed, 2003-02-19 at 12:29, Tom Lane wrote:
> Dave Cramer <dave(at)fastcrypt(dot)com> writes:
> > Referring to
> > http://src.doc.ic.ac.uk/packages/dbperl/refinfo/sql3/sql3bnf.sep93.txt
> > the following grammar exists
> > is the reference above valid?
>
> Sep 93? That would be an extremely early draft of what eventually became
> SQL99. Looks like the parens got lost again by the time of the final
> spec.
>
> Given that there's no visible functionality gain from allowing parens
> here, I'm not surprised that the spec authors decided it wasn't such
> a hot idea after all... too bad Informix didn't get the word :-(
>
> regards, tom lane
>
> ---------------------------(end of broadcast)---------------------------
> TIP 6: Have you searched our list archives?
>
> http://archives.postgresql.org
--
Dave Cramer <dave(at)fastcrypt(dot)com>
Cramer Consulting

Dave Cramer <dave(at)fastcrypt(dot)com> writes:
> Ok, if a patch were submitted to the parser to allow the syntax in
> question would it be considered?

I would vote against it ... but that's only one vote.

regards, tom lane

On Wed, Feb 19, 2003 at 07:31:35AM -0500, Dave Cramer wrote:
> Bruce,
>
> Can you chime in with your support here?
>
> Dave
>
> I have a large customer who is converting from informix to postgres and
> they have made extensive use of
>
> update table set (col...) = ( val...)
>
> as a first pass would it be possible to translate this in the parser to
>
> update table set col=val
>
> It would appear that this is SQL3 compliant
>
> <set clause> ::=
> <update target> <equals operator> <row value designator>
>
> <update target> ::=
> <object column>
> | <left paren> <object column list> <right paren>
>
>
> or can someone think of another way?

I don't understand the original problem. What does informix give you? A
text file full of "update table set ()=()" which you then try to feed
into postgres? In that case, why not pass said text file through a sed or
perl script first?

Cheers,

Patrick

Patrick,

No, they support the syntax:

update table set (col1, col2, col3) = ( val1, val2, val3 )

I have a customer with a rather large application which uses this
syntax, because they were using informix. There is also a rather
interesting 4GL project called aubit which is on sourceforge. They would
also like to see this supported for the same reasons.

Dave

On Wed, 2003-02-19 at 15:02, Patrick Welche wrote:
> > I have a large customer who is converting from informix to postgres and
> > they have made extensive use of
> >
> > update table set (col...) = ( val...)
> >
> > as a first pass would it be possible to translate this in the parser to
> >
> > update table set col=val
> >
> > It would appear that this is SQL3 compliant
> >
> > <set clause> ::=
> > <update target> <equals operator> <row value designator>
> >
> > <update target> ::=
> > <object column>
> > | <left paren> <object column list> <right paren>
> >
> >
> > or can someone think of another way?
>
> I don't understand the original problem. What does informix give you? A
> text file full of "update table set ()=()" which you then try to feed
> into postgres? In that case, why not pass said text file through a sed or
> perl script first?
>
> Cheers,
>
> Patrick
--
Dave Cramer <dave(at)fastcrypt(dot)com>
Cramer Consulting

While I don't see the syntax of:

update table set (col...) = ( val...)

as valuable compared to separate col=val assignments, I do see a value
in allowing subqueries in such assignments:

update table set (col...) = ( select val ..)

Without it, you have to do separate subquery statements, and if they are
complex, that is a waste. I assume that was the motivation for the
feature.

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

Dave Cramer wrote:
> Patrick,
>
> No, they support the syntax:
>
> update table set (col1, col2, col3) = ( val1, val2, val3 )
>
> I have a customer with a rather large application which uses this
> syntax, because they were using informix. There is also a rather
> interesting 4GL project called aubit which is on sourceforge. They would
> also like to see this supported for the same reasons.
>
> Dave
>
>
> On Wed, 2003-02-19 at 15:02, Patrick Welche wrote:
> > > I have a large customer who is converting from informix to postgres and
> > > they have made extensive use of
> > >
> > > update table set (col...) = ( val...)
> > >
> > > as a first pass would it be possible to translate this in the parser to
> > >
> > > update table set col=val
> > >
> > > It would appear that this is SQL3 compliant
> > >
> > > <set clause> ::=
> > > <update target> <equals operator> <row value designator>
> > >
> > > <update target> ::=
> > > <object column>
> > > | <left paren> <object column list> <right paren>
> > >
> > >
> > > or can someone think of another way?
> >
> > I don't understand the original problem. What does informix give you? A
> > text file full of "update table set ()=()" which you then try to feed
> > into postgres? In that case, why not pass said text file through a sed or
> > perl script first?
> >
> > Cheers,
> >
> > Patrick
> --
> Dave Cramer <dave(at)fastcrypt(dot)com>
> Cramer Consulting
>
>
> ---------------------------(end of broadcast)---------------------------
> TIP 5: Have you checked our extensive FAQ?
>
> http://www.postgresql.org/users-lounge/docs/faq.html
>

--
Bruce Momjian | http://candle.pha.pa.us
pgman(at)candle(dot)pha(dot)pa(dot)us | (610) 359-1001
+ If your life is a hard drive, | 13 Roberts Road
+ Christ can be your backup. | Newtown Square, Pennsylvania 19073

On Wednesday 19 February 2003 8:18 pm, Dave Cramer wrote:
> I have a customer with a rather large application which uses this
> syntax, because they were using informix. There is also a rather
> interesting 4GL project called aubit which is on sourceforge. They would
> also like to see this supported for the same reasons.

Hey - I was going to say that...

For the curious:
Quick URL - http://aubit4gl.sourceforge.net/

Its a 'clone' of the Informix 4GL tool, a nice 'clean' language specifically
designed for writing database applications, with both curses & GTK, support
for multiple database types and a bunch of other things...

We're about to release version 0.30 - and I was going to wait until then....

> Dave Cramer <dave(at)fastcrypt(dot)com> writes:
> > Ok, if a patch were submitted to the parser to allow the syntax in
> > question would it be considered?
>
> I would vote against it ... but that's only one vote.

I would vote against it too. Please don't add a none-standard syntax
any more (I think we got enough lesson from "foo = NULL compatibilty"
with MS products).
--
Tatsuo Ishii

Tom Lane wrote:
> Dave Cramer <dave(at)fastcrypt(dot)com> writes:
>
>>Ok, if a patch were submitted to the parser to allow the syntax in
>>question would it be considered?
>
>
> I would vote against it ... but that's only one vote.

As a thought, will it add significant maintenance penalties or be
detrimental?

There seem to be quite a lot of Informix people moving to PostgreSQL
these days, moreso than Oracle shops. Might have been brought on by
IBM's purchase of Informix.

Wondering if this one change be a significant improvement in regards to
making it easier to migrate, or just a minor thing?

Regards and best wishes,

Justin Clift

> regards, tom lane

--
"My grandfather once told me that there are two kinds of people: those
who work and those who take the credit. He told me to try to be in the
first group; there was less competition there."
- Indira Gandhi

> While I don't see the syntax of:
>
> update table set (col...) = ( val...)
>
> as valuable compared to separate col=val assignments, I do see a value
> in allowing subqueries in such assignments:
>
> update table set (col...) = ( select val ..)
>
> Without it, you have to do separate subquery statements, and if they are
> complex, that is a waste. I assume that was the motivation for the
> feature.

The number of times I've needed this feature... :)

Chris

After a long battle with technology,mike(at)aubit(dot)com (Mike Aubury), an earthling, wrote:
> On Wednesday 19 February 2003 8:18 pm, Dave Cramer wrote:
>> I have a customer with a rather large application which uses this
>> syntax, because they were using informix. There is also a rather
>> interesting 4GL project called aubit which is on sourceforge. They would
>> also like to see this supported for the same reasons.
>
> Hey - I was going to say that...
>
> For the curious:
> Quick URL - http://aubit4gl.sourceforge.net/

> Its a 'clone' of the Informix 4GL tool, a nice 'clean' language
> specifically designed for writing database applications, with both
> curses & GTK, support for multiple database types and a bunch of
> other things...

> We're about to release version 0.30 - and I was going to wait until
> then....

I tried it out a while back; couldn't get it to compile, probably due
to there being a bit too much 'bleeding' to the 'bleeding edge.'

It looks as though it could be pretty interesting, if PG support
matures (which can certainly be a two way street!).

How's the cross-platform support? Aubit would be an easier sell, to
be sure, if it is readily deployable on Those Other Platforms, too...
--
(concatenate 'string "cbbrowne" "@acm.org")
http://www.ntlug.org/~cbbrowne/multiplexor.html
"MSDOS didn't get as bad as it is overnight -- it took over ten years
of careful development." -- <dmeggins(at)aix1(dot)uottawa(dot)ca>

Justin,

This is certainly the case here. I think IBM is deprecating informix,
and many informix users are being forced to make a change, and they are
seriously considering postgres as an alternative.

It behooves us to look at aubit http://aubit4gl.sourceforge.net/ before
making this decision as well.

I believe the aubit project has the potential to move postgres forward
considerably as well.

Dave

On Wed, 2003-02-19 at 21:08, Justin Clift wrote:
> Tom Lane wrote:
> > Dave Cramer <dave(at)fastcrypt(dot)com> writes:
> >
> >>Ok, if a patch were submitted to the parser to allow the syntax in
> >>question would it be considered?
> >
> >
> > I would vote against it ... but that's only one vote.
>
> As a thought, will it add significant maintenance penalties or be
> detrimental?
>
> There seem to be quite a lot of Informix people moving to PostgreSQL
> these days, moreso than Oracle shops. Might have been brought on by
> IBM's purchase of Informix.
>
> Wondering if this one change be a significant improvement in regards to
> making it easier to migrate, or just a minor thing?
>
> Regards and best wishes,
>
> Justin Clift
>
>
> > regards, tom lane
--
Dave Cramer <dave(at)fastcrypt(dot)com>
Cramer Consulting

On 19 Feb 2003, Dave Cramer wrote:

> Justin,
>
> This is certainly the case here. I think IBM is deprecating informix,
> and many informix users are being forced to make a change, and they are
> seriously considering postgres as an alternative.

Do you have any evidence that they are evaluating it?

Gavin

Dave, Justin,

I have several Informix clients who will be moving to a Postgresql/Aubit4gl
solution at some point. The Informix line is, for them, a dead end. One
way or another the backend will become Postgresql. Because of the number of
SQL statements, I would encourage support where possible and reasonable.

Jordan

----- Original Message -----
From: "Dave Cramer" <dave(at)fastcrypt(dot)com>
To: "Justin Clift" <justin(at)postgresql(dot)org>
Cc: "Tom Lane" <tgl(at)sss(dot)pgh(dot)pa(dot)us>; "Peter Eisentraut" <peter_e(at)gmx(dot)net>;
"Pgsql Hackers" <pgsql-hackers(at)postgresql(dot)org>
Sent: Wednesday, February 19, 2003 10:18 PM
Subject: Re: [HACKERS] request for sql3 compliance for the update command

> Justin,
>
> This is certainly the case here. I think IBM is deprecating informix,
> and many informix users are being forced to make a change, and they are
> seriously considering postgres as an alternative.
>
> It behooves us to look at aubit http://aubit4gl.sourceforge.net/ before
> making this decision as well.
>
>
> I believe the aubit project has the potential to move postgres forward
> considerably as well.
>
> Dave
>
> On Wed, 2003-02-19 at 21:08, Justin Clift wrote:
> > Tom Lane wrote:
> > > Dave Cramer <dave(at)fastcrypt(dot)com> writes:
> > >
> > >>Ok, if a patch were submitted to the parser to allow the syntax in
> > >>question would it be considered?
> > >
> > >
> > > I would vote against it ... but that's only one vote.
> >
> > As a thought, will it add significant maintenance penalties or be
> > detrimental?
> >
> > There seem to be quite a lot of Informix people moving to PostgreSQL
> > these days, moreso than Oracle shops. Might have been brought on by
> > IBM's purchase of Informix.
> >
> > Wondering if this one change be a significant improvement in regards to
> > making it easier to migrate, or just a minor thing?
> >
> > Regards and best wishes,
> >
> > Justin Clift
> >
> >
> > > regards, tom lane
> --
> Dave Cramer <dave(at)fastcrypt(dot)com>
> Cramer Consulting
>
>
> ---------------------------(end of broadcast)---------------------------
> TIP 3: if posting/reading through Usenet, please send an appropriate
> subscribe-nomail command to majordomo(at)postgresql(dot)org so that your
> message can get through to the mailing list cleanly

Yes, the company in question is more than evaluating it; this request is
a result of a project to port their application to postgres.

Dave
On Wed, 2003-02-19 at 22:32, Gavin Sherry wrote:
> On 19 Feb 2003, Dave Cramer wrote:
>
> > Justin,
> >
> > This is certainly the case here. I think IBM is deprecating informix,
> > and many informix users are being forced to make a change, and they are
> > seriously considering postgres as an alternative.
>
> Do you have any evidence that they are evaluating it?
>
> Gavin
--
Dave Cramer <dave(at)fastcrypt(dot)com>
Cramer Consulting

Justin Clift <justin(at)postgresql(dot)org> writes:
> As a thought, will it add significant maintenance penalties or be
> detrimental?

Well, yes it will if you look at the big picture. In the past we've
generally regretted it when we put in nonstandard features just to be
compatible with some other database. (Tatsuo already pointed out the
"foo = NULL" fiasco.) And we get ragged on regularly for the non-SQL-
standard features we've inherited from Berkeley Postgres (eg, the
implicit-FROM frammish that was under discussion yesterday).

I don't think we're really doing the users any favor either. If they
want to move to some other database after Postgres, are they likely to
get that other database to insert a not-very-useful nonstandard syntax?
Sooner or later they're going to have to bite this bullet, and it may
as well be sooner. (I can hardly believe that this is the worst
compatibility issue an ex-Informix user would face, anyhow.)

This is an Informix-ism. It should stay that way.

regards, tom lane

Agreed folks are going to have bigger problems from Informix than just
this, and in fact I used Informix for years and didn't know they allowed
this.

However, what solution do we have for UPDATE (coll...) = (select val...)
for folks? It is awkward to repeat a query multiple times in an UPDATE.

I think it makes sense to add it only if it adds functionality.

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

Tom Lane wrote:
> Justin Clift <justin(at)postgresql(dot)org> writes:
> > As a thought, will it add significant maintenance penalties or be
> > detrimental?
>
> Well, yes it will if you look at the big picture. In the past we've
> generally regretted it when we put in nonstandard features just to be
> compatible with some other database. (Tatsuo already pointed out the
> "foo = NULL" fiasco.) And we get ragged on regularly for the non-SQL-
> standard features we've inherited from Berkeley Postgres (eg, the
> implicit-FROM frammish that was under discussion yesterday).
>
> I don't think we're really doing the users any favor either. If they
> want to move to some other database after Postgres, are they likely to
> get that other database to insert a not-very-useful nonstandard syntax?
> Sooner or later they're going to have to bite this bullet, and it may
> as well be sooner. (I can hardly believe that this is the worst
> compatibility issue an ex-Informix user would face, anyhow.)
>
> This is an Informix-ism. It should stay that way.
>
> regards, tom lane
>
> ---------------------------(end of broadcast)---------------------------
> TIP 3: if posting/reading through Usenet, please send an appropriate
> subscribe-nomail command to majordomo(at)postgresql(dot)org so that your
> message can get through to the mailing list cleanly
>

--
Bruce Momjian | http://candle.pha.pa.us
pgman(at)candle(dot)pha(dot)pa(dot)us | (610) 359-1001
+ If your life is a hard drive, | 13 Roberts Road
+ Christ can be your backup. | Newtown Square, Pennsylvania 19073

Bruce Momjian <pgman(at)candle(dot)pha(dot)pa(dot)us> writes:
> While I don't see the syntax of:
> update table set (col...) = ( val...)
> as valuable compared to separate col=val assignments, I do see a value
> in allowing subqueries in such assignments:
> update table set (col...) = ( select val ..)

Hm. That's at least got some defensibility to it. But does it do
anything that you can't already do with a join?

BTW, looking at the SQL99 standard, I see that you can do

UPDATE table SET ROW = foo WHERE ...

where foo is supposed to yield a row of the same rowtype as table
--- I didn't dig through the spec in detail, but I imagine foo can
be a sub-select. I don't care a whole lot for that, though, since it
would be a real pain in the neck if you're not updating all the columns.
You'd have to go

UPDATE table SET ROW = (SELECT table.a, table.b, foo.x, ... FROM foo)

which seems ugly, tedious, and error-prone.

regards, tom lane

On 19 Feb 2003, Dave Cramer wrote:

> Yes, the company in question is more than evaluating it; this request is
> a result of a project to port their application to postgres.

Ahh. I thought you were referring to IBM. That is, that IBM was evaluating
Postgres...

Gavin

> BTW, looking at the SQL99 standard, I see that you can do
>
> UPDATE table SET ROW = foo WHERE ...
>
> where foo is supposed to yield a row of the same rowtype as table
> --- I didn't dig through the spec in detail, but I imagine foo can
> be a sub-select. I don't care a whole lot for that, though, since it
> would be a real pain in the neck if you're not updating all the columns.
> You'd have to go
>
> UPDATE table SET ROW = (SELECT table.a, table.b, foo.x, ... FROM foo)

How is the Informix syntax any better?

Chris

Christopher Kings-Lynne wrote:
>
> > BTW, looking at the SQL99 standard, I see that you can do
> >
> > UPDATE table SET ROW = foo WHERE ...
> >
> > where foo is supposed to yield a row of the same rowtype as table
> > --- I didn't dig through the spec in detail, but I imagine foo can
> > be a sub-select. I don't care a whole lot for that, though, since it
> > would be a real pain in the neck if you're not updating all the columns.
> > You'd have to go
> >
> > UPDATE table SET ROW = (SELECT table.a, table.b, foo.x, ... FROM foo)
>
> How is the Informix syntax any better?

With Informix, you specify the columns you want updated in parens,
rather than saying ROW. Does the spec allow a list of columns after
ROW? That would be nice, like Informix. I doubt many folks update all
the columns.

--
Bruce Momjian | http://candle.pha.pa.us
pgman(at)candle(dot)pha(dot)pa(dot)us | (610) 359-1001
+ If your life is a hard drive, | 13 Roberts Road
+ Christ can be your backup. | Newtown Square, Pennsylvania 19073

On Wed, Feb 19, 2003 at 04:37:33PM +0100, Peter Eisentraut wrote:
> That's not what my copy says.

Strange. I just looked at all the docs I have and all have it listed the
way Dave wrote. So I seem to have to update my docs. Peter, could you
send me a copy?

Michael
--
Michael Meskes
Email: Michael(at)Fam-Meskes(dot)De
ICQ: 179140304
Go SF 49ers! Go Rhein Fire! Use Debian GNU/Linux! Use PostgreSQL!

On Wed, Feb 19, 2003 at 12:29:12PM -0500, Tom Lane wrote:
> SQL99. Looks like the parens got lost again by the time of the final
> spec.

I don't think the parens really matter. It's just the different ordering
of columns and values.

Michael
--
Michael Meskes
Email: Michael(at)Fam-Meskes(dot)De
ICQ: 179140304
Go SF 49ers! Go Rhein Fire! Use Debian GNU/Linux! Use PostgreSQL!

Tom Lane kirjutas K, 19.02.2003 kell 21:12:
> Dave Cramer <dave(at)fastcrypt(dot)com> writes:
> > Ok, if a patch were submitted to the parser to allow the syntax in
> > question would it be considered?
>
> I would vote against it ... but that's only one vote.

Are you against it just on grounds of cleanliness and ANSI compliance,
or do you see more serious problems in letting it in ?

-------------
Hannu

Bruce Momjian kirjutas N, 20.02.2003 kell 06:16:
> Agreed folks are going to have bigger problems from Informix than just
> this, and in fact I used Informix for years and didn't know they allowed
> this.
>
> However, what solution do we have for UPDATE (coll...) = (select val...)
> for folks? It is awkward to repeat a query multiple times in an UPDATE.

hannu=# create table target (id serial, a int, b int, c int);
NOTICE: CREATE TABLE will create implicit sequence 'target_id_seq' for
SERIAL column 'target.id'
CREATE TABLE
hannu=# insert into target(a,b,c) values (0,0,0);
INSERT 16983 1
hannu=# insert into target(a,b,c) values (1,1,1);
INSERT 16984 1
hannu=# update target set
hannu-# a = source.a1, b=source.a2, c=source.a3
hannu-# from (select 1 as a1, 2 as a2, 3 as a3 ) as source
hannu-# where id = 1
hannu-# ;
UPDATE 1
hannu=# select * from target;
id | a | b | c
----+---+---+---
2 | 1 | 1 | 1
1 | 1 | 2 | 3
(2 rows)

hannu=#

--------------
Hannu

Bruce Momjian kirjutas N, 20.02.2003 kell 06:16:
> Agreed folks are going to have bigger problems from Informix than just
> this, and in fact I used Informix for years and didn't know they allowed
> this.
>
> However, what solution do we have for UPDATE (coll...) = (select val...)
> for folks? It is awkward to repeat a query multiple times in an UPDATE.
>
> I think it makes sense to add it only if it adds functionality.

It makes it easier (less keystrokes) to write as well as similar in
appearance to INSERT, so the same code can be used to generate the
queries.

If we were at adding functionality then IMHO making VALUES(x,y,z) a
proper "rowsource" would be a more worthy effort.

---------------
Hannu

Hannu Krosing <hannu(at)tm(dot)ee> writes:
> Are you against it just on grounds of cleanliness and ANSI compliance,
> or do you see more serious problems in letting it in ?

At this point it seems there are two different things being tossed
about. I originally understood Dave to be asking for parens to be
allowed around individual target column names, which seems a useless
frammish to me. What Bruce has pointed out is that a syntax that lets
you assign multiple columns from a single rowsource would be an actual
improvement in functionality, or at least in convenience and efficiency.
(It would also be a substantial bit of work, which is why I think this
isn't what Dave was offering a quick patch to do...) What I'd like to
know right now is which interpretation Informix actually implements.

I don't like adding nonstandard syntaxes that add no functionality ---
but if Informix has done what Bruce is talking about, that's a different
matter altogether.

regards, tom lane

Hannu Krosing <hannu(at)tm(dot)ee> writes:
> Bruce Momjian kirjutas N, 20.02.2003 kell 06:16:
>> However, what solution do we have for UPDATE (coll...) = (select val...)
>> for folks? It is awkward to repeat a query multiple times in an UPDATE.

> hannu=# update target set
> hannu-# a = source.a1, b=source.a2, c=source.a3
> hannu-# from (select 1 as a1, 2 as a2, 3 as a3 ) as source
> hannu-# where id = 1
> hannu-# ;

I've been trying to think of a case that can't be handled by transposing
the sub-select into FROM. I'm not sure there are any. I thought for a
minute that grouped aggregates would be an issue. For example, suppose
table "totals" has one row for each distinct value of "groupid"
appearing in table "details", and you use it to store group aggregate
values. You can do

UPDATE totals SET
xmax = (SELECT max(x) FROM details WHERE groupid = totals.groupid),
xmin = (SELECT min(x) FROM details WHERE groupid = totals.groupid),
ymax = (SELECT max(y) FROM details WHERE groupid = totals.groupid),
ymin = (SELECT min(y) FROM details WHERE groupid = totals.groupid),
...

but that is awfully tedious and will be inefficiently implemented. This
is what Bruce is worried about. On the other hand, one could argue that
this is a wrongheaded way to go about it anyway, and the correct way is

UPDATE totals SET
xmax = ss.xmax, xmin = ss.xmin, ...
FROM
(SELECT groupid, max(x) AS xmax, ... FROM details GROUP BY groupid) ss
WHERE groupid = ss.groupid;

If there is indeed a row in "totals" for every groupid, then this will
certainly beat out the first approach that has to run a separate query
for each groupid, even if we avoid a separate query for each aggregate.
(It could maybe lose if you only wanted to update the totals for a few
groupids; but even then you could probably push the WHERE conditions
restricting the groups into the sub-select.)

Of course this syntax isn't standard either ... but we already have it.

Right now I'm not convinced there is a functionality argument for
supporting the Informix-style syntax, even with multiple columns.

regards, tom lane

On Thu, 20 Feb 2003, Tom Lane wrote:

> Hannu Krosing <hannu(at)tm(dot)ee> writes:
> > Are you against it just on grounds of cleanliness and ANSI compliance,
> > or do you see more serious problems in letting it in ?
>
> At this point it seems there are two different things being tossed
> about. I originally understood Dave to be asking for parens to be
> allowed around individual target column names, which seems a useless
> frammish to me. What Bruce has pointed out is that a syntax that lets
> you assign multiple columns from a single rowsource would be an actual
> improvement in functionality, or at least in convenience and efficiency.
> (It would also be a substantial bit of work, which is why I think this
> isn't what Dave was offering a quick patch to do...) What I'd like to
> know right now is which interpretation Informix actually implements.
>
> I don't like adding nonstandard syntaxes that add no functionality ---
> but if Informix has done what Bruce is talking about, that's a different
> matter altogether.

Tom, I was purusing the wild and wonderfully exciting new SQL

(found here:
ftp://sqlstandards.org/SC32/WG3/Progression_Documents/FCD/4FCD1-01-Framework-2002-01.pdf)

ANSI TC NCITS H2
ISO/IEC JTC 1/SC 32/WG 3
Database

document to see what it had to say, and on this subject, and it looks like
update is going to be supporing this same style we're discussing here.

Look on or around p. 858 in that doc.)

On Thu, Feb 20, 2003 at 09:31:21AM -0500, Tom Lane wrote:
> about. I originally understood Dave to be asking for parens to be
> allowed around individual target column names, which seems a useless
> frammish to me. What Bruce has pointed out is that a syntax that lets
> you assign multiple columns from a single rowsource would be an actual
> improvement in functionality, or at least in convenience and efficiency.
> (It would also be a substantial bit of work, which is why I think this
> isn't what Dave was offering a quick patch to do...) What I'd like to
> know right now is which interpretation Informix actually implements.

Informix syntax is listed on
http://www-3.ibm.com/software/data/informix/pubs/library/visionary/infoshelf/sqls/01start.fm.html#156200

It's more than just parens IMO. :-)

Michael
--
Michael Meskes
Email: Michael(at)Fam-Meskes(dot)De
ICQ: 179140304
Go SF 49ers! Go Rhein Fire! Use Debian GNU/Linux! Use PostgreSQL!

Scott,

I can't find page 858 in that document, is it the right one?

also the link s/b ?

ftp://ftp.sqlstandards.org/SC32/WG3/Progression_Documents/FCD/4FCD1-01-Framework-2002-01.pdf

Dave
On Thu, 2003-02-20 at 11:20, scott.marlowe wrote:
> On Thu, 20 Feb 2003, Tom Lane wrote:
>
> > Hannu Krosing <hannu(at)tm(dot)ee> writes:
> > > Are you against it just on grounds of cleanliness and ANSI compliance,
> > > or do you see more serious problems in letting it in ?
> >
> > At this point it seems there are two different things being tossed
> > about. I originally understood Dave to be asking for parens to be
> > allowed around individual target column names, which seems a useless
> > frammish to me. What Bruce has pointed out is that a syntax that lets
> > you assign multiple columns from a single rowsource would be an actual
> > improvement in functionality, or at least in convenience and efficiency.
> > (It would also be a substantial bit of work, which is why I think this
> > isn't what Dave was offering a quick patch to do...) What I'd like to
> > know right now is which interpretation Informix actually implements.
> >
> > I don't like adding nonstandard syntaxes that add no functionality ---
> > but if Informix has done what Bruce is talking about, that's a different
> > matter altogether.
>
> Tom, I was purusing the wild and wonderfully exciting new SQL
>
> (found here:
> ftp://sqlstandards.org/SC32/WG3/Progression_Documents/FCD/4FCD1-01-Framework-2002-01.pdf)
>
> ANSI TC NCITS H2
> ISO/IEC JTC 1/SC 32/WG 3
> Database
>
> document to see what it had to say, and on this subject, and it looks like
> update is going to be supporing this same style we're discussing here.
>
> Look on or around p. 858 in that doc.)
--
Dave Cramer <dave(at)fastcrypt(dot)com>
Cramer Consulting

Scott,

Thanks for the reference, I think the actual document is

ftp://ftp.sqlstandards.org/SC32/WG3/Progression_Documents/FCD/4FCD1-01-Framework-2002-01.pdf

and it is in section 14.12

on or about page 839

Dave
On Thu, 2003-02-20 at 11:20, scott.marlowe wrote:
> On Thu, 20 Feb 2003, Tom Lane wrote:
>
> > Hannu Krosing <hannu(at)tm(dot)ee> writes:
> > > Are you against it just on grounds of cleanliness and ANSI compliance,
> > > or do you see more serious problems in letting it in ?
> >
> > At this point it seems there are two different things being tossed
> > about. I originally understood Dave to be asking for parens to be
> > allowed around individual target column names, which seems a useless
> > frammish to me. What Bruce has pointed out is that a syntax that lets
> > you assign multiple columns from a single rowsource would be an actual
> > improvement in functionality, or at least in convenience and efficiency.
> > (It would also be a substantial bit of work, which is why I think this
> > isn't what Dave was offering a quick patch to do...) What I'd like to
> > know right now is which interpretation Informix actually implements.
> >
> > I don't like adding nonstandard syntaxes that add no functionality ---
> > but if Informix has done what Bruce is talking about, that's a different
> > matter altogether.
>
> Tom, I was purusing the wild and wonderfully exciting new SQL
>
> (found here:
> ftp://sqlstandards.org/SC32/WG3/Progression_Documents/FCD/4FCD1-01-Framework-2002-01.pdf)
>
> ANSI TC NCITS H2
> ISO/IEC JTC 1/SC 32/WG 3
> Database
>
> document to see what it had to say, and on this subject, and it looks like
> update is going to be supporing this same style we're discussing here.
>
> Look on or around p. 858 in that doc.)
--
Dave Cramer <dave(at)fastcrypt(dot)com>
Cramer Consulting

sorry, it's the -02 document.

just change the last 01 to 02 and you'll get the right one.

On 20 Feb 2003, Dave Cramer wrote:

> Scott,
>
> I can't find page 858 in that document, is it the right one?
>
> also the link s/b ?
>
> ftp://ftp.sqlstandards.org/SC32/WG3/Progression_Documents/FCD/4FCD1-01-Framework-2002-01.pdf
>
> Dave
> On Thu, 2003-02-20 at 11:20, scott.marlowe wrote:
> > On Thu, 20 Feb 2003, Tom Lane wrote:
> >
> > > Hannu Krosing <hannu(at)tm(dot)ee> writes:
> > > > Are you against it just on grounds of cleanliness and ANSI compliance,
> > > > or do you see more serious problems in letting it in ?
> > >
> > > At this point it seems there are two different things being tossed
> > > about. I originally understood Dave to be asking for parens to be
> > > allowed around individual target column names, which seems a useless
> > > frammish to me. What Bruce has pointed out is that a syntax that lets
> > > you assign multiple columns from a single rowsource would be an actual
> > > improvement in functionality, or at least in convenience and efficiency.
> > > (It would also be a substantial bit of work, which is why I think this
> > > isn't what Dave was offering a quick patch to do...) What I'd like to
> > > know right now is which interpretation Informix actually implements.
> > >
> > > I don't like adding nonstandard syntaxes that add no functionality ---
> > > but if Informix has done what Bruce is talking about, that's a different
> > > matter altogether.
> >
> > Tom, I was purusing the wild and wonderfully exciting new SQL
> >
> > (found here:
> > ftp://sqlstandards.org/SC32/WG3/Progression_Documents/FCD/4FCD1-01-Framework-2002-01.pdf)
> >
> > ANSI TC NCITS H2
> > ISO/IEC JTC 1/SC 32/WG 3
> > Database
> >
> > document to see what it had to say, and on this subject, and it looks like
> > update is going to be supporing this same style we're discussing here.
> >
> > Look on or around p. 858 in that doc.)
>

The right URL (I'll get it eventually) is

ftp://sqlstandards.org/SC32/WG3/Progression_Documents/FCD/4FCD1-02-Foundation-2002-01.pdf

That time I exactly copied the URL. sorry for the wrong one previously.

On 20 Feb 2003, Dave Cramer wrote:

> Scott,
>
> Thanks for the reference, I think the actual document is
>
> ftp://ftp.sqlstandards.org/SC32/WG3/Progression_Documents/FCD/4FCD1-01-Framework-2002-01.pdf
>
> and it is in section 14.12
>
>
> on or about page 839
>
> Dave
> On Thu, 2003-02-20 at 11:20, scott.marlowe wrote:
> > On Thu, 20 Feb 2003, Tom Lane wrote:
> >
> > > Hannu Krosing <hannu(at)tm(dot)ee> writes:
> > > > Are you against it just on grounds of cleanliness and ANSI compliance,
> > > > or do you see more serious problems in letting it in ?
> > >
> > > At this point it seems there are two different things being tossed
> > > about. I originally understood Dave to be asking for parens to be
> > > allowed around individual target column names, which seems a useless
> > > frammish to me. What Bruce has pointed out is that a syntax that lets
> > > you assign multiple columns from a single rowsource would be an actual
> > > improvement in functionality, or at least in convenience and efficiency.
> > > (It would also be a substantial bit of work, which is why I think this
> > > isn't what Dave was offering a quick patch to do...) What I'd like to
> > > know right now is which interpretation Informix actually implements.
> > >
> > > I don't like adding nonstandard syntaxes that add no functionality ---
> > > but if Informix has done what Bruce is talking about, that's a different
> > > matter altogether.
> >
> > Tom, I was purusing the wild and wonderfully exciting new SQL
> >
> > (found here:
> > ftp://sqlstandards.org/SC32/WG3/Progression_Documents/FCD/4FCD1-01-Framework-2002-01.pdf)
> >
> > ANSI TC NCITS H2
> > ISO/IEC JTC 1/SC 32/WG 3
> > Database
> >
> > document to see what it had to say, and on this subject, and it looks like
> > update is going to be supporing this same style we're discussing here.
> >
> > Look on or around p. 858 in that doc.)
>

Tom Lane wrote:
> UPDATE totals SET
> xmax = ss.xmax, xmin = ss.xmin, ...
> FROM
> (SELECT groupid, max(x) AS xmax, ... FROM details GROUP BY groupid) ss
> WHERE groupid = ss.groupid;

As long as any individual item that you can express in the
parenthesized (Informix) syntax can also be expressed as an element in
a SELECT, then the above is equivalent in every way to the Informix
syntax. And since SELECT allows subselects, it seems to me that the
PG syntax is complete.

My question is whether or not there's likely to be an approved
standard way of accomplishing what either syntax does. Is there
anything in the current draft that addresses this?

--
Kevin Brown kevin(at)sysexperts(dot)com

Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> writes:

> UPDATE totals SET
> xmax = (SELECT max(x) FROM details WHERE groupid = totals.groupid),
> ...
>
> but that is awfully tedious and will be inefficiently implemented. This
> is what Bruce is worried about. On the other hand, one could argue that
> this is a wrongheaded way to go about it anyway, and the correct way is
>
> UPDATE totals SET
> xmax = ss.xmax, xmin = ss.xmin, ...
> FROM
> (SELECT groupid, max(x) AS xmax, ... FROM details GROUP BY groupid) ss
> WHERE groupid = ss.groupid;
...
> Of course this syntax isn't standard either ... but we already have it.

This is nice, but I could see it being a big pain if the join clause wasn't so
neat and tidy as a groupid column that you can group by. The Informix syntax
has some appeal -- speaking from the point of view of someone who has had to
write some awkward update statements like this in the past. (In Oracle where
the best syntax is to create an updatable inline view which is pretty much
equivalent in expressiveness to the Postgres syntax.)

Consider how awkward this query would be if the iterations in the original
query overlapped for example. You would have to introduce a another table to
the select just to drive the join artificially.

For example consider a hypothetical case:

UPDATE networks set num_hosts = (select count(*) from hosts where addr << netblock)

Where some hosts are on multiple nested netblocks.

The only way I see to convert that to Postgres's syntax would be to join
against the networks table again and then group by the primary key of the
networks table. Ick.

--
greg

On Thu, 20 Feb 2003, Kevin Brown wrote:

> Tom Lane wrote:
> > UPDATE totals SET
> > xmax = ss.xmax, xmin = ss.xmin, ...
> > FROM
> > (SELECT groupid, max(x) AS xmax, ... FROM details GROUP BY groupid) ss
> > WHERE groupid = ss.groupid;
>
> As long as any individual item that you can express in the
> parenthesized (Informix) syntax can also be expressed as an element in
> a SELECT, then the above is equivalent in every way to the Informix
> syntax. And since SELECT allows subselects, it seems to me that the
> PG syntax is complete.
>
> My question is whether or not there's likely to be an approved
> standard way of accomplishing what either syntax does. Is there
> anything in the current draft that addresses this?

Yes there is. I've posted the URL on the hackers list a while back, but
here it is again:

ftp://sqlstandards.org/SC32/WG3/Progression_Documents/FCD/4FCD1-02-Foundation-2002-01.pdf

pp 851 to 862, in particular, p 858 defines the the <set clause list> as
supporting <multiple column assignment> as supporting something like:

(target1, target2, target3) = (value1, value2, value3)

Given that the direction of the spec seems to be headed towards the
desired syntax, can we put this on the TODO list?

Dave

On Thu, 2003-02-20 at 11:49, Dave Cramer wrote:
> Scott,
>
> I can't find page 858 in that document, is it the right one?
>
> also the link s/b ?
>
> ftp://ftp.sqlstandards.org/SC32/WG3/Progression_Documents/FCD/4FCD1-01-Framework-2002-01.pdf
>
> Dave
> On Thu, 2003-02-20 at 11:20, scott.marlowe wrote:
> > On Thu, 20 Feb 2003, Tom Lane wrote:
> >
> > > Hannu Krosing <hannu(at)tm(dot)ee> writes:
> > > > Are you against it just on grounds of cleanliness and ANSI compliance,
> > > > or do you see more serious problems in letting it in ?
> > >
> > > At this point it seems there are two different things being tossed
> > > about. I originally understood Dave to be asking for parens to be
> > > allowed around individual target column names, which seems a useless
> > > frammish to me. What Bruce has pointed out is that a syntax that lets
> > > you assign multiple columns from a single rowsource would be an actual
> > > improvement in functionality, or at least in convenience and efficiency.
> > > (It would also be a substantial bit of work, which is why I think this
> > > isn't what Dave was offering a quick patch to do...) What I'd like to
> > > know right now is which interpretation Informix actually implements.
> > >
> > > I don't like adding nonstandard syntaxes that add no functionality ---
> > > but if Informix has done what Bruce is talking about, that's a different
> > > matter altogether.
> >
> > Tom, I was purusing the wild and wonderfully exciting new SQL
> >
> > (found here:
> > ftp://sqlstandards.org/SC32/WG3/Progression_Documents/FCD/4FCD1-01-Framework-2002-01.pdf)
> >
> > ANSI TC NCITS H2
> > ISO/IEC JTC 1/SC 32/WG 3
> > Database
> >
> > document to see what it had to say, and on this subject, and it looks like
> > update is going to be supporing this same style we're discussing here.
> >
> > Look on or around p. 858 in that doc.)
--
Dave Cramer <dave(at)fastcrypt(dot)com>
Cramer Consulting

While I can see a subquery in UPDATE as working in most cases:

UPDATE tab
SET col - t.col
FROM (SELECT col from xx) AS t
WHERE ...

but I don't see that working for correlated subqueries, where you want
to set a column based on a value you are updating. (Many use correlated
subqueries in UPDATE a lot.) Do FROM subqueries work as correlated
subqueries? I can't see how they would because you don't have a row
being processed at the FROM stage of the query.

I did look at the SQL99 standards and ROW does appear there:

<update statement: positioned> ::=
UPDATE <target table>
SET <set clause list>
WHERE CURRENT OF <cursor name>

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

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

and later it says:

a) If <update target> specifies ROW, then let CL be the set of
all columns of T.

The TODO item would be:

Support SQL99 UPDATE SET ROW = () with extension SET ROW (col ...) = ()

This also gets into that weird Informix syntax where you have to
double-paren when you want to use a subquery. Basically, this thing
keeps getting wierder and wierder.

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

Dave Cramer wrote:
> Given that the direction of the spec seems to be headed towards the
> desired syntax, can we put this on the TODO list?
>
> Dave
>
> On Thu, 2003-02-20 at 11:49, Dave Cramer wrote:
> > Scott,
> >
> > I can't find page 858 in that document, is it the right one?
> >
> > also the link s/b ?
> >
> > ftp://ftp.sqlstandards.org/SC32/WG3/Progression_Documents/FCD/4FCD1-01-Framework-2002-01.pdf
> >
> > Dave
> > On Thu, 2003-02-20 at 11:20, scott.marlowe wrote:
> > > On Thu, 20 Feb 2003, Tom Lane wrote:
> > >
> > > > Hannu Krosing <hannu(at)tm(dot)ee> writes:
> > > > > Are you against it just on grounds of cleanliness and ANSI compliance,
> > > > > or do you see more serious problems in letting it in ?
> > > >
> > > > At this point it seems there are two different things being tossed
> > > > about. I originally understood Dave to be asking for parens to be
> > > > allowed around individual target column names, which seems a useless
> > > > frammish to me. What Bruce has pointed out is that a syntax that lets
> > > > you assign multiple columns from a single rowsource would be an actual
> > > > improvement in functionality, or at least in convenience and efficiency.
> > > > (It would also be a substantial bit of work, which is why I think this
> > > > isn't what Dave was offering a quick patch to do...) What I'd like to
> > > > know right now is which interpretation Informix actually implements.
> > > >
> > > > I don't like adding nonstandard syntaxes that add no functionality ---
> > > > but if Informix has done what Bruce is talking about, that's a different
> > > > matter altogether.
> > >
> > > Tom, I was purusing the wild and wonderfully exciting new SQL
> > >
> > > (found here:
> > > ftp://sqlstandards.org/SC32/WG3/Progression_Documents/FCD/4FCD1-01-Framework-2002-01.pdf)
> > >
> > > ANSI TC NCITS H2
> > > ISO/IEC JTC 1/SC 32/WG 3
> > > Database
> > >
> > > document to see what it had to say, and on this subject, and it looks like
> > > update is going to be supporing this same style we're discussing here.
> > >
> > > Look on or around p. 858 in that doc.)
> --
> Dave Cramer <dave(at)fastcrypt(dot)com>
> Cramer Consulting
>
>

--
Bruce Momjian | http://candle.pha.pa.us
pgman(at)candle(dot)pha(dot)pa(dot)us | (610) 359-1001
+ If your life is a hard drive, | 13 Roberts Road
+ Christ can be your backup. | Newtown Square, Pennsylvania 19073

With no one replying on how to do correlated subqueries in FROM for
UPDATE, I am adding this to the TODO list:

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

Several people indicated they wanted this functionality. The ROW is SQL
standard, and the column list is an extension.

I do not see any way to allow subqueries without requiring two levels of
parentheses, one for the list, another for the subquery. ROW should
also be optional.

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

Bruce Momjian wrote:
>
> While I can see a subquery in UPDATE as working in most cases:
>
> UPDATE tab
> SET col - t.col
> FROM (SELECT col from xx) AS t
> WHERE ...
>
> but I don't see that working for correlated subqueries, where you want
> to set a column based on a value you are updating. (Many use correlated
> subqueries in UPDATE a lot.) Do FROM subqueries work as correlated
> subqueries? I can't see how they would because you don't have a row
> being processed at the FROM stage of the query.
>
> I did look at the SQL99 standards and ROW does appear there:
>
> <update statement: positioned> ::=
> UPDATE <target table>
> SET <set clause list>
> WHERE CURRENT OF <cursor name>
>
> <set clause list> ::=
> <set clause> [ { <comma> <set clause> }... ]
>
> <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>
>
> and later it says:
>
> a) If <update target> specifies ROW, then let CL be the set of
> all columns of T.
>
> The TODO item would be:
>
> Support SQL99 UPDATE SET ROW = () with extension SET ROW (col ...) = ()
>
> This also gets into that weird Informix syntax where you have to
> double-paren when you want to use a subquery. Basically, this thing
> keeps getting wierder and wierder.
>
> ---------------------------------------------------------------------------
>
> Dave Cramer wrote:
> > Given that the direction of the spec seems to be headed towards the
> > desired syntax, can we put this on the TODO list?
> >
> > Dave
> >
> > On Thu, 2003-02-20 at 11:49, Dave Cramer wrote:
> > > Scott,
> > >
> > > I can't find page 858 in that document, is it the right one?
> > >
> > > also the link s/b ?
> > >
> > > ftp://ftp.sqlstandards.org/SC32/WG3/Progression_Documents/FCD/4FCD1-01-Framework-2002-01.pdf
> > >
> > > Dave
> > > On Thu, 2003-02-20 at 11:20, scott.marlowe wrote:
> > > > On Thu, 20 Feb 2003, Tom Lane wrote:
> > > >
> > > > > Hannu Krosing <hannu(at)tm(dot)ee> writes:
> > > > > > Are you against it just on grounds of cleanliness and ANSI compliance,
> > > > > > or do you see more serious problems in letting it in ?
> > > > >
> > > > > At this point it seems there are two different things being tossed
> > > > > about. I originally understood Dave to be asking for parens to be
> > > > > allowed around individual target column names, which seems a useless
> > > > > frammish to me. What Bruce has pointed out is that a syntax that lets
> > > > > you assign multiple columns from a single rowsource would be an actual
> > > > > improvement in functionality, or at least in convenience and efficiency.
> > > > > (It would also be a substantial bit of work, which is why I think this
> > > > > isn't what Dave was offering a quick patch to do...) What I'd like to
> > > > > know right now is which interpretation Informix actually implements.
> > > > >
> > > > > I don't like adding nonstandard syntaxes that add no functionality ---
> > > > > but if Informix has done what Bruce is talking about, that's a different
> > > > > matter altogether.
> > > >
> > > > Tom, I was purusing the wild and wonderfully exciting new SQL
> > > >
> > > > (found here:
> > > > ftp://sqlstandards.org/SC32/WG3/Progression_Documents/FCD/4FCD1-01-Framework-2002-01.pdf)
> > > >
> > > > ANSI TC NCITS H2
> > > > ISO/IEC JTC 1/SC 32/WG 3
> > > > Database
> > > >
> > > > document to see what it had to say, and on this subject, and it looks like
> > > > update is going to be supporing this same style we're discussing here.
> > > >
> > > > Look on or around p. 858 in that doc.)
> > --
> > Dave Cramer <dave(at)fastcrypt(dot)com>
> > Cramer Consulting
> >
> >
>
> --
> Bruce Momjian | http://candle.pha.pa.us
> pgman(at)candle(dot)pha(dot)pa(dot)us | (610) 359-1001
> + If your life is a hard drive, | 13 Roberts Road
> + Christ can be your backup. | Newtown Square, Pennsylvania 19073
>
> ---------------------------(end of broadcast)---------------------------
> TIP 3: if posting/reading through Usenet, please send an appropriate
> subscribe-nomail command to majordomo(at)postgresql(dot)org so that your
> message can get through to the mailing list cleanly
>

--
Bruce Momjian | http://candle.pha.pa.us
pgman(at)candle(dot)pha(dot)pa(dot)us | (610) 359-1001
+ If your life is a hard drive, | 13 Roberts Road
+ Christ can be your backup. | Newtown Square, Pennsylvania 19073

Bruce Momjian kirjutas E, 17.03.2003 kell 20:49:
> With no one replying on how to do correlated subqueries in FROM for
> UPDATE,

Correlated subqueries not working in FROM cluse of UPDATE is IMHO a bug,
so the way to do correlated subqueries in FROM for UPDATE would be to
fix this bug ;)

All common sense tells me that if I can update set col1=col2 and *not*
get the value from the first col2 to all col1's then the same should be
true for this

hannu=# creatre table updtarget(
hannu(# id int, val text);
ERROR: parser: parse error at or near "creatre" at character 1
hannu=# create table updtarget(id int, val text);
CREATE TABLE
hannu=# create table updsource(id int, val text);
CREATE TABLE
hannu=# insert into updtarget(id) values (1);
INSERT 16995 1
hannu=# insert into updtarget(id) values (2);
INSERT 16996 1
hannu=# insert into updsource(id,val) values (1,'one');
INSERT 16997 1
hannu=# insert into updsource(id,val) values (2,'two');
INSERT 16998 1
hannu=# update updtarget set val = src.val
hannu-# from (select s.val from updsource s
hannu-# where s.id=updtarget.id) as src
hannu-# ;
NOTICE: Adding missing FROM-clause entry in subquery for table
"updtarget"
UPDATE 2
hannu=# select * from updtarget;
id | val
----+-----
1 | one
2 | one
(2 rows)

there should be no need to add "missing FROM-clause entry" and the
result *should* be:

hannu=# select * from updtarget;
id | val
----+-----
1 | one
2 | two
(2 rows)

--------------------
Hannu

I wasn't sure it made logical sense to allow correlated subqueries in
FROM because the FROM is processed before the WHERE.

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

Hannu Krosing wrote:
> Bruce Momjian kirjutas E, 17.03.2003 kell 20:49:
> > With no one replying on how to do correlated subqueries in FROM for
> > UPDATE,
>
> Correlated subqueries not working in FROM cluse of UPDATE is IMHO a bug,
> so the way to do correlated subqueries in FROM for UPDATE would be to
> fix this bug ;)
>
> All common sense tells me that if I can update set col1=col2 and *not*
> get the value from the first col2 to all col1's then the same should be
> true for this
>
> hannu=# creatre table updtarget(
> hannu(# id int, val text);
> ERROR: parser: parse error at or near "creatre" at character 1
> hannu=# create table updtarget(id int, val text);
> CREATE TABLE
> hannu=# create table updsource(id int, val text);
> CREATE TABLE
> hannu=# insert into updtarget(id) values (1);
> INSERT 16995 1
> hannu=# insert into updtarget(id) values (2);
> INSERT 16996 1
> hannu=# insert into updsource(id,val) values (1,'one');
> INSERT 16997 1
> hannu=# insert into updsource(id,val) values (2,'two');
> INSERT 16998 1
> hannu=# update updtarget set val = src.val
> hannu-# from (select s.val from updsource s
> hannu-# where s.id=updtarget.id) as src
> hannu-# ;
> NOTICE: Adding missing FROM-clause entry in subquery for table
> "updtarget"
> UPDATE 2
> hannu=# select * from updtarget;
> id | val
> ----+-----
> 1 | one
> 2 | one
> (2 rows)
>
> there should be no need to add "missing FROM-clause entry" and the
> result *should* be:
>
> hannu=# select * from updtarget;
> id | val
> ----+-----
> 1 | one
> 2 | two
> (2 rows)
>
> --------------------
> Hannu
>
>
>
> ---------------------------(end of broadcast)---------------------------
> TIP 3: if posting/reading through Usenet, please send an appropriate
> subscribe-nomail command to majordomo(at)postgresql(dot)org so that your
> message can get through to the mailing list cleanly
>

--
Bruce Momjian | http://candle.pha.pa.us
pgman(at)candle(dot)pha(dot)pa(dot)us | (610) 359-1001
+ If your life is a hard drive, | 13 Roberts Road
+ Christ can be your backup. | Newtown Square, Pennsylvania 19073

Bruce Momjian <pgman(at)candle(dot)pha(dot)pa(dot)us> writes:
> I wasn't sure it made logical sense to allow correlated subqueries in
> FROM because the FROM is processed before the WHERE.

It doesn't; in fact it violates the whole semantic model of SQL,
as far as I can see. Sub-selects in FROM are (in principle)
evaluated separately and then joined. They can't have cross-references.

I think there is some weird construct in SQL99 that alters this behavior,
though.

regards, tom lane

Tom Lane kirjutas K, 19.03.2003 kell 16:46:
> Bruce Momjian <pgman(at)candle(dot)pha(dot)pa(dot)us> writes:
> > I wasn't sure it made logical sense to allow correlated subqueries in
> > FROM because the FROM is processed before the WHERE.
>
> It doesn't; in fact it violates the whole semantic model of SQL,
> as far as I can see. Sub-selects in FROM are (in principle)
> evaluated separately and then joined. They can't have cross-references.

Makes sense. What I was describing would have been akin to updatable
queries where you first do all the joining and then update one of the
underlying tables.

the more accurate (nonstandard) syntax could have been

SELECT src.val,
tgt.val
FROM updatesrc as src FOR UPDATE,
updatetgd as tgt
WHERE src.id = tgt.id
SET src.val = tgt.val
;

> I think there is some weird construct in SQL99 that alters this behavior,
> though.

You probably mean WITH, which acts like FROM but has lexically previous
(or all in case of WITH RECURSIVE) sub-selects in its namespace.

----------------
Hannu

Hannu Krosing <hannu(at)tm(dot)ee> writes:

> the more accurate (nonstandard) syntax could have been
>
> SELECT src.val,
> tgt.val
> FROM updatesrc as src FOR UPDATE,
> updatetgd as tgt
> WHERE src.id = tgt.id
> SET src.val = tgt.val
> ;

The syntax in Oracle, for example, would be not very different:

UPDATE (
SELECT src.id, src.val, tgt.val as newval
FROM udpatesrc AS src,
updatetgd AS tgt
WHERE src.id = tgt.id
)
SET val = newval

This only works if src.id is declared as a primary key.

I'm not sure if this is blessed by any standard.
It's certainly extremely useful.

--
greg

Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> writes:

> UPDATE totals SET
> xmax = ss.xmax, xmin = ss.xmin, ...
> FROM
> (SELECT groupid, max(x) AS xmax, ... FROM details GROUP BY groupid) ss
> WHERE groupid = ss.groupid;
>
...
>
> Of course this syntax isn't standard either ... but we already have it.

Did this patch ever make it in? It's not documented in the 8.0 documentation
for UPDATE at:

http://www.postgresql.org/docs/8.0/interactive/dml-update.html

--
greg

Greg Stark wrote:
>
> Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> writes:
>
> > UPDATE totals SET
> > xmax = ss.xmax, xmin = ss.xmin, ...
> > FROM
> > (SELECT groupid, max(x) AS xmax, ... FROM details GROUP BY groupid) ss
> > WHERE groupid = ss.groupid;
> >
> ...
> >
> > Of course this syntax isn't standard either ... but we already have it.
>
>
> Did this patch ever make it in? It's not documented in the 8.0 documentation
> for UPDATE at:
>
> http://www.postgresql.org/docs/8.0/interactive/dml-update.html

It is documented only in the UPDATE manual page because it is fairly exotic:

http://www.postgresql.org/docs/8.0/interactive/sql-update.html

--
Bruce Momjian | http://candle.pha.pa.us
pgman(at)candle(dot)pha(dot)pa(dot)us | (610) 359-1001
+ If your life is a hard drive, | 13 Roberts Road
+ Christ can be your backup. | Newtown Square, Pennsylvania 19073