Google Cloud Blog
Follow
Multiple result sets: How Database Migration Service automates SQL server to PostgreSQL translation
Migrating multi-result stored procedures from SQL Server to PostgreSQL presents a significant challenge due to architectural differences. SQL Server natively streams multiple result sets, while PostgreSQL requires explicit cursor management. Manually rewriting hundreds of procedures is impractical, making automated tools like Google Cloud's Database Migration Service (DMS) essential. DMS analyzes procedures to determine if they return one or multiple result sets, with or without a scalar return value. For single result sets or scalar returns, it generates a PostgreSQL PROCEDURE with an INOUT refcursor. Multiple result sets, or a combination, are converted into a PostgreSQL FUNCTION returning SETOF refcursor. The scalar return value is appended as a separate "return_value" cursor. Testing migrated code requires executing within transaction blocks and fetching data from each cursor sequentially. DMS uses a sophisticated pre-processing mechanism and a directed graph analysis with Depth First Search to accurately determine result set counts. This automated approach preserves core logic while adapting the output handling for application integration and QA. Understanding DMS's conversion strategy is key for seamless day-two operations in PostgreSQL.