In the Medium blog post, “From MARS to SETOF REFCURSOR: Migrating Multi-Result Stored Procedures to PostgreSQL,” we explored the fundamental architectural differences between SQL Server and PostgreSQL regarding multiple result sets. We looked at how SQL Server natively streams multiple tabular streams from a single execution, whereas PostgreSQL requires a more deliberate strategy using explicit cursor manipulation.
If you’re facing a massive database migration with hundreds of these procedures, manually rewriting them is a non-starter. This is where automated tools come in. In this post, we’ll explore in detail how Google Cloud’s Database Migration Service (DMS) approaches this exact challenge, the conversion logic it applies under the hood, and how to actually run and test the generated code.
The Core Conversion Strategy of DMS
There are many reasons to migrate your databases to PostgreSQL, including enterprise performance and availability, a thriving developer and user community, and strong AI capabilities. But tricky queries, like those with multiple result sets, can slow down your migration project.
DMS looks at two specific things: How many result sets does the procedure return? and Does it use a scalar RETURN value?
The decision matrix for the translation looks like this:
|
Scenario |
SQL Server Characteristic |
PostgreSQL Target Object |
Mechanism |
|
Scenario A |
1 Result Set OR a Scalar Return Value only |
STORED PROCEDURE |
Handled natively via an INOUT refcursor parameter or standard variable tracking. |
|
Scenario B |
Multiple Result Sets OR a combination of Result Sets + Scalar Return |
FUNCTION |
Converted to a RETURNS SETOF refcursor block. The scalar return value is appended as its own separate cursor dataset. |
Automating Multi-Result Set Conversions: Inside DMS
SQL Server utilizes a tabular data stream protocol that allows multiple results to be transmitted over a single connection execution path without explicit declarations. PostgreSQL, by contrast, relies on a distinct execution protocol where multiple datasets are managed deterministically via cursors. To bridge this structural difference, DMS automates the translation logic.
Consider a baseline healthcare reporting scenario. We have a master procedure (sp_GetPatientSummary) that orchestrates data retrieval for a patient by conditionally calling two child procedures: one for lab results (sp_GetPatientLabResults) and one for clinical visits (sp_GetPatientDoctorVisits).
Depending on conditional logic and procedural execution paths, a single execution can return up to four distinct result sets plus a status integer indicating whether the patient was found.
SQL
