Converting Stored Procedures and Functions from SQL Server to PostgreSQL

|

Migrating stored procedures and functions from SQL Server to PostgreSQL is a crucial step when transitioning from Microsoft’s proprietary database system to the open-source PostgreSQL platform. While both databases support procedural code, there are fundamental differences in syntax, features, and execution methodologies.

This article explores how to convert SQL Server stored procedures and functions into PostgreSQL equivalents, using coding examples to demonstrate key differences.

Understanding Key Differences

Before diving into the conversion process, it is essential to understand the fundamental differences between SQL Server’s Transact-SQL (T-SQL) and PostgreSQL’s PL/pgSQL:

  1. Stored Procedures vs. Functions:
    • SQL Server distinguishes between stored procedures (CREATE PROCEDURE) and functions (CREATE FUNCTION), where procedures can execute without returning a value, while functions must return a value.
    • PostgreSQL allows stored procedures (CREATE PROCEDURE) but relies more heavily on functions (CREATE FUNCTION) with RETURNS void when returning no value.
  2. Variable Declaration:
    • SQL Server uses DECLARE @variableName DataType.
    • PostgreSQL uses variableName DataType := value; inside functions and DECLARE variableName DataType; inside procedural blocks.
  3. Control Flow Statements:
    • SQL Server uses BEGIN...END, IF...ELSE, and WHILE loops.
    • PostgreSQL uses PL/pgSQL blocks with BEGIN...END, IF...THEN...ELSE, and LOOP constructs.
  4. Exception Handling:
    • SQL Server uses TRY...CATCH.
    • PostgreSQL uses EXCEPTION WHEN ... THEN.
  5. Returning Values:
    • SQL Server procedures use RETURN or OUTPUT parameters.
    • PostgreSQL functions return values using RETURNS.

Converting SQL Server Stored Procedures to PostgreSQL

Example 1: Simple Stored Procedure with Output Parameter

SQL Server Version

CREATE PROCEDURE GetEmployeeName
    @EmployeeID INT,
    @EmployeeName NVARCHAR(100) OUTPUT
AS
BEGIN
    SELECT @EmployeeName = Name FROM Employees WHERE EmployeeID = @EmployeeID;
END;

PostgreSQL Equivalent

CREATE OR REPLACE FUNCTION GetEmployeeName(EmployeeID INT) RETURNS TEXT AS $$
DECLARE
    EmployeeName TEXT;
BEGIN
    SELECT name INTO EmployeeName FROM Employees WHERE EmployeeID = GetEmployeeName.EmployeeID;
    RETURN EmployeeName;
END;
$$ LANGUAGE plpgsql;

Example 2: Procedure with Transaction Handling

SQL Server Version

CREATE PROCEDURE UpdateEmployeeSalary
    @EmployeeID INT,
    @NewSalary DECIMAL(10,2)
AS
BEGIN
    BEGIN TRANSACTION;
    UPDATE Employees SET Salary = @NewSalary WHERE EmployeeID = @EmployeeID;
    COMMIT TRANSACTION;
END;

PostgreSQL Equivalent

CREATE OR REPLACE PROCEDURE UpdateEmployeeSalary(EmployeeID INT, NewSalary DECIMAL(10,2)) AS $$
BEGIN
    UPDATE Employees SET Salary = NewSalary WHERE EmployeeID = UpdateEmployeeSalary.EmployeeID;
    COMMIT;
END;
$$ LANGUAGE plpgsql;

Converting SQL Server Functions to PostgreSQL

Example 3: Scalar Function

SQL Server Version

CREATE FUNCTION GetTotalOrders(@CustomerID INT) RETURNS INT
AS
BEGIN
    DECLARE @TotalOrders INT;
    SELECT @TotalOrders = COUNT(*) FROM Orders WHERE CustomerID = @CustomerID;
    RETURN @TotalOrders;
END;

PostgreSQL Equivalent

CREATE OR REPLACE FUNCTION GetTotalOrders(CustomerID INT) RETURNS INT AS $$
DECLARE
    TotalOrders INT;
BEGIN
    SELECT COUNT(*) INTO TotalOrders FROM Orders WHERE CustomerID = GetTotalOrders.CustomerID;
    RETURN TotalOrders;
END;
$$ LANGUAGE plpgsql;

Example 4: Table-Valued Function

SQL Server Version

CREATE FUNCTION GetOrdersByCustomer(@CustomerID INT)
RETURNS TABLE
AS
RETURN (
    SELECT OrderID, OrderDate, TotalAmount FROM Orders WHERE CustomerID = @CustomerID
);

PostgreSQL Equivalent

CREATE OR REPLACE FUNCTION GetOrdersByCustomer(CustomerID INT)
RETURNS TABLE(OrderID INT, OrderDate DATE, TotalAmount DECIMAL) AS $$
BEGIN
    RETURN QUERY SELECT OrderID, OrderDate, TotalAmount FROM Orders WHERE CustomerID = GetOrdersByCustomer.CustomerID;
END;
$$ LANGUAGE plpgsql;

Handling Exception and Error Logging

SQL Server Version

BEGIN TRY
    INSERT INTO Orders (CustomerID, OrderDate) VALUES (1, GETDATE());
END TRY
BEGIN CATCH
    PRINT 'An error occurred';
END CATCH;

PostgreSQL Equivalent

BEGIN
    INSERT INTO Orders (CustomerID, OrderDate) VALUES (1, CURRENT_DATE);
EXCEPTION WHEN OTHERS THEN
    RAISE NOTICE 'An error occurred';
END;

Conclusion

Converting SQL Server stored procedures and functions to PostgreSQL requires careful attention to syntax differences, procedural structures, and error handling mechanisms. While SQL Server uses T-SQL with a procedural approach, PostgreSQL relies heavily on PL/pgSQL and supports both procedures and functions with flexible return types.

Key takeaways from this conversion process include:

  • PostgreSQL does not support output parameters in stored procedures the same way SQL Server does; instead, functions returning values are preferred.
  • DECLARE and INTO are crucial for handling variables and query results in PostgreSQL.
  • PostgreSQL exception handling differs from SQL Server’s TRY...CATCH mechanism but provides similar robustness with EXCEPTION blocks.
  • Transactions in PostgreSQL require explicit COMMIT and ROLLBACK when used within procedures.

By following these guidelines and understanding the differences, organizations can efficiently migrate SQL Server procedural code to PostgreSQL, ensuring seamless functionality and improved performance in an open-source environment.