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. 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:
| | | | | | | 1 Result Set OR a Scalar Return Value only | | Handled natively via an INOUT refcursor parameter or standard variable tracking. | | | Multiple Result Sets OR a combination of Result Sets + Scalar Return | | Converted to a RETURNS SETOF refcursor block. The scalar return value is appended as its own separate cursor dataset. |
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
To align with PostgreSQL's execution model, DMS maps the original T-SQL behavior into a structural PL/pgSQL architecture using SETOF refcursor
and explicit cursor management.
For the child procedure sp_getpatientdoctorvisits, which yields exactly one result set, DMS creates a standard PostgreSQL PROCEDURE utilizing an explicit INOUT refcursor parameter to safely pass the pointer back to the caller. SQL
For the master routine and the complex lab child routine, an INOUT parameter isn't enough to capture the varying arrays of output. DMS transforms these into PL/pgSQL Functions returning a SETOF refcursor. Take note of how the translated sp_getpatientlabresults builds its cursors sequentially and, at the very end, dynamically opens a distinct cursor explicitly named "return_value" to pass the scalar integer back to the execution stack:
SQL
When integrating these migrated routines back into your application data access layer, QA and application engineers need to adapt how they process execution results. Instead of reading standard tabular rows sequentially, the calling application or test harness receives an array of cursor references. To handle this programmatically, your application must fetch the data from each returned portal sequentially. DMS isolates the scalar return value by placing it inside its own dedicated, explicitly named "return_value" cursor dataset at the very end of the execution stack. Preparing your development teams for this structural mapping ensures that your application logic and validation scripts can accurately parse the multi-layered response arrays without disruption.
Testing these migrated objects in PostgreSQL requires working within explicit transaction blocks. Because PostgreSQL cursors are bound to the transaction lifecycle, accessing the data from the memory portals requires encapsulating the execution and the data retrieval commands within a single BEGIN ... COMMIT
block.
Here is how you execute and fetch the entire complex dataset for Patient 3 inside PostgreSQL:
SQL
The DMS product employs a sophisticated pre-processing mechanism to understand the expected result set count and the possible existence of a return value. The ultimate goal is to map every SQL Server procedure into one of three distinct categories: no result sets, a single result set, or multiple/dynamic result set counts.
To achieve this accurately, DMS performs a deep structural analysis:
Direct Result Sets: First, the engine scans the procedure's body to count the direct result sets, which are the explicit SELECT statements executed directly within it.
Dynamic Considerations: Special consideration must be given when dealing with looped or conditional SELECT
/EXEC
statements. Because these constructs inherently mean the number of returned result sets can differ between executions, the count for that procedure is immediately marked as "dynamic".
Building the Call Network: When the engine encounters references to other stored procedures, it does not yet know how many result sets those child procedures expose. To solve this, DMS builds a comprehensive directed graph to model the entire call hierarchy between procedures.
DFS Propagation: Only once this directed network is fully built can the engine run a Depth First Search (DFS) algorithm. This DFS traversal systematically propagates the result set counts—whether fixed integers or dynamic flags—back up the call chain to the top-level procedures.
Consequently, the generated code is accurately modified to support both returning the outer datasets and allowing child routines to be invoked correctly within nested execution stacks. This graph-based approach guarantees accuracy and seamlessly supports highly complex inter-procedural interactions, including both direct and indirect recursions.
Google Cloud’s Database Migration Service takes the guesswork out of structural transformations by programmatically applying the FUNCTION vs. PROCEDURE decision tree based on result set counts. While it completely preserves your core business and conditional execution logic, it does change how application connection pools and QA engineers interact with execution results.
Understanding this automated architecture ensures you can effectively map out your validation scripts and configure your data access layers for seamless day-two operations in PostgreSQL.
Now that you learned how to convert multiple result sets, it's time to start a PostgreSQL database on Google Cloud. Please let us know how your journey was.
Get started with a Database Migration Service with the Google Cloud $300 free credits. Start building for free.