Converteer Stored Procedures en Functies van SQL Server naar PostgreSQL

Handleidingen
PostgreSQL SQL

Opgeslagen procedures en functies implementeren de bedrijfslogica van de database. Bij het migreren van de SQL Server-database naar PostgreSQL, moet u de opgeslagen procedures en functies op de juiste manier converteren, waarbij u let op de verwerking van parameters, het ophalen van rijensets en andere specifieke syntaxisconstructies.

SQL Server gebruikt een dialect van SQL genaamd Transact-SQL (of T-SQL) voor opgeslagen procedures en functies, terwijl PostgreSQL Procedural Language/PostgreSQL (of PL/pgSQL) gebruikt voor hetzelfde. Deze talen hebben aanzienlijk verschillende syntaxis en mogelijkheden, dus opgeslagen procedures en functies moeten zorgvuldig worden geanalyseerd en geconverteerd.

Daarnaast hebben sommige T-SQL-functies geen directe equivalenten in PL/pgSQL, en daarom is een alternatieve implementatie vereist voor die gevallen. Tenslotte moeten opgeslagen procedures en functies geoptimaliseerd worden voor de PostgreSQL-engine om ervoor te zorgen dat ze efficiënt presteren.

Het retourneren van een rijenset

Zowel SQL Server als PostgreSQL staan de terugkeer van een rijset toe, meestal het resultaat van een SELECT query, vanuit opgeslagen procedures of functies, maar de syntaxis is verschillend. Als de opgeslagen procedure in T-SQL SELECT als de laatste verklaring van de body bevat, betekent dit dat het een rijset retourneert. PL/pgSQL vereist ofwel een vooruitlopende declaratie van de geretourneerde rijset als een tabel of het ophalen van gegevens via refcursor.

Wanneer de geretourneerde rijset slechts een paar kolommen met duidelijke types heeft, kun je de RETURNS TABLE functie van PostgreSQL gebruiken.

In T-SQL:

SQL

  

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

 

In PL/pgSQL kan het er als volgt uitzien:

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;

En de aanroepende PostgreSQL-code kan er als volgt uitzien:

SQL

  

SELECT * FROM GetCustomerOrders(5);

Als de geretourneerde rijset ingewikkelder is en het moeilijk is om het datatype voor elke kolom te bepalen, werkt de bovenstaande aanpak mogelijk niet. Voor die gevallen is de oplossing om refcursor te gebruiken.

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;

En de aanroepende PostgreSQL-code kan er als volgt uitzien:

SQL

  

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

 

Declaratie van Lokale Variabelen

T-SQL staat toe dat lokale variabelen overal binnen een opgeslagen procedure of functiebody worden gedeclareerd. PL/pgSQL vereist dat alle lokale variabelen worden gedeclareerd vóór het BEGIN sleutelwoord:

SQL

  

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

 

In SQL Server kunnen tabelvariabelen als volgt worden gedeclareerd:

SQL

  

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

 

PostgreSQL ondersteunt deze functie niet; in plaats daarvan moeten tijdelijke tabellen worden gebruikt: 

SQL

  

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

 

Onthoud dat tijdelijke tabellen automatisch worden verwijderd aan het einde van de sessie of de huidige transactie. Als je de levensduur van de tabel expliciet moet beheren, gebruik dan de DROP TABLE IF EXISTS-verklaring.

Let op het juiste SQL Server naar PostgreSQL typen mapping bij het converteren van variabelen declaratie.

Laatste waarde van Auto-Increment kolom

Na het uitvoeren van een INSERT-query, moet je mogelijk de gegenereerde waarde van de auto-increment kolom ophalen. In T-SQL kan dit worden verkregen als

SQL

  

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

PostgreSQL staat toegang toe tot de laatst ingevoegde waarde via een automatisch gegenereerde sequentie die altijd de naam heeft {tabelnaam}_{kolomnaam}_seq:

SQL

  

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

 

Ingebouwde Functies

Bij het migreren van opgeslagen procedures en functies van SQL Server naar PostgreSQL moeten alle specifieke ingebouwde functies en operatoren worden omgezet in equivalenten volgens de onderstaande regels:

  • Functie CHARINDEX moet worden vervangen door het PostgreSQL-equivalent POSITION
  • Functie CONVERT moet worden gemigreerd naar PostgreSQL volgens de regels zoals gespecificeerd in dit artikel
  • Functie DATEADD($interval, $n_units, $date) kan worden omgezet in PostgreSQL-expressies die de operator + gebruiken, afhankelijk van de waarde van $interval als volgt:

    DAG / DD / D / DAGVJ / DY

    ($date + $n_units * interval ‘1 dag’)::date
    UUR / HH ($date + $n_units * interval ‘1 uur’)::date
    MINUUT / MI / N ($date + $n_units * interval ‘1 minuut’)::date
    MAAND / MM / M ($date + $n_units * interval ‘1 maand’)::date
    KWARTAAL / QQ / Q ($date + $n_units * 3 * interval ‘1 maand’)::date
    SECONDE / SS / S ($date + $n_units * interval ‘1 seconde’)::date
    WEEK / WW / WK ($date + $n_units * interval ‘1 week’)::date
    WEEKDAG / DW / W ($date + $n_units * interval ‘1 dag’)::date
    JAAR / YY ($date + $n_units * interval ‘1 jaar’)::date

  • Functie DATEDIFF($interval, $date1, $date2) van SQL Server kan worden geëmuleerd in PostgreSQL via DATE_PART als volgt:
    DAG / DD / D / DAGVANHETJAAR / DY

    date_part(‘day’, $date2 – $date1)::int
    UUR / HH 24 * date_part(‘day’, $date2 – $date1)::int + date_part(‘hour’, $date2 – $date1)
    MINUUT / MI / N 1440 * date_part(‘day’, $date2 – $date1)::int + 60 * date_part(‘hour’, $date2 – $date1) + date_part(‘minute’, $date2 – $date1)
    MAAND / MM / M (12 * (date_part(‘year’, $date2) – date_part(‘year’, $date1))::int + date_part(‘month’, $date2) – date_part(‘month’, $date1))::int
    SECONDE / 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)
    WEEKDAG / DW / W date_part(‘day’, $date2 – $date1)::int
    JAAR / YY (date_part(‘year’, $date2) – date_part(‘year’, $date1))::int

  • Elk voorkomen van DATEPART moet vervangen worden door DATE_PART
  • SQL Server functie GETDATE moet worden geconverteerd naar PostgreSQL NOW()
  • Conditionele operator IIF($condition, $first, $second) moet worden geconverteerd naar CASE WHEN $condition THEN $first ELSE $second END
  • Elke voorkomen van ISNULL moet worden vervangen door COALESCE
  • De SQL Server-functie REPLICATE moet worden geconverteerd naar de PostgreSQL-equivalent, REPEAT
  • Elke voorkomen van SPACE($n) moet worden vervangen door REPEAT(' ', $n)

Conclusie

De migratie van opgeslagen procedures en functies tussen twee DBMS’en is een vrij gecompliceerde procedure die veel tijd en moeite vereist. Hoewel het niet volledig geautomatiseerd kan worden, kunnen sommige beschikbare online tools helpen bij het gedeeltelijk automatiseren van de procedure.

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