Porting MSSQL to PGSQL (Was: [OT] MySQL is bad, but THIS bad?)

> On Thursday 18 May 2006 12:38, Josh Berkus wrote:
>> Personally, I'd go after MSSQL before I bothered with MySQL.   Sure, let's
>> make *migration* easier for those who wake up and smell the BS, but
>> migration can (and probably should) be one-way.

Somebody earlier was mentioning, why no automatic transformer from Transact-SQL
to PLPGSQL (maybe with a bunch of glue routines). The grammar is not a problem,
though you have to wonder at all the wired-in keywords (T-SQL always felt like 
COBOL).

The stumbling blocks are not in language, but function. Many of those functions 
are rarely used, but some big ones are quite common ...

T-SQL has statement-level triggers, and they get used a lot (some big apps ONLY 
put code in triggers). Statement-level triggers are very efficient for 
maintaining aggregates; the closest PG has are rewrite rules.

Other issues: stored procs returning multiple result sets; "print" statements; 
SELECT TOP n PERCENT; COMPUTE-expressions (subtotals); and some of the @@global 
variables that are hard to emulate @@IDENTITY being the main problem in older 
T-SQL code.

OpenXML is cool, but such a pig, that its difficulty in emulation is probably 
not an issue.

There are plenty of things that happily go away, or can be implemented with a 
client wrapper; for example, BULK INSERT and BACKUP. Other things just have no 
equivalent, and amount to no-ops in a PG world (partition functions)

A few things require some custom metadata tables (MSSQL "RULE" != PG "RULE").

If you want to convince MSSQL users to move over to PG, statement-level triggers
(with "OLD" and "NEW" rowsets) are a bottom-line requirement.
.......

For high-end MSSQL shops, a high value is being able to trace and profile 
(EXPLAIN) every client SQL command from the server side ... with plenty of 
options for selective trace.