Thread: Re: Compiere ERP and SQL quirks
Marek Mosiewicz wrote: > Hello > > We made Compiere (Open source ERP system) to Firebird (Fyracle) > This is special version of Firebird with added Oracle compatibility > (Oracle > PL/SQL). > It made porting much easier, but our experience show that it > would be now also not very difficult with other databases like PostgreSQL. > > Compiere contained lot of PL/SQL which size is now largely reduced. > Main problem is some SQL constructions which are not supported. > Particulary something like this is very important: > UPDATE sometable set (col1,col2) = (select col_a,col_b from > another_table > where ....) > This construction seems to be quite useful in another cases. > > Would be it diffcult and possible to add such syntax to PostgreSQL ? PostgreSQL has limited support for the SQL 92 row constructor. You can use it in select expressions in most places, but not in update as you noticed. Be forewarned that row constructor expressions involving the > or < operators can give the wrong answer: select (2,2,3) > (2,1,3) returns false when it should return true. Merlin
Upps sorry now found it on TODO list. I was not aware that it is SQL92 standard. Is it difficult to implement ?Simplest approach would be to rewrite it to UPDATE t1 set col1 = (select cola ...), col2 = (select colb....) .... but it would result in not optimal plan. Marek Mosiewicz -----Original Message----- From: pgsql-hackers-owner@postgresql.org [mailto:pgsql-hackers-owner@postgresql.org]On Behalf Of Merlin Moncure Sent: Friday, January 07, 2005 6:15 PM To: Marek Mosiewicz Cc: pgsql-hackers@postgresql.org Subject: Re: [HACKERS] Compiere ERP and SQL quirks Marek Mosiewicz wrote: > Hello > > We made Compiere (Open source ERP system) to Firebird (Fyracle) > This is special version of Firebird with added Oracle compatibility > (Oracle > PL/SQL). > It made porting much easier, but our experience show that it > would be now also not very difficult with other databases like PostgreSQL. > > Compiere contained lot of PL/SQL which size is now largely reduced. > Main problem is some SQL constructions which are not supported. > Particulary something like this is very important: > UPDATE sometable set (col1,col2) = (select col_a,col_b from > another_table > where ....) > This construction seems to be quite useful in another cases. > > Would be it diffcult and possible to add such syntax to PostgreSQL ? PostgreSQL has limited support for the SQL 92 row constructor. You can use it in select expressions in most places, but not in update as you noticed. Be forewarned that row constructor expressions involving the > or < operators can give the wrong answer: select (2,2,3) > (2,1,3) returns false when it should return true. Merlin ---------------------------(end of broadcast)--------------------------- TIP 7: don't forget to increase your free space map settings
Marek Mosiewicz wrote: > Upps sorry now found it on TODO list. > > I was not aware that it is SQL92 standard. > > Is it difficult to implement ? > Simplest approach would be > to rewrite it to UPDATE t1 set col1 = (select cola ...), col2 = (select > colb....) .... > but it would result in not optimal plan. Doesn't something like UPDATE t1 SET col1=cola, col2=colb FROM t1 JOIN anothertable ot ON t1.id=ot.id WHERE ... Work the way you'd like it? I'd expect this syntax to be as widely portable and performant. Regards, Andreas
Andreas wrote: > Marek Mosiewicz wrote: > > Upps sorry now found it on TODO list. > > > > I was not aware that it is SQL92 standard. > > > > Is it difficult to implement ? > > Simplest approach would be > > to rewrite it to UPDATE t1 set col1 = (select cola ...), col2 = (select > > colb....) .... > > but it would result in not optimal plan. > > Doesn't something like > > UPDATE t1 SET col1=cola, col2=colb > FROM t1 JOIN anothertable ot ON t1.id=ot.id > WHERE ... > > Work the way you'd like it? I'd expect this syntax to be as widely > portable and performant. Hmm, 'from' in an update is a PostgreSQL extension to the update command :). Merlin
On Fri, 7 Jan 2005, Merlin Moncure wrote:
> Marek Mosiewicz wrote:
> > Hello
> >
> > We made Compiere (Open source ERP system) to Firebird (Fyracle)
> > This is special version of Firebird with added Oracle compatibility
> > (Oracle
> > PL/SQL).
> > It made porting much easier, but our experience show that it
> > would be now also not very difficult with other databases like
> PostgreSQL.
> >
> > Compiere contained lot of PL/SQL which size is now largely reduced.
> > Main problem is some SQL constructions which are not supported.
> > Particulary something like this is very important:
> > UPDATE sometable set (col1,col2) = (select col_a,col_b from
> > another_table
> > where ....)
> > This construction seems to be quite useful in another cases.
> >
> > Would be it diffcult and possible to add such syntax to PostgreSQL ?
>
> PostgreSQL has limited support for the SQL 92 row constructor. You can
> use it in select expressions in most places, but not in update as you
> noticed.
Umm, SQL92 doesn't appear to allow row constructors at that place in
update as far as I can tell.
<set clause list> ::= <set clause> [ { <comma> <set clause> }... ]
<set clause> ::= <object column> <equals operator> <update source>
<update source> ::= <value expression> | <null specification> | DEFAULT
<object column> ::= <column name>
Merlin Moncure wrote: > Andreas wrote: > >>Doesn't something like >> >>UPDATE t1 SET col1=cola, col2=colb >> FROM t1 JOIN anothertable ot ON t1.id=ot.id >> WHERE ... >> >>Work the way you'd like it? I'd expect this syntax to be as widely >>portable and performant. > > > Hmm, 'from' in an update is a PostgreSQL extension to the update command > :). Ooops... Oracle doesn't know it, I used to use it on MSSQL. Regards, Andreas