Convert Stored Procedures and Functions From SQL Server to PostgreSQL

Tutorials
PostgreSQL SQL

Stored procedures and functions are implementing the business logic of the database. When migrating the SQL Server database to PostgreSQL, you will need to convert stored procedures and functions properly, paying attention to parameter handling, rowset retrieval, and other specific syntax constructions. 

SQL Server uses a dialect of SQL called Transact-SQL (or T-SQL) for stored procedures and functions, while PostgreSQL uses Procedural Language/PostgreSQL (or PL/pgSQL) for the same. These languages have significantly different syntax and capabilities, so stored procedures and functions must be carefully analyzed and converted.

Also, some T-SQL features have no direct equivalents in PL/pgSQL, and therefore, alternative implementation is required for those cases. Finally, stored procedures and functions must be optimized for the PostgreSQL engine to ensure they perform efficiently.

Returning a Rowset

Both SQL Server and PostgreSQL allow the return of a rowset, usually the result of a SELECT query, from stored procedures or functions, but the syntax is distinguished. If the stored procedure in T-SQL contains SELECT as the last statement of the body, this means it returns rowset. PL/pgSQL requires either forward declaration of returned rowset as a table or fetching data through refcursor. 

When returning rowset has just a few columns with clear types, you can use the RETURNS TABLE feature of PostgreSQL. 

In T-SQL:

SQL

  

CREATE PROCEDURE GetCustomerOrders @CustomerID INT
AS
    SELECT OrderID, OrderDate, Amount FROM Orders WHERE CustomerID = @CustomerID;
GO

 

In PL/pgSQL, the same may look like this: 

SQL

  

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

And the caller PostgreSQL code may look like this:

SQL

  

SELECT * FROM GetCustomerOrders(5);

If the returning rowset is more complicated and it is hard to determine the data type for each column, the approach above may not work. For those cases, the workaround is to use refcursor.

In T-SQL:

SQL

  

CREATE PROCEDURE GetSalesByRange
	@DateFrom DATETIME,
	@DateTo DATETIME
AS
    SELECT C.CustomerID, C.Name AS CustomerName, C.FirstName, C.LastName, 
    	C.Email AS CustomerEmail, C.Mobile, C.AddressOne, C.AddressTwo, C.City, 
        C.ZipCode, CY.Name AS Country, ST.TicketID, TT.TicketTypeID, 
        TT.Name AS TicketType, PZ.PriceZoneID, PZ.Name AS PriceZone, 
        ST.FinalPrice AS Price, ST.Created, ST.TransactionType, 
        COALESCE(VME.ExternalEventID, IIF(E.ExternalID = '', NULL, E.ExternalID), '0') AS ExternalID, 
        E.EventID, ES.[Name] AS Section, ST.RowName, ST.SeatName
    FROM [Event] E WITH (NOLOCK) 
    INNER JOIN EventCache EC WITH (NOLOCK) ON E.EventID = EC.EventID
    INNER JOIN SaleTicket ST WITH (NOLOCK) ON E.EventID = ST.EventID
    INNER JOIN EventSection ES WITH (NOLOCK) ON ST.EventSectionID = ES.EventSectionID
    INNER JOIN Customer C WITH (NOLOCK) ON ST.CustomerID = C.CustomerID
    INNER JOIN Country CY WITH (NOLOCK) ON C.CountryID = CY.CountryID
    INNER JOIN TicketType TT WITH (NOLOCK) ON ST.TicketTypeID = TT.TicketTypeID
    INNER JOIN PriceZone PZ WITH (NOLOCK) ON ST.PriceZoneID = PZ.PriceZoneID
    LEFT OUTER JOIN VenueManagementEvent VME ON VME.EventID = E.EventID
WHERE ST.Created BETWEEN @DateFrom AND @DateTo 
ORDER BY ST.Created
GO

In PL/pgSQL:

SQL

  

CREATE OR REPLACE FUNCTION GetSalesByRange (
	V_DateFrom TIMESTAMP(3),
	V_DateTo TIMESTAMP(3),
	V_rc refcursor
) 
RETURNS refcursor
AS $$
BEGIN
	OPEN V_rc FOR
	SELECT C.CustomerID, C.Name AS CustomerName, C.FirstName, C.LastName, 
		C.Email AS CustomerEmail, C.Mobile, C.AddressOne, C.AddressTwo, C.City, 
		C.ZipCode, CY.Name AS Country, ST.TicketID, TT.TicketTypeID, 
		TT.Name AS TicketType, PZ.PriceZoneID, PZ.Name AS PriceZone,
		ST.FinalPrice AS Price, ST.Created, ST.TransactionType,
		COALESCE(
			VME.ExternalEventID, 
			(CASE WHEN E.ExternalID = '' THEN NULL ELSE E.ExternalID END), 
			'0') AS ExternalID, 
		E.EventID, ES.Name AS Section, ST.RowName, ST.SeatName
	FROM Event E 
	INNER JOIN EventCache EC ON E.EventID = EC.EventID
	INNER JOIN SaleTicket ST ON E.EventID = ST.EventID
	INNER JOIN EventSection ES ON ST.EventSectionID = ES.EventSectionID
	INNER JOIN Customer C ON ST.CustomerID = C.CustomerID
	INNER JOIN Country CY ON C.CountryID = CY.CountryID
	INNER JOIN TicketType TT ON ST.TicketTypeID = TT.TicketTypeID
    INNER JOIN PriceZone PZ ON ST.PriceZoneID = PZ.PriceZoneID
	LEFT OUTER JOIN VenueManagementEvent VME ON VME.EventID = E.EventID
	WHERE ST.Created BETWEEN V_DateFrom AND V_DateTo
	ORDER BY ST.Created;

	RETURN V_rc;
