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