Konvertieren von gespeicherten Prozeduren und Funktionen von SQL Server nach PostgreSQL

Tutorials

PostgreSQL SQL

Gespeicherte Prozeduren und Funktionen implementieren die Geschäftslogik der Datenbank. Bei der Migration der SQL Server-Datenbank nach PostgreSQL müssen gespeicherte Prozeduren und Funktionen ordnungsgemäß konvertiert werden, wobei besonderes Augenmerk auf die Handhabung von Parametern, das Abrufen von Zeilensätzen und andere spezifische Syntaxkonstruktionen gelegt werden muss.

SQL Server verwendet ein SQL-Dialekt namens Transact-SQL (oder T-SQL) für gespeicherte Prozeduren und Funktionen, während PostgreSQL für dasselbe die Prozedursprache/PostgreSQL (oder PL/pgSQL) verwendet. Diese Sprachen haben signifikant unterschiedliche Syntax und Fähigkeiten, daher müssen gespeicherte Prozeduren und Funktionen sorgfältig analysiert und konvertiert werden.

Außerdem haben einige T-SQL-Funktionen keine direkten Entsprechungen in PL/pgSQL, weshalb eine alternative Implementierung für diese Fälle erforderlich ist. Schließlich müssen gespeicherte Prozeduren und Funktionen für den PostgreSQL-Engine optimiert werden, um sicherzustellen, dass sie effizient ausgeführt werden.

Rückgabe eines Zeilensatzes

Sowohl SQL Server als auch PostgreSQLermöglichen die Rückgabe eines Zeilensatzes, normalerweise das Ergebnis einer SELECT-Abfrage, aus gespeicherten Prozeduren oder Funktionen, aber die Syntax unterscheidet sich. Wenn die gespeicherte Prozedur in T-SQL SELECT als letzte Anweisung des Rumpfes enthält, bedeutet dies, dass sie einen Zeilensatz zurückgibt. PL/pgSQL erfordert entweder eine Vorwärtsdeklaration des zurückgegebenen Zeilensatzes als Tabelle oder das Abrufen von Daten über refcursor.

Wenn der zurückgegebene Zeilensatz nur wenige Spalten mit klaren Typen hat, können Sie die Funktion RETURNS TABLE von PostgreSQL verwenden.

In T-SQL:

SQL

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

In PL/pgSQL könnte das folgendermaßen aussehen:

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;

Und der aufrufende PostgreSQL-Code könnte so aussehen:

SQL

SELECT * FROM GetCustomerOrders(5);

Wenn der zurückgegebene Zeilensatz komplexer ist und es schwierig ist, den Datentyp für jede Spalte zu bestimmen, funktioniert der obige Ansatz möglicherweise nicht. Für solche Fälle ist der Workaround die Verwendung von 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;

Und der aufrufende PostgreSQL-Code könnte so aussehen:

SQL

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

Deklaration von lokalen Variablen

T-SQL erlaubt die Deklaration von lokalen Variablen überall im Rumpf einer gespeicherten Prozedur oder Funktion. PL/pgSQL erfordert, dass alle lokalen Variablen vor dem BEGIN-Schlüsselwort deklariert werden:

SQL

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

In SQL Server können Tabellendatenvariablen wie folgt deklariert werden:

SQL

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

PostgreSQL unterstützt dieses Feature nicht; stattdessen sollten temporäre Tabellen verwendet werden:

SQL

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

Denken Sie daran, dass temporäre Tabellen am Ende der Sitzung oder der aktuellen Transaktion automatisch gelöscht werden. Wenn Sie die Lebensdauer der Tabelle explizit verwalten müssen, verwenden Sie die DROP TABLE IF EXISTS-Anweisung.

Achten Sie auf angemessene SQL Server zu PostgreSQL Typen Zuordnung bei der Konvertierung der Variablendeklaration.

Letzter Wert der Auto-Increment-Spalte

Nach dem Ausführen einer INSERT-Abfrage müssen Sie möglicherweise den generierten Wert der Auto-Increment-Spalte abrufen. In T-SQL kann dies wie folgt erfolgen:

SQL

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

PostgreSQL ermöglicht den Zugriff auf den zuletzt eingefügten Wert über eine automatisch generierte Sequenz, die immer den Namen {tablename}_{columnname}_seq hat::

SQL

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

Integrierte Funktionen

Bei der Migration von gespeicherten Prozeduren und Funktionen von SQL Server nach PostgreSQL müssen alle spezifischen integrierten Funktionen und Operatoren gemäß den folgenden Regeln in Äquivalente umgewandelt werden:

Die Funktion CHARINDEX muss durch das PostgreSQL-Äquivalent POSITION ersetzt werden.
Funktion CONVERT muss gemäß den in diesem Artikel festgelegten Regeln nach PostgreSQL migriert werden.

Funktion DATEADD($interval, $n_units, $date) kann in PostgreSQL-Ausdrücke umgewandelt werden, die den Operator + je nach $interval Wert wie folgt verwenden:

TAG / DD / D / DAYOFYEAR / DY	($date + $n_units * interval ‚1 day‘)::date
STUNDE / HH	($date + $n_units * interval ‚1 hour‘)::date
MINUTE / MI / N	($date + $n_units * interval ‚1 minute‘)::date
MONAT / MM / M	($date + $n_units * interval ‚1 month‘)::date
QUARTAL / QQ / Q	($date + $n_units * 3 * interval ‚1 month‘)::date
SEKUNDE / SS / S	($date + $n_units * interval ‚1 second‘)::date
WOCHEN / WW / WK	($date + $n_units * interval ‚1 week‘)::date
WOCHENTAG / DW / W	($date + $n_units * interval ‚1 day‘)::date
JAHR / YY	($date + $n_units * interval ‚1 year‘)::date

Funktion DATEDIFF($interval, $date1, $date2) von SQL Server kann in PostgreSQL über DATE_PART wie folgt emuliert werden:

TAG / DD / D / TAGDESJAHR / DY	date_part(‚day‘, $date2 – $date1)::int
STUNDE / 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)
MONAT / MM / M	(12 * (date_part(‚year‘, $date2) – date_part(‚year‘, $date1))::int + date_part(‚month‘, $date2) – date_part(‚month‘, $date1))::int
SEKUNDE / 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)
WOCHE / WW / WK	TRUNC(date_part(‚day‘, $date2 – $date1) / 7)
WOCHENTAG / DW / W	date_part(‚day‘, $date2 – $date1)::int
JAHR / YY	(date_part(‚year‘, $date2) – date_part(‚year‘, $date1))::int

Jedes Vorkommen von DATEPART muss durch DATE_PART
SQL Server Funktion GETDATE muss in PostgreSQL NOW()
Konditionaler Operator IIF($condition, $first, $second) muss umgewandelt werden in CASE WHEN $condition THEN $first ELSE $second END
Jedes Vorkommen von ISNULL muss durch COALESCE
SQL Server Funktion REPLICATE muss in das PostgreSQL-Äquivalent REPEAT
Jedes Vorkommen von SPACE($n) muss durch REPEAT(' ', $n)

Fazit

Die Migration von gespeicherten Prozeduren und Funktionen zwischen zwei DBMSs ist ein recht komplizierter Vorgang, der viel Zeit und Mühe erfordert. Obwohl er nicht vollständig automatisiert werden kann, könnten einige verfügbare Tools online dabei helfen, den Vorgang teilweise zu automatisieren.

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