Convertir les procédures stockées et les fonctions de SQL Server en PostgreSQL

Tutoriels
PostgreSQL SQL

Les procédures stockées et les fonctions implémentent la logique métier de la base de données. Lors de la migration de la base de données SQL Server vers PostgreSQL, vous devrez convertir correctement les procédures stockées et les fonctions, en faisant attention à la gestion des paramètres, à la récupération des ensembles de lignes et à d’autres constructions syntaxiques spécifiques.

SQL Server utilise un dialecte SQL appelé Transact-SQL (ou T-SQL) pour les procédures stockées et les fonctions, tandis que PostgreSQL utilise le langage procédural/PostgreSQL (ou PL/pgSQL) pour les mêmes. Ces langages ont une syntaxe et des capacités significativement différentes, il est donc nécessaire d’analyser et de convertir soigneusement les procédures stockées et les fonctions.

De plus, certaines fonctionnalités T-SQL n’ont pas d’équivalents directs en PL/pgSQL, il est donc nécessaire de prévoir une implémentation alternative pour ces cas. Enfin, les procédures stockées et les fonctions doivent être optimisées pour le moteur PostgreSQL afin de garantir des performances efficaces.

Retourner un ensemble de lignes

À la fois SQL Server et PostgreSQL permettent le retour d’un ensemble de lignes, généralement le résultat d’une requête SELECT, à partir de procédures stockées ou de fonctions, mais la syntaxe est différente. Si la procédure stockée en T-SQL contient SELECT comme dernière instruction du corps, cela signifie qu’elle retourne un ensemble de lignes. PL/pgSQL exige soit une déclaration anticipée de l’ensemble de lignes retourné comme une table, soit la récupération des données via refcursor. 

Lorsque l’ensemble de lignes retourné comporte peu de colonnes avec des types clairs, vous pouvez utiliser la fonction RETURNS TABLE de PostgreSQL. 

En T-SQL :

SQL

  

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

 

En PL/pgSQL, cela pourrait ressembler à ceci : 

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;

Et le code appelant PostgreSQL pourrait ressembler à ceci :

SQL

  

SELECT * FROM GetCustomerOrders(5);

Si l’ensemble de lignes retourné est plus complexe et qu’il est difficile de déterminer le type de données pour chaque colonne, l’approche ci-dessus peut ne pas fonctionner. Pour ces cas, la solution de contournement est d’utiliser refcursor.

En 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

En 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;

Et le code appelant PostgreSQL pourrait ressembler à ceci :

SQL

  

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

 

Déclaration de variables locales

T-SQL autorise la déclaration de variables locales partout à l’intérieur du corps d’une procédure stockée ou d’une fonction. PL/pgSQL exige que toutes les variables locales soient déclarées avant le mot-clé BEGIN :

SQL

  

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

 

En SQL Server, les variables de table peuvent être déclarées comme suit :

SQL

  

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

 

PostgreSQL ne prend pas en charge cette fonctionnalité ; des tables temporaires doivent être utilisées à la place :

SQL

  

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

 

N’oubliez pas que les tables temporaires sont automatiquement supprimées à la fin de la session ou de la transaction en cours. Si vous avez besoin de gérer explicitement la durée de vie de la table, utilisez l’instruction DROP TABLE IF EXISTS.

Prêtez attention à une correspondance des types SQL Server vers PostgreSQL appropriée lors de la conversion de la déclaration des variables.

Dernière Valeur de la Colonne d’Auto-Incrémentation

Après l’exécution de la requête INSERT, vous devrez peut-être obtenir la valeur générée de la colonne d’auto-incrément. En T-SQL, cela peut être obtenu comme suit :

SQL

  

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

PostgreSQL permet d’accéder à la dernière valeur insérée via une séquence automatiquement générée qui a toujours pour nom {nomtable}_{nomcolonne}_seq :

SQL

  

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

 

Fonctions Intégrées

Lors de la migration des procédures stockées et des fonctions de SQL Server vers PostgreSQL, toutes les fonctions intégrées spécifiques et les opérateurs doivent être convertis en équivalents selon les règles ci-dessous :

  • La fonction CHARINDEX doit être remplacée par l’équivalent PostgreSQL POSITION
  • Fonction CONVERT doit être migrée dans PostgreSQL selon les règles spécifiées dans cet article
  • La fonctionDATEADD($intervalle, $n_unités, $date)peut être convertie en expressions PostgreSQL qui utilisent l’opérateur + en fonction de la valeur de$intervallecomme suit:

    JOUR / DD / D / JOURDELANNEE / DY

    ($date + $n_unités * interval ‘1 day’)::date
    HEURE / HH ($date + $n_unités * interval ‘1 hour’)::date
    MINUTE / MI / N ($date + $n_unités * interval ‘1 minute’)::date
    MOIS / MM / M ($date + $n_unités * interval ‘1 month’)::date
    TRIMESTRE / QQ / Q ($date + $n_unités * 3 * interval ‘1 month’)::date
    SECONDE / SS / S ($date + $n_unités * interval ‘1 second’)::date
    SEMAINE / WW / WK ($date + $n_unités * interval ‘1 week’)::date
    JOURDELASEMAINE / DW / W ($date + $n_unités * interval ‘1 day’)::date
    ANNÉE / YY ($date + $n_unités * interval ‘1 year’)::date

  • Fonction DATEDIFF($interval, $date1, $date2) de SQL Server peut être émulée dans PostgreSQL via DATE_PART comme suit :
    JOUR / DD / D / JOURDELANNEE / DY

    date_part(‘day’, $date2 – $date1)::int
    HEURE / 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)
    MOIS / 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)
    SEMAINE / WW / WK TRUNC(date_part(‘day’, $date2 – $date1) / 7)
    JOURDELASEMAINE / DW / W date_part(‘day’, $date2 – $date1)::int
    AN / YY (date_part(‘year’, $date2) – date_part(‘year’, $date1))::int

  • Chaque occurrence de DATEPART doit être remplacée par DATE_PART
  • La fonction SQL Server GETDATE doit être convertie en PostgreSQL NOW()
  • L’opérateur conditionnel IIF($condition, $first, $second) doit être converti en CASE WHEN $condition THEN $first ELSE $second END
  • Chaque occurrence de ISNULL doit être remplacée par COALESCE
  • La fonction SQL Server REPLICATE doit être convertie en équivalent PostgreSQL, REPEAT
  • Chaque occurrence de SPACE($n) doit être remplacée par REPEAT(' ', $n)

Conclusion

La migration des procédures stockées et des fonctions entre deux SGBD est une procédure assez compliquée nécessitant beaucoup de temps et d’efforts. Bien qu’elle ne puisse pas être entièrement automatisée, certains outils disponibles en ligne pourraient aider à automatiser partiellement la procédure.

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