將在 SQL Server 中存儲的程序和函數轉換為 PostgreSQL

教學
PostgreSQL SQL

儲存程序和函數實現了資料庫的業務邏輯。在將 SQL Server 資料庫遷移到 PostgreSQL 時，您需要正確轉換儲存程序和函數，特別注意參數處理、行集檢索以及其他特定的語法結構。

SQL Server 使用一種稱為 Transact-SQL（或 T-SQL）的 SQL 方言來編寫儲存程序和函數，而 PostgreSQL 則使用程序語言/PostgreSQL（或 PL/pgSQL）來實現相同的功能。這些語言的語法和功能有顯著的不同，因此儲存程序和函數必須仔細分析和轉換。

此外，一些 T-SQL 特性在 PL/pgSQL 中沒有直接的對應，因此在這些情況下需要替代實現。最後，儲存程序和函數必須針對 PostgreSQL 引擎進行優化，以確保它們能有效執行。

返回行集

SQL Server 和PostgreSQL允許從儲存過程或函數返回行集，通常是 SELECT 查詢的結果，但語法有所區別。如果 T-SQL 中的儲存過程包含 SELECT 作為主體的最後語句，這意味著它返回行集。PL/pgSQL 需要提前聲明返回行集為一個表，或通過 refcursor 獲取數據。

當返回的行集只有幾個明確類型的列時，可以使用 PostgreSQL 的 RETURNS TABLE 功能。

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

而呼叫者的 PostgreSQL 代碼可能是這樣：

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;

而呼叫者的 PostgreSQL 代碼可能是這樣：

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 的類型 映射 在轉換變數宣告時。

自動增量欄位的最後值

在執行 INSERT 查詢後，您可能需要獲取自動增量欄位生成的值。在 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的值如下：

    天 / DD / D / 年中的天數 / DY

    ($date + $n_units * interval ‘1 day’)::date
    小時 / HH ($date + $n_units * interval ‘1 hour’)::date
    分鐘 / MI / N ($date + $n_units * interval ‘1 minute’)::date
    月份 / MM / M ($date + $n_units * interval ‘1 month’)::date
    季度 / QQ / Q ($date + $n_units * 3 * interval ‘1 month’)::date
    秒 / SS / S ($date + $n_units * interval ‘1 second’)::date
    週 / WW / WK ($date + $n_units * interval ‘1 week’)::date
    星期 / DW / W ($date + $n_units * interval ‘1 day’)::date
    年 / YY ($date + $n_units * interval ‘1 year’)::date

  • 函數DATEDIFF($interval, $date1, $date2)在 PostgreSQL 中可以透過DATE_PART來模擬如下：
    天 / DD / D / 年中的第幾天 / DY

    date_part(‘day’, $date2 – $date1)::int
    小時 / HH 24 * date_part(‘day’, $date2 – $date1)::int + date_part(‘hour’, $date2 – $date1)
    分鐘 / MI / N 1440 * date_part(‘day’, $date2 – $date1)::int + 60 * date_part(‘hour’, $date2 – $date1) + date_part(‘minute’, $date2 – $date1)
    月份 / MM / M (12 * (date_part(‘year’, $date2) – date_part(‘year’, $date1))::int + date_part(‘month’, $date2) – date_part(‘month’, $date1))::int
    秒 / 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)
    週 / WW / WK TRUNC(date_part(‘day’, $date2 – $date1) / 7)
    星期幾 / DW / W date_part(‘day’, $date2 – $date1)::int
    年 / YY (date_part(‘year’, $date2) – date_part(‘year’, $date1))::int

  • 每次出現的 DATEPART 必須替換為 DATE_PART
  • SQL Server 函數 GETDATE 必須轉換為 PostgreSQL NOW()
  • 條件運算子 IIF($condition, $first, $second) 必須轉換為 CASE WHEN $condition THEN $first ELSE $second END
  • 每個出現的 ISNULL 必須替換為 COALESCE
  • SQL Server 函數 REPLICATE 必須轉換為 PostgreSQL 對應的 REPEAT
  • 每個出現的 SPACE($n) 必須替換為 REPEAT(' ', $n)

結論

在兩個 DBMS之間遷移存儲過程和函數是一個相當複雜的過程，需要大量時間和精力。雖然無法完全自動化，但一些在線可用的工具可以幫助部分自動化該過程。

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