Thread: Porting SQL Server -> PostgreSQL ( SELECT INTO problem... )

Porting SQL Server -> PostgreSQL ( SELECT INTO problem... )

From
Patrick FICHE
Date:
Hi,

I'm porting an application built upon stored procedures from SQL Server to
PostgreSQL.
Most of the work was done without too many problems but now I'm testing and
I'm facing to a comportment which seems to be different.

In SQL Server, if you do :
SELECT @var1 = @var1 + col1 FROM tbl1 WHERE col2 = 1
If there is no record in tbl1 with this condition, @var1 remains unchanged.

I changed it in PostgreSQL to
SELECT INTO _var1 _var1 || col1 FROM tbl1 WHERE col2 = 1
It there is no record in tbl1 with this condition, _var1 is now NULL.

Is there an easy way to have the same result as in SQL Server or do I have
use a temporary variable and concatenate values after ?

Patrick Fiche
email : patrick.fiche@aqsacom.com
tél : 01 69 29 36 18


Porting SQL Server -> PostgreSQL ( SELECT INTO problem... )

From
Lee Kindness
Date:
Just thinking aloud, but consider:

 SELECT INTO _var1 COALESCE(_var1 || col1, _var1) FROM tbl1 WHERE col2 = 1

I think that, or something along that lines, should do the trick.

Lee.

Patrick FICHE writes:
 > Hi,
 >
 > I'm porting an application built upon stored procedures from SQL Server to
 > PostgreSQL.
 > Most of the work was done without too many problems but now I'm testing and
 > I'm facing to a comportment which seems to be different.
 >
 > In SQL Server, if you do :
 > SELECT @var1 = @var1 + col1 FROM tbl1 WHERE col2 = 1
 > If there is no record in tbl1 with this condition, @var1 remains unchanged.
 >
 > I changed it in PostgreSQL to
 > SELECT INTO _var1 _var1 || col1 FROM tbl1 WHERE col2 = 1
 > It there is no record in tbl1 with this condition, _var1 is now NULL.
 >
 > Is there an easy way to have the same result as in SQL Server or do I have
 > use a temporary variable and concatenate values after ?
 >
 > Patrick Fiche
 > email : patrick.fiche@aqsacom.com
 > tél : 01 69 29 36 18
 >
 >
 > ---------------------------(end of broadcast)---------------------------
 > TIP 2: you can get off all lists at once with the unregister command
 >     (send "unregister YourEmailAddressHere" to majordomo@postgresql.org)