Convert SQL server stored procedures to postgreSQL

Question

I have a SQL Server database that I am migrating to PostgreSQL 12.0.

I have managed to migrate the schema and the tables. The applications connecting to the database rely a lot on stored procedures, which also need to be translated to PostgreSQL.

Unfortunately, the tool I was using doesn't convert stored procedures. Any ideas on how I should go about this or is there a tool which can do this?

Answer 1

Unfortunately you'll probably have to handle the stored procedures by hand. If you're lucky, they're simple procedures following standard SQL syntax, and you'll be able to just copy the existing queries over to PostgreSQL, otherwise you'll have to handle any fallout on a case by case basis.

Side note: this is one benefit of some database agnostic ORMs like Dapper, if your database is mainly consumed by application code. By using the ORM framework's native calls, you decouple your database logic from the actual database system, but there's pros and cons.

Answer 2

There is no compete solution for migration of stored procedures and functions from SQL Server to PostgreSQL since this is a quite complicated task. However, some tools can partially automate the job by migrating generic SQL patterns and syntax constructions:

• SQL Server to PostgreSQL Code Converter
• Full Convert SQL Server to PostgreSQL