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

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
[mailtogsql-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


---------------------------(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

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



---------------------------(end of broadcast)---------------------------
TIP 6: Have you searched our list archives?

http://archives.postgresql.org

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>


---------------------------(end of broadcast)---------------------------
TIP 6: Have you searched our list archives?

http://archives.postgresql.org