2

Can anybody suggest a tool or share code which can help me converting large number of t-sql stored procedures in database to postgresql while migrating from SQL Server database to postgresql?

== Edited== here are some clear goals:

  1. Convert all sps to postgre sql without considering performance / tuning etc, by only focusing that syntax is right and query returns correct data

Language conversion and transformation related guidelines are really useful. I see couple of old vbscript examples (most of them not working for me) and could try to convert them to T4 templates.

I am surprised no one really needed this kind of conversion! I still don't have a right tool to do so.

Thanks in advance.

Prashant Lakhlani
  • 5,758
  • 5
  • 25
  • 39

3 Answers3

2

Translating languages is hard. You need to parse the original (with all the strange syntax and warts it allows above and beyond what the documentation says), determine what names mean, discover the semantics, and translate to the target language without losing the subtle details.

I'd be surprised if you found a solution for this off-the-shelf. Part of the problem is there are a vast number N of source languages (made worse by dialects), and a vast number of targets M, requiring a library of NxM translators to be lying around. And if you wait a few months, N and M both move. One hope is to translate N langauges to a common universal core language, and translate that to M targets, now only requiring N+M... but nobody has found a really universal language, and if you wait a few months, N and M move anyway.

One can consider building (or getting someone else to build) a specific translator for the task; this usually is uneconomical, at least if you want it to be reliable, because so much of the translator machinery (parsing, name resolution, pattern matching/translating, ...) tends to get rebuilt from scratch.

One can amortize the cost of the translator infrastructure if you are careful, by building (a lot of) shared machinery. Once you do that, building a translator is easier (still not easy) and the economics make more sense for individual cases. You can read more about this approach at: What kinds of patterns could I enforce on the code to make it easier to translate to another programming language?

Community
  • 1
  • 1
Ira Baxter
  • 93,541
  • 22
  • 172
  • 341
  • I don't think you will be able to find a tool because I don't believe it is possible. Our app supports several DBMSs. We write in T-SQL and it converts for Oracle PL/SQL, etc. But PostGreSQL functions are quite different and are not designed for returning the results of select queries. For example, it is a lot of work to have a function in PostGreSQL that simply returns the results of "Select * from TableA inner join TableB on A.ID=B.ID" because you need to specify every column in the return type of the function. Other DBMSs do not require that. We will probably not support PostGreSQL. – Rob Kraft Sep 06 '17 at 18:07
  • 1
    Stored procedure languages are all Turing-capable AFAIK. That means one can compute anything the other can compute. So, it *is* possible. Granted, It might be lot of work, so much that in fact it isn't worth the effort, that's different than you can't do it. I build translators for a living. We've overcome most practical problems in ugly translations, so I'm a lot more optimistic than you are. – Ira Baxter Sep 06 '17 at 20:24
0

Have you seen this page:

Convert from other databases to PostgreSQL?

Jan Marek
  • 10,390
  • 3
  • 21
  • 19
0

I'm sure that every SP needs to be converted by developers. Ira Baxter says (absolutelly correct) about languages, but I want to say about platform differences. for example, how to translate to different platform the usage temporary tables? What to do with performance-tuned queries? Performance tuning uses the specific features of RDBMS, and this tuning cannot be automatically converted. And so on...

Alex_L
  • 2,658
  • 1
  • 15
  • 13