Google Cloud explains SQL Server to PostgreSQL migration

Google Cloud has outlined how its Database Migration Service converts SQL Server stored procedures with multiple result sets into PostgreSQL code, addressing a common obstacle in large database migration projects.

The service uses an automated decision process to determine whether a SQL Server procedure should become a PostgreSQL stored procedure or a function. That distinction depends on how many result sets the original routine returns and whether it also uses a scalar return value.

In Google Cloud's model, procedures with one result set or only a scalar return value are translated into PostgreSQL stored procedures. Routines that return multiple result sets, or combine result sets with a scalar return value, are translated into functions that return a SETOF refcursor.

The issue reflects a deeper difference between the two database systems. SQL Server can stream multiple tabular results from a single execution path, while PostgreSQL handles multiple datasets through explicit cursor management.

As a result, organisations moving large estates of database code may need to change not just syntax but execution behaviour. Manual rewriting can become impractical when migrations involve hundreds of stored procedures with nested calls and conditional logic.

Google Cloud illustrated the process with a healthcare reporting example. In that scenario, a master procedure retrieves patient details, calls one child procedure for lab results, calls another for doctor visits, and may return up to four result sets along with a status integer indicating whether the patient was found.

In the PostgreSQL translation, a simpler child procedure that returns one result set becomes a standard procedure with an INOUT refcursor parameter. More complex routines become functions that open cursors in sequence and return each one individually.

One part of the design is the handling of scalar return values. Rather than keeping that status outside the result flow, the translated PostgreSQL function opens a dedicated cursor named return_value at the end of execution and places the scalar integer there.

This changes how applications and testing teams consume outputs from migrated routines. Instead of reading rows directly from a single execution response, the calling layer receives cursor references and must fetch each dataset separately and in order.

Testing changes

Testing these translated objects in PostgreSQL must happen inside an explicit transaction block. Because PostgreSQL cursors are tied to the transaction lifecycle, execution and data retrieval must take place between BEGIN and COMMIT.

In the example, the user first executes the translated function to produce cursor references, then fetches data from each anonymous portal one by one, and finally fetches the return_value cursor. This sequence covers patient demographics, lab result sets, doctor visits, and the scalar status code.

Google Cloud also described the internal analysis used to classify stored procedures before translation. The service first scans a procedure body for direct result sets, looking for explicit SELECT statements that produce tabular output.

Conditional logic and loops complicate that analysis. If a procedure contains conditional SELECT or EXEC statements, the number of result sets can vary between runs, so the routine is treated as having a dynamic result count rather than a fixed one.

Nested procedure calls add another layer. To resolve those dependencies, the service builds a directed graph of calls between procedures, then propagates result-set counts back through the hierarchy using depth-first search.

This graph-based method is intended to determine whether a top-level procedure has no result sets, one result set, or multiple or dynamic result sets after accounting for child routines. The same approach can support direct and indirect recursion inside procedure call chains.

Migration context

The detail matters because database migrations often fail on edge cases hidden in older application logic. Stored procedures that return several datasets are common in legacy SQL Server systems, particularly where a single call was designed to reduce round trips between application and database.

When those routines are moved to PostgreSQL, teams may find that successful code conversion is only part of the task. They also need to update test harnesses, application data access layers, and operational scripts to deal with explicit cursor fetching and transaction-bound result handling.

Google Cloud's description of the Database Migration Service suggests the product is designed to reduce that burden by automating structural rewrites while preserving existing conditional logic. The trade-off is that downstream application components must understand the new response pattern created by PostgreSQL cursor management.

According to Google Cloud, the service categorises each SQL Server procedure into one of three groups: no result sets, a single result set, or multiple or dynamic result sets.