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

Tutoriais
PostgreSQL SQL

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

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

Além disso, alguns recursos do T-SQL não têm equivalentes diretos no PL/pgSQL e, portanto, é necessária uma implementação alternativa para esses casos. Por fim, os procedimentos e funções armazenados devem ser otimizados para o mecanismo do PostgreSQL para garantir que funcionem 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. 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 PostgreSQL pode parecer assim:

SQL

  

SELECT * FROM GetCustomerOrders(5);

Se o conjunto de linhas retornado for mais complicado e for difícil determinar o tipo de dados de 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 PostgreSQL pode parecer 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. 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, as 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; tabelas temporárias devem ser usadas em seu lugar: 

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 removidas no final da sessão ou da transação atual. Se precisar gerenciar explicitamente a vida útil da tabela, use a instrução DROP TABLE IF EXISTS.

Preste atenção na adequada conversão de tipos de SQL Server para PostgreSQL ao declarar as variáveis. 

Último Valor da Coluna de Autoincremento

Após executar a consulta INSERT, talvez seja necessário obter o valor gerado da coluna de autoincremento. 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 o 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 e funções armazenadas de SQL Server para 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 no PostgreSQL POSITION
  • Função CONVERT deve ser migrada para o PostgreSQL de acordo com as regras especificadas neste artigo
  • Função DATEADD($interval, $n_units, $date) pode ser convertida em expressões PostgreSQL que usam o operador + dependendo do valor de $interval como segue:

    DIA / DD / D / DIA_DO_ANO / DY

    ($date + $n_units * interval ‘1 day’)::date
    HORA / HH ($date + $n_units * interval ‘1 hour’)::date
    MINUTO / MI / N ($date + $n_units * interval ‘1 minute’)::date
    MÊS / MM / M ($date + $n_units * interval ‘1 month’)::date
    TRIMESTRE / QQ / Q ($date + $n_units * 3 * interval ‘1 month’)::date
    SEGUNDO / SS / S ($date + $n_units * interval ‘1 second’)::date
    SEMANA / WW / WK ($date + $n_units * interval ‘1 week’)::date
    DIA_DA_SEMANA / DW / W ($date + $n_units * interval ‘1 day’)::date
    ANO / YY ($date + $n_units * interval ‘1 year’)::date

  • Função DATEDIFF($interval, $date1, $date2) do SQL Server pode ser emulado no PostgreSQL via DATE_PART da seguinte forma:
    DIA / DD / D / DIA_DO_ANO / DY

    date_part(‘day’, $date2 – $date1)::int
    HORA / HH 24 * date_part(‘day’, $date2 – $date1)::int + date_part(‘hour’, $date2 – $date1)
    MINUTO / MI / N 1440 * date_part(‘day’, $date2 – $date1)::int + 60 * date_part(‘hour’, $date2 – $date1) + date_part(‘minute’, $date2 – $date1)
    MÊS / MM / M (12 * (date_part(‘year’, $date2) – date_part(‘year’, $date1))::int + date_part(‘month’, $date2) – date_part(‘month’, $date1))::int
    SEGUNDO / 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)
    SEMANA / WW / WK TRUNC(date_part(‘day’, $date2 – $date1) / 7)
    DIA_DA_SEMANA / DW / W date_part(‘day’, $date2 – $date1)::int
    ANO / YY (date_part(‘year’, $date2) – date_part(‘year’, $date1))::int

  • Cada ocorrência de DATEPART deve ser substituída por DATE_PART
  • A função do SQL Server GETDATE deve ser convertida em PostgreSQL NOW()
  • O operador condicional IIF($condition, $first, $second) deve ser convertido em 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 procedimentos armazenados e funções entre dois SGBDs é um procedimento bastante complicado que requer muito tempo e esforço. Embora não possa ser totalmente automatizado, algumas ferramentas disponíveis online podem ajudar a automatizar parcialmente o procedimento.

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