Experience Report addressing the manual migration of MS SQL Server Stored Procedures and Table-Valued Functions to PL/pgSQL. We chose a manual migration from T-SQL over using a PostgreSQL Extension with an automated translation. The motivation was that the T-SQL code contains non-trivial business logic for which we wanted idiomatic PL/pgSQL code. 

The T-SQL Code used features like 

• Mix of Stored Procedures and Table-valued Functions

• Table variables, (user-defined) table types 

• Recursive Common Table Expressions 

• Optional parameters 

The migration required

• Adopting naming conventions 

• Renaming parameters and local variables 

• Maping table types 

• Mapping table-valued parameters to arrays 

• Mapping table-valued return types to SETOF record

• Translating between arrays and tables 

In this session we will migrate a chain of dependend functions T-SQL functions to PL/pgSQL. We will work around quirks in both T-SQL functions and PL/pgSQL.