Re: Compiere ERP and SQL quirks

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>