Converter Procedimentos Armazenados e Funções do SQL Server para o PostgreSQL

Tutoriais

PostgreSQL SQL

Procedimentos armazenados e funções implementam a lógica de negócios do banco de dados. Ao migrar o banco de dados do SQL Server para o PostgreSQL, é necessário converter adequadamente os procedimentos armazenados e funções, prestando atenção ao manuseio de parâmetros, recuperação de conjuntos de linhas e outras construções de sintaxe específicas.

SQL Server utiliza um dialeto de SQL chamado Transact-SQL (ou T-SQL) para procedimentos armazenados e funções, enquanto o PostgreSQL utiliza Linguagem Procedural/PostgreSQL (ou PL/pgSQL) para o mesmo. Essas linguagens têm sintaxes e capacidades significativamente diferentes, portanto, os procedimentos armazenados e funções devem ser cuidadosamente analisados e convertidos.

Também, algumas funcionalidades do T-SQL não possuem equivalentes diretos no PL/pgSQL, e, portanto, é necessária uma implementação alternativa para esses casos. Por fim, os procedimentos armazenados e funções devem ser otimizados para o motor do PostgreSQL para garantir que sejam executados de forma eficiente.

Retornando um Conjunto de Linhas

O SQL Server e PostgreSQL permitem o retorno de um conjunto de linhas, geralmente o resultado de uma consulta SELECT, de procedimentos armazenados ou funções, mas a sintaxe é distinta. Se o procedimento armazenado em T-SQL contiver SELECT como a última instrução do corpo, isso significa que ele retorna um conjunto de linhas. O PL/pgSQL requer ou a declaração antecipada do conjunto de linhas retornado como uma tabela ou a recuperação de dados através de refcursor.

Quando o conjunto de linhas retornado tem apenas algumas colunas com tipos claros, você pode usar o recurso RETURNS TABLE do PostgreSQL.

No T-SQL:

SQL

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

No PL/pgSQL, o mesmo pode parecer assim:

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;

E o código chamador do PostgreSQL pode ser assim:

SQL

SELECT * FROM GetCustomerOrders(5);

Se o conjunto de linhas retornado for mais complicado e for difícil determinar o tipo de dados para cada coluna, a abordagem acima pode não funcionar. Para esses casos, a solução alternativa é usar refcursor.

No 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

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

E o código chamador do PostgreSQL pode ser assim:

SQL

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

Declaração de Variáveis Locais

O T-SQL permite que variáveis locais sejam declaradas em qualquer lugar dentro do corpo de um procedimento armazenado ou função. O PL/pgSQL requer que todas as variáveis locais sejam declaradas antes da palavra-chave BEGIN:

SQL

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

No SQL Server, variáveis de tabela podem ser declaradas da seguinte forma:

SQL

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

O PostgreSQL não suporta esse recurso; em vez disso, devem ser usadas tabelas temporárias:

SQL

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

Lembre-se de que as tabelas temporárias são automaticamente excluídas no final da sessão ou da transação atual. Se você precisar gerenciar explicitamente o tempo de vida da tabela, use a instrução DROP TABLE IF EXISTS.

Preste atenção à apropriada conversão de tipos de SQL Server para PostgreSQL quando declarando variáveis.

Último Valor da Coluna de Auto-Incremento

Após executar a consulta INSERT, você pode precisar obter o valor gerado da coluna de auto-incremento. No T-SQL, isso pode ser obtido como

SQL

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

O PostgreSQL permite acesso ao último valor inserido por meio de uma sequência automaticamente gerada que sempre tem o nome {nomedatabela}_{nomedacoluna}_seq:

SQL

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

Funções Internas

Ao migrar procedimentos armazenados e funções do SQL Server para o PostgreSQL, todas as funções internas específicas e operadores devem ser convertidos em equivalentes de acordo com as regras abaixo:

A função CHARINDEX deve ser substituída pelo equivalente do PostgreSQL POSITION
A função CONVERT deve ser migrada para o PostgreSQL de acordo com as regras especificadas neste artigo

A função DATEADD($interval, $n_units, $date) pode ser convertida em expressões PostgreSQL que utilizam o operador + dependendo do valor de $interval da seguinte forma:

($date + $n_units * interval ‘1 hour’)::date

($date + $n_units * interval ‘1 minute’)::date

($date + $n_units * interval ‘1 month’)::date

($date + $n_units * 3 * interval ‘1 month’)::date

($date + $n_units * interval ‘1 second’)::date

($date + $n_units * interval ‘1 week’)::date

($date + $n_units * interval ‘1 day’)::date

($date + $n_units * interval ‘1 year’)::date

DAY / DD / D / DAYOFYEAR / DY	($date + $n_units * interval ‘1 day’)::date
HOUR / HH
MINUTE / MI / N
MONTH / MM / M
QUARTER / QQ / Q
SECOND / SS / S
WEEK / WW / WK
WEEKDAY / DW / W
YEAR / YY

A função DATEDIFF($interval, $data1, $data2) do SQL Server pode ser emulado no PostgreSQL via DATE_PART da seguinte forma:

DAY / DD / D / DAYOFYEAR / DY	date_part(‘day’, $data2 – $data1)::int
HOUR / HH	24 * date_part(‘day’, $data2 – $data1)::int + date_part(‘hour’, $data2 – $data1)
MINUTE / MI / N	1440 * date_part(‘day’, $data2 – $data1)::int + 60 * date_part(‘hour’, $data2 – $data1) + date_part(‘minute’, $data2 – $data1)
MONTH / MM / M	(12 * (date_part(‘year’, $data2) – date_part(‘year’, $data1))::int + date_part(‘month’, $data2) – date_part(‘month’, $data1))::int
SECOND / SS / S	86400 * date_part(‘day’, $data2 – $data1)::int + 3600 * date_part(‘hour’, $data2 – $data1) + 60 * date_part(‘minute’, $data2 – $data1) + date_part(‘second’, $data2 – $data1)
WEEK / WW / WK	TRUNC(date_part(‘day’, $data2 – $data1) / 7)
WEEKDAY / DW / W	date_part(‘day’, $data2 – $data1)::int
YEAR / YY	(date_part(‘year’, $data2) – date_part(‘year’, $data1))::int

Cada ocorrência de DATEPART deve ser substituída por DATE_PART
A função do SQL Server GETDATE deve ser convertida para PostgreSQL NOW()
O operador condicional IIF($condition, $first, $second) deve ser convertido para CASE WHEN $condition THEN $first ELSE $second END
Cada ocorrência de ISNULL deve ser substituída por COALESCE
A função SQL Server REPLICATE deve ser convertida para o equivalente em PostgreSQL, REPEAT
Cada ocorrência de SPACE($n) deve ser substituída por REPEAT(' ', $n)

Conclusão

A migração de stored procedures e funções entre dois SGDBs é um procedimento bastante complicado que requer muito tempo e esforço. Embora não possa ser completamente automatizada, algumas ferramentas disponíveis online poderiam ajudar a automatizar parcialmente o procedimento.

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