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