\copy in Current CVS

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@fastcrypt.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@fastcrypt.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@gmx.net

Dave Cramer <dave@fastcrypt.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@fastcrypt.com>
Cramer Consulting

Dave Cramer <dave@fastcrypt.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@fastcrypt.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@fastcrypt.com>
Cramer Consulting

Dave Cramer <dave@fastcrypt.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@fastcrypt.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@fastcrypt.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@candle.pha.pa.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@fastcrypt.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@fastcrypt.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@aubit.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@aix1.uottawa.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@fastcrypt.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@fastcrypt.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@fastcrypt.com>
To: "Justin Clift" <justin@postgresql.org>
Cc: "Tom Lane" <tgl@sss.pgh.pa.us>; "Peter Eisentraut" <peter_e@gmx.net>;
"Pgsql Hackers" <pgsql-hackers@postgresql.org>
Sent: Wednesday, February 19, 2003 10:18 PM
Subject: Re: [HACKERS] request for sql3 compliance for the update command