Convert Stored Procedures and Functions From SQL Server to PostgreSQL
This article explains key differences in stored procedures and functions syntax in SQL Server and PostgreSQL, and basic steps to convert T-SQL code into PL/pgSQL.
Join the DZone community and get the full member experience.
Join For FreeStored 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:
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:
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:
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:
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:
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:
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:
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:
DECLARE @Products TABLE
(
ProductID int,
ProductTitle varchar(100),
ProductPrice decimal (8,2)
)
PostgreSQL does not support this feature; temporary tables should be used instead:
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
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
:
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 equivalentPOSITION
- 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 viaDATE_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 byDATE_PART
- SQL Server function
GETDATE
must be converted into PostgreSQLNOW()
- Conditional operator
IIF($condition, $first, $second)
must be converted intoCASE WHEN $condition THEN $first ELSE $second END
- Every occurrence of
ISNULL
must be replaced byCOALESCE
- SQL Server function
REPLICATE
must be converted into PostgreSQL equivalent,REPEAT
- Every occurrence of
SPACE($n)
must be replaced byREPEAT(' ', $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.
Published at DZone with permission of Vlad Bilyak. See the original article here.
Opinions expressed by DZone contributors are their own.
Comments