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: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] 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 ---------------------------(end of broadcast)--------------------------- TIP 9: the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
