Convertire Stored Procedures e Funzioni da SQL Server a PostgreSQL

Tutorial

PostgreSQL SQL

Le stored procedure e le funzioni implementano la logica aziendale del database. Quando si migra il database SQL Server a PostgreSQL, sarà necessario convertire correttamente stored procedure e funzioni, prestando attenzione alla gestione dei parametri, al recupero dell’insieme di righe e ad altre costruzioni di sintassi specifiche.

SQL Server utilizza un dialetto di SQL chiamato Transact-SQL (o T-SQL) per le stored procedure e le funzioni, mentre PostgreSQL utilizza il Linguaggio Procedurale/PostgreSQL (o PL/pgSQL) per lo stesso scopo. Questi linguaggi hanno una sintassi e capacità significativamente diverse, pertanto le stored procedure e le funzioni devono essere attentamente analizzate e convertite.

Inoltre, alcune funzionalità di T-SQL non hanno equivalenti diretti in PL/pgSQL, e quindi è necessaria un’implementazione alternativa per quei casi. Infine, le stored procedure e le funzioni devono essere ottimizzate per il motore di PostgreSQL per garantire un’efficienza delle prestazioni.

Restituire un insieme di righe

Sia SQL Server chePostgreSQL permettono il ritorno di un insieme di righe, di solito il risultato di una query SELECT, da stored procedure o funzioni, ma la sintassi è differente. Se la stored procedure in T-SQL contiene SELECT come ultimo statement del corpo, questo significa che ritorna un insieme di righe. PL/pgSQL richiede la dichiarazione preventiva dell’insieme di righe restituito come tabella o il recupero dei dati tramite refcursor.

Quando l’insieme di righe restituito ha solo poche colonne con tipi chiari, è possibile utilizzare la funzionalità RETURNS TABLE di PostgreSQL.

In T-SQL:

SQL

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

In PL/pgSQL, lo stesso potrebbe apparire così:

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;

E il codice chiamante PostgreSQL potrebbe apparire così:

SQL

SELECT * FROM GetCustomerOrders(5);

Se l’insieme di righe restituito è più complicato e è difficile determinare il tipo di dati per ciascuna colonna, l’approccio sopra potrebbe non funzionare. Per quei casi, il workaround è utilizzare 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;

E il codice chiamante PostgreSQL potrebbe apparire così:

SQL

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

Dichiarazione delle Variabili Locali

T-SQL consente di dichiarare variabili locali ovunque all’interno del corpo di una stored procedure o funzione. PL/pgSQL richiede che tutte le variabili locali siano dichiarate prima della parola chiave BEGIN:

SQL

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

In SQL Server, le variabili di tabella possono essere dichiarate come segue:

SQL

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

PostgreSQL non supporta questa funzionalità; dovrebbero essere utilizzate tabelle temporanee invece:

SQL

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

Ricorda che le tabelle temporanee vengono eliminate automaticamente alla fine della sessione o della transazione corrente. Se hai bisogno di gestire esplicitamente la durata della tabella, usa l’istruzione DROP TABLE IF EXISTS.

Fai attenzione al corretto mapping dei tipi da SQL Server a PostgreSQL quando converti la dichiarazione delle variabili.

Ultimo valore della colonna auto-incrementale

Dopo aver eseguito una query INSERT, potrebbe essere necessario ottenere il valore generato della colonna auto-incrementale. In T-SQL, può essere ottenuto come

SQL

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

PostgreSQL consente l’accesso all’ultimo valore inserito tramite una sequenza generata automaticamente che ha sempre il nome {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’);

Funzioni incorporate

Quando si migrano procedure memorizzate e funzioni da SQL Server a PostgreSQL, tutte le funzioni e gli operatori incorporati specifici devono essere convertiti nei rispettivi equivalenti secondo le regole seguenti:

La funzione CHARINDEX deve essere sostituita dal corrispondente di PostgreSQL POSITION
La funzione CONVERT deve essere migrata in PostgreSQL secondo le regole specificate in questo articolo

La funzioneDATEADD($interval, $n_units, $date)può essere convertita in espressioni PostgreSQL che utilizzano l’operatore+a seconda del valore di$intervalcome segue:

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

La funzione DATEDIFF($interval, $date1, $date2) di SQL Server può essere emulata in PostgreSQL tramite DATE_PART nel seguente modo:

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

Ogni occorrenza di DATEPART deve essere sostituita con DATE_PART
La funzione di SQL Server GETDATE deve essere convertita in PostgreSQL NOW()
L’operatore condizionale IIF($condition, $first, $second) deve essere convertito in CASE WHEN $condition THEN $first ELSE $second END
Ogni occorrenza di ISNULL deve essere sostituita con COALESCE
La funzione SQL Server REPLICATE deve essere convertita nell’equivalente in PostgreSQL, REPEAT
Ogni occorrenza di SPACE($n) deve essere sostituita con REPEAT(' ', $n)

Conclusione

La migrazione di stored procedure e funzioni tra due DBMS è una procedura piuttosto complicata che richiede molto tempo e sforzo. Anche se non può essere completamente automatizzata, alcuni strumenti disponibili online potrebbero aiutare a automatizzare parzialmente la procedura.

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