Porting SQL Server 2000 database to PostgreSQL

[Rod Early]

I have the task of converting a SQL Server 2000 database to
PostgreSQL. The data itself does not need to be converted, but the
structure and stored procedures must be.

I expect that converting tables and views will be simple. I expect
that converting stored procedures and user-defined functions from
T-SQL to PostgreSQL will be more complex.

In any case, I am looking for recommendations and counsel regarding my
expectations. I have much Windows 2000/NT and SQL Server experience,
and some Unix/Linux experience, probably post-beginner level. (I used
to work with Unix and VMS all the time in college 15 years ago but
haven't touched it since.)

I plan to use a Windows client such as pgAdminIII to administer and
work with the database in PostgreSQL. The database is used by a
WindowsCE application, so I will need to change its data access layer
from SQLClient to whatever is needed in CE and Windows to access
PostgreSQL.

Thank you in advance for your recommendations, advice, warnings,
gripes, etc.

Rod Early

[anony]

1. stored procedures are now called functions

2. unlike ms tsql, in postgres query analyzer you can't test
conditional statements and variables outside of a function. this makes
debugging really hard.

3. inside the function single quotes must be escaped because your
function is entered in as a string literal.

4. to return recordsets you must create a custom return TYPE and use a
FOR loop with %ROWTYPE to interate thru the data.

5. you can overload functions. two function with the same name but
different input fn_foo(int) OR fn_foo(int, varchar).

6. temp tables don't work well in functions unless you make them
dynamically with execute. instead use normal tables and your pid.

favorite editor: editplus w/ psql definitions

[Richard Huxton]

Rod Early wrote:

I have the task of converting a SQL Server 2000 database to
PostgreSQL. The data itself does not need to be converted, but the
structure and stored procedures must be.

I expect that converting tables and views will be simple. I expect
that converting stored procedures and user-defined functions from
T-SQL to PostgreSQL will be more complex.
There are a couple of notes on the techdocs site detailing conversion
from MS-SQL.
http://techdocs.postgresql.org/
Where you'll need to put some extra work in:
- Returning multiple recordsets from a function/stored-proc (we don't)
- In/out parameters (pass by ref) - we don't
- Exception handling (needs improvement)
You have some useful new tools available:
- Partial/conditional/functional indexes
- Lots of different procedural languages available
- a bunch of stuff that has slipped my mind momentarily
Oh, PG returns the complete result set in one go, so if you want to see
the first row of 100,000 quickly use an explicit cursor.
In any case, I am looking for recommendations and counsel regarding my
expectations. I have much Windows 2000/NT and SQL Server experience,
and some Unix/Linux experience, probably post-beginner level. (I used
to work with Unix and VMS all the time in college 15 years ago but
haven't touched it since.)
The standard guide to the config file and performance tuning is at
"GeneralBits":
http://www.varlena.com/varlena/Gener...bits/index.php
I plan to use a Windows client such as pgAdminIII to administer and
work with the database in PostgreSQL. The database is used by a
WindowsCE application, so I will need to change its data access layer
from SQLClient to whatever is needed in CE and Windows to access
PostgreSQL.

Looks like you've found the .net/odbc libraries in your next post. Can't
say anything about CE as an environment.

--
Richard Huxton
Archonet Ltd

---------------------------(end of broadcast)---------------------------
TIP 7: don't forget to increase your free space map settings

[Robert Treat]

On Fri, 2004-05-21 at 02:49, anony wrote:

3. inside the function single quotes must be escaped because your
function is entered in as a string literal.

be aware that your editor might quote these for you (at least some of
the admin apps do, not sure about PgAdminIII though)
4. to return recordsets you must create a custom return TYPE and use a
FOR loop with %ROWTYPE to interate thru the data.

this isn't always true... see
http://techdocs.postgresql.org/guide...rningFunctions

And while you're there check out the section on the main page about
converting from other databases, there are a couple on $ql server. Also
take notes on your conversion and submit your own article when your done
:-)
Robert Treat
--
Build A Brighter Lamp :: Linux Apache {middleware} PostgreSQL
---------------------------(end of broadcast)---------------------------
TIP 4: Don't 'kill -9' the postmaster