将存储过程和函数从SQL Server转换为PostgreSQL

教程
PostgreSQL SQL

存储过程和函数负责实现数据库的业务逻辑。在将 SQL Server 数据库迁移到PostgreSQL 时，您需要适当地转换存储过程和函数，注意参数处理、行集检索以及其他特定的语法结构。

SQL Server使用一种称为 Transact-SQL（或 T-SQL）的 SQL 方言来编写存储过程和函数，而 PostgreSQL 使用 Procedural Language/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)可以根据$interval的值转换为使用运算符+的 PostgreSQL 表达式，具体如下：

    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

  • SQL Server中的函数DATEDIFF($interval, $date1, $date2)可以通过DATE_PART在PostgreSQL中进行模拟，如下所示：
    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($condition, $first, $second) 必须转换为 CASE WHEN $condition THEN $first ELSE $second END
  • 每个ISNULL的出现都必须替换为COALESCE
  • SQL Server函数REPLICATE必须转换为PostgreSQL的等效函数REPEAT
  • 每个SPACE($n)的出现都必须替换为REPEAT(' ', $n)

结论

在两个DBMSs之间迁移存储过程和函数是一个相当复杂的过程，需要大量的时间和精力。虽然无法完全自动化，但一些在线工具可以帮助部分自动化该过程。

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