Преобразование хранимых процедур и функций из SQL Server в PostgreSQL

Учебники
PostgreSQL SQL

Хранимые процедуры и функции реализуют бизнес-логику базы данных. При миграции базы данных SQL Server на PostgreSQL необходимо правильно конвертировать хранимые процедуры и функции, обращая внимание на обработку параметров, извлечение наборов строк и другие специфические конструкции синтаксиса.

SQL Server использует диалект SQL, называемый Transact-SQL (или T-SQL), для хранимых процедур и функций, в то время как PostgreSQL использует Procedural Language/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;

И вызывающий код на 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($интервал, $дата1, $дата2) SQL Server можно эмулировать в PostgreSQL с помощью DATE_PART следующим образом:
    DAY / DD / D / DAYOFYEAR / DY

    date_part(‘day’, $дата2 – $дата1)::int
    HOUR / HH 24 * date_part(‘day’, $дата2 – $дата1)::int + date_part(‘hour’, $дата2 – $дата1)
    MINUTE / MI / N 1440 * date_part(‘day’, $дата2 – $дата1)::int + 60 * date_part(‘hour’, $дата2 – $дата1) + date_part(‘minute’, $дата2 – $дата1)
    MONTH / MM / M (12 * (date_part(‘year’, $дата2) – date_part(‘year’, $дата1))::int + date_part(‘month’, $дата2) – date_part(‘month’, $дата1))::int
    SECOND / SS / S 86400 * date_part(‘day’, $дата2 – $дата1)::int + 3600 * date_part(‘hour’, $дата2 – $дата1) + 60 * date_part(‘minute’, $дата2 – $дата1) + date_part(‘second’, $дата2 – $дата1)
    WEEK / WW / WK TRUNC(date_part(‘day’, $дата2 – $дата1) / 7)
    WEEKDAY / DW / W date_part(‘day’, $дата2 – $дата1)::int
    YEAR / YY (date_part(‘year’, $дата2) – date_part(‘year’, $дата1))::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)

Заключение

Миграция хранимых процедур и функций между двумя СУБД – довольно сложная процедура, требующая много времени и усилий. Хотя ее нельзя полностью автоматизировать, некоторые доступные онлайн-инструменты могут помочь частично автоматизировать процесс.

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