Are you migrating a database from SQL Server to PostgreSQL? One key task is converting your stored procedures from T-SQL to PL/pgSQL, PostgreSQL’s procedural language. In this article, you’ll learn a step-by-step process for translating a SQL Server stored procedure to its PostgreSQL equivalent. By the end, you’ll be able to confidently port your T-SQL code to run on Postgres.

Understand the Differences

First, it’s important to understand the key differences between T-SQL and PL/pgSQL:

• PL/pgSQL uses $$ to delimit code blocks instead of BEGIN and END
• Variable declaration syntax is different
• Some built-in functions and operators have different names
• Error handling works differently

Keeping these differences in mind will help as you convert the code.

Follow the Translation Process

Here’s a simple process to follow when translating a stored procedure from SQL Server to PostgreSQL:

1. Create the procedure shell using CREATE FUNCTION
2. Declare variables using DECLARE
3. Convert the procedural logic to PL/pgSQL
4. Adjust any SQL statements to work with Postgres
5. Test the converted function thoroughly

Let’s walk through an example to illustrate. Consider this simple T-SQL procedure:

CREATE PROCEDURE GetCustomerName
  @CustomerID int,
  @CustomerName varchar(50) OUTPUT
AS
BEGIN
  SELECT @CustomerName = FirstName + ' ' + LastName 
  FROM Customers
  WHERE CustomerID = @CustomerID;
END

To convert it to PL/pgSQL:

1. Create the procedure shell:

CREATE FUNCTION get_customer_name(customer_id int, OUT customer_name varchar(50)) 
RETURNS varchar(50) AS $$
DECLARE
BEGIN
END;
$$ LANGUAGE plpgsql;

2. Declare variables – in this case we don’t need any additional variables.
3. Convert the procedural logic:

CREATE FUNCTION get_customer_name(customer_id int, OUT customer_name varchar(50))
RETURNS varchar(50) AS $$
BEGIN
  SELECT INTO customer_name 
    first_name || ' ' || last_name
  FROM customers 
  WHERE customer_id = get_customer_name.customer_id;
END;
$$ LANGUAGE plpgsql;

4. Adjust SQL to work with Postgres – concatenate using || instead of +.
5. Test it:

SELECT get_customer_name(101);

Handle More Complex Procedures

The previous example was quite simple. For more complex stored procedures, a few other things to look out for are:

• Looping and conditional constructs like WHILE and IF
• Transactions using COMMIT and ROLLBACK
• Error handling with EXCEPTION
• Returning result sets with RETURN QUERY

Refer to the PL/pgSQL documentation for details on how to implement these in PostgreSQL.

Conclusion

Converting stored procedures from SQL Server to PostgreSQL is a key part of a database migration. By understanding the differences between T-SQL and PL/pgSQL, following a systematic translation process, and handling the complexities that can arise, you’ll be able to successfully port your stored procedures to Postgres. The effort invested upfront will pay dividends in having your database code run smoothly in its new environment.