המרת פרוצדורות ופונקציות מ- SQL Server ל- PostgreSQL

מדריכים
PostgreSQL SQL

נהגים ופונקציות מאצטחים את הלוגיקה העסקית של מסד הנתונים. כאשר מבצעים העברת בסיס הנתונים של SQL Server ל־PostgreSQL, יהיה עליך להמיר נכון נהגים ופונקציות, ולהתייחס לטיפול בפרמטרים, לשחזור סט של שורות ולתקנות גרמר ספציפיים אחרים.

SQL Server משתמש בדיאלקט של SQL בשם Transact-SQL (או T-SQL) עבור נהגים ופונקציות, בעוד ש־PostgreSQL משתמש בשפת תוכנה/PostgreSQL (PL/pgSQL) לאותן מטרות. שפות אלו מציעות תחביר ויכולות שונות באופן משמעותי, ולכן יש לנפות בזהירות נהגים ופונקציות ולהמיר אותם.

כמו כן, ישנם תכונות ב־T-SQL שאין להן שקילות ישירה ב־PL/pgSQL, ולכן, נדרשת מימוש חלופי למקרים אלו. לבסוף, יש לאופטימזציה של נהגים ופונקציות עבור מנוע ה־PostgreSQL כדי להבטיח ביצועים יעילים.

החזרת סט של שורות

גם SQL Server וגם PostgreSQL מאפשרים להחזיר אוסף שורות, בדרך כלל כתוצאה משאילתת SELECT, מתוך פרוצדורות מאוחסנות או פונקציות, אך הסינטקס שונה. אם הפרוצדורה המאוחסנת ב-T-SQL מכילה SELECT כהצהרה האחרונה של הגוף, זה אומר שהיא מחזירה אוסף שורות. PL/pgSQL דורש או הצהרה קדימה של אוסף השורות המוחזר כטבלה או משיכת נתונים דרך refcursor. 

כאשר אוסף השורות המוחזר מכיל רק כמה עמודות עם סוגים ברורים, ניתן להשתמש בתכונת RETURNS TABLE של PostgreSQL. 

ב-T-SQL:

SQL

  

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

 

ב-PL/pgSQL, זה יכול להיראות כך: 

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;

וקוד ה-PgSQL של המתקשר יכול להיראות כך:

SQL

  

SELECT * FROM GetCustomerOrders(5);

אם אוסף השורות המוחזר מורכב יותר וקשה לקבוע את סוג הנתונים עבור כל עמודה, הגישה לעיל עשויה לא לעבוד. במקרים אלה, הפתרון הוא להשתמש בrefcursor.

ב-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

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

וקוד ה-PgSQL של המתקשר יכול להיראות כך:

SQL

  

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

 

הצהרת משתנים מקומיים

T-SQL מאפשרת להצהיר משתנים מקומיים בכל מקום בתוך גוף פרוצדורה מאוחסנת או פונקציה. PL/pgSQL דורש שכל המשתנים המקומיים יוכרזו לפני מילת המפתח BEGIN:

SQL

  

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

 

ב- SQL Server, משתני טבלה ניתן להגדיר כך:

SQL

  

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

 

PostgreSQL אינה תומכת בתכונה זו; במקום צריך להשתמש בטבלאות זמניות:

SQL

  

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

 

זכור כי טבלאות זמניות מתמחקות אוטומטית בסיום ההפעלה או העסקה הנוכחית. אם יש צורך לנהל באופן פעיל את תקופת חיי הטבלה, יש להשתמש בהצהרת DROP TABLE IF EXISTS.

שים לב להתאמת סוגי נתונים מ- SQL Server ל- PostgreSQL במהלך המרה של הצהרות משתנים. 

ערך אחרון של עמודת Auto-Increment

לאחר ביצוע שאילתת INSERT, ייתכן שיהיה צורך לקבל את הערך שנוצר של עמודת ה- auto-increment. ב- T-SQL, ניתן לקבל את זה בתור

SQL

  

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

PostgreSQL מאפשרת גישה לערך האחרון שהוכנס דרך רצף שנוצר באופן אוטומטי שתמיד נקרא {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’);

 

פונקציות מובנות

בעת העברת תהליכי אחסון ופונקציות מ- SQL Server ל- PostgreSQL, כל הפונקציות המובנות והמפעילים המסוימים יש להמיר לשקיפות בהתאם לכללים הבאים:

  • הפונקציה CHARINDEX חייבת להיות מוחלפת בשקיפות של PostgreSQL POSITION
  • הפונקציה CONVERT חייבת להיות מומרת ל־PostgreSQL על פי הכללים שצוינו ב־מאמר זה
  • הפונקציהDATEADD($interval, $n_units, $date) ניתן להמיר לביטויים ב־PostgreSQL המשתמשים באופרטור + בהתאם לערך של$interval כדלקמן:

    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

  • פונקציהDATEDIFF($interval, $date1, $date2) של SQL Server ניתן להדמות ב-PostgreSQL באמצעות DATE_PART כדלקמן:
    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

  • כל מופע של DATEPART ייחלף ב-DATE_PART
  • פונקציית SQL Server GETDATE יומר ל-PostgreSQL NOW()
  • אופרטור תנאי IIF($תנאי, $ראשון, $שני) יומר ל-CASE WHEN $תנאי THEN $ראשון ELSE $שני END
  • כל המופע של ISNULL ייחלף ב- COALESCE
  • פונקציית SQL Server REPLICATE חייבת להיות מומרת לשקילות ב-PostgreSQL, REPEAT
  • כל המופע של SPACE($n) ייחלף ב- REPEAT(' ', $n)

מסקנה

המעבר של הנגזרות והפונקציות בין שני מערכי בסיסי נתונים הוא תהליך מורכב שדורש המון זמן ומאמצים. אף על פי שלא ניתן לאוטומטית את התהליך באופן מלא, כלי זמינים ברשת עשויים לעזור באוטומציה חלקית של התהליך.

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