END; 
$$ LANGUAGE plpgsql;

And the caller PostgreSQL code may look like this:

SQL

  

BEGIN;
SELECT GetSalesByRange(
	'2024-01-01'::TIMESTAMP(3), 
	'2025-01-01'::TIMESTAMP(3), 
	'mycursorname'
);
FETCH 4 FROM mycursorname;
COMMIT;

 

Declaration of Local Variables

T-SQL allows local variables to be declared everywhere inside a stored procedure or function body. PL/pgSQL requires that all local variables are declared before BEGIN keyword:

SQL

  

CREATE OR REPLACE FUNCTION CreateEvent(…)
AS $$
DECLARE
	v_EventID INT;
	v_EventGroupID INT;
BEGIN
…
END; 
$$ LANGUAGE plpgsql;

 

In SQL Server, table variables can be declared as follows:

SQL

  

DECLARE @Products TABLE
(
	ProductID int,
	ProductTitle varchar(100),
	ProductPrice decimal (8,2)
)

 

PostgreSQL does not support this feature; temporary tables should be used instead: 

SQL

  

CREATE TEMP TABLE Products
(
	ProductID int,
	ProductTitle varchar(100),
	ProductPrice decimal (8,2)
)

 

Remember that temporary tables are automatically dropped at the end of the session or the current transaction. If you need to manage the lifetime of the table explicitly, use the DROP TABLE IF EXISTS statement.

Pay attention to appropriate SQL Server to PostgreSQL types mapping when converting variables declaration.

Last Value of Auto-Increment Column

After running INSERT-query, you may need to get the generated value of the auto-increment column. In T-SQL, it may be obtained as

SQL

  

CREATE TABLE aitest (id int identity, val varchar(20));
INSERT INTO aitest(val) VALUES ('one'),('two'),('three');
SELECT @LastID = SCOPE_IDENTITY();

PostgreSQL allows access to the last inserted value via an automatically generated sequence that always has the name {tablename}_{columnname}_seq:

SQL

  

CREATE TABLE aitest (id serial, val varchar(20));
INSERT INTO aitest(val) VALUES ('one'),('two'),('three');
LastID := currval('aitest_id_seq’);

 

Built-In Functions

When migrating stored procedures and functions from SQL Server to PostgreSQL, all specific built-in functions and operators must be converted into equivalents according to the rules below:

  • Function CHARINDEX must be replaced by PostgreSQL equivalent POSITION
  • Function CONVERT must be migrated into PostgreSQL according to the rules specified in this article
  • Function DATEADD($interval, $n_units, $date) can be converted into PostgreSQL expressions that use the operator + depending on $interval value as follows:

    DAY / DD / D / DAYOFYEAR / DY

    ($date + $n_units * interval ‘1 day’)::date
    HOUR / HH ($date + $n_units * interval ‘1 hour’)::date
    MINUTE / MI / N ($date + $n_units * interval ‘1 minute’)::date
    MONTH / MM / M ($date + $n_units * interval ‘1 month’)::date
    QUARTER / QQ / Q ($date + $n_units * 3 * interval ‘1 month’)::date
    SECOND / SS / S ($date + $n_units * interval ‘1 second’)::date
    WEEK / WW / WK ($date + $n_units * interval ‘1 week’)::date
    WEEKDAY / DW / W ($date + $n_units * interval ‘1 day’)::date
    YEAR / YY ($date + $n_units * interval ‘1 year’)::date

  • Function DATEDIFF($interval, $date1, $date2) of SQL Server can be emulated in PostgreSQL via DATE_PART as follows:
    DAY / DD / D / DAYOFYEAR / DY

    date_part(‘day’, $date2 – $date1)::int
    HOUR / HH 24 * date_part(‘day’, $date2 – $date1)::int + date_part(‘hour’, $date2 – $date1)
    MINUTE / MI / N 1440 * date_part(‘day’, $date2 – $date1)::int + 60 * date_part(‘hour’, $date2 – $date1) + date_part(‘minute’, $date2 – $date1)
    MONTH / MM / M (12 * (date_part(‘year’, $date2) – date_part(‘year’, $date1))::int + date_part(‘month’, $date2) – date_part(‘month’, $date1))::int
    SECOND / SS / S 86400 * date_part(‘day’, $date2 – $date1)::int + 3600 * date_part(‘hour’, $date2 – $date1) + 60 * date_part(‘minute’, $date2 – $date1) + date_part(‘second’, $date2 – $date1)
    WEEK / WW / WK TRUNC(date_part(‘day’, $date2 – $date1) / 7)
    WEEKDAY / DW / W date_part(‘day’, $date2 – $date1)::int
    YEAR / YY (date_part(‘year’, $date2) – date_part(‘year’, $date1))::int

  • Every occurrence of DATEPART must be replaced by DATE_PART
  • SQL Server function GETDATE must be converted into PostgreSQL NOW()
  • Conditional operator IIF($condition, $first, $second) must be converted into CASE WHEN $condition THEN $first ELSE $second END
  • Every occurrence of ISNULL must be replaced by COALESCE
  • SQL Server function REPLICATE must be converted into PostgreSQL equivalent, REPEAT
  • Every occurrence of SPACE($n) must be replaced by REPEAT(' ', $n)

Conclusion

The migration of stored procedures and functions between two DBMSs is quite a complicated procedure requiring much time and effort. Although it cannot be completely automated, some available tools online could help partially automate the procedure.

Source:
https://dzone.com/articles/convert-stored-procedures-sql-server-to-postgresql