Convertir Procedimientos Almacenados y Funciones de SQL Server a PostgreSQL

Los procedimientos almacenados y funciones implementan la lógica empresarial de la base de datos. Al migrar la base de datos de SQL Server a PostgreSQL, deberá convertir los procedimientos almacenados y funciones adecuadamente, prestando atención al manejo de parámetros, la recuperación de conjuntos de filas y otras construcciones de sintaxis específicas.

SQL Server utiliza un dialecto de SQL llamado Transact-SQL (o T-SQL) para procedimientos almacenados y funciones, mientras que PostgreSQL utiliza Procedural Language/PostgreSQL (o PL/pgSQL) para lo mismo. Estos lenguajes tienen sintaxis y capacidades significativamente diferentes, por lo que los procedimientos almacenados y funciones deben ser analizados y convertidos cuidadosamente.

Además, algunas características de T-SQL no tienen equivalentes directos en PL/pgSQL, por lo tanto, se requiere una implementación alternativa para esos casos. Por último, los procedimientos almacenados y funciones deben ser optimizados para el motor de PostgreSQL para garantizar que funcionen de manera eficiente.

Retorno de un Conjunto de Filas

Tanto SQL Server como PostgreSQL permiten devolver un conjunto de filas, generalmente el resultado de una consulta SELECT, desde procedimientos almacenados o funciones, pero la sintaxis es distinta. Si el procedimiento almacenado en T-SQL contiene SELECT como la última instrucción del cuerpo, esto significa que devuelve un conjunto de filas. PL/pgSQL requiere o la declaración previa del conjunto de filas devuelto como una tabla o recuperar datos a través de refcursor.

Cuando el conjunto de filas devuelto tiene solo unas pocas columnas con tipos claros, se puede utilizar la característica RETURNS TABLE de PostgreSQL.

En T-SQL:

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

En PL/pgSQL, lo mismo puede verse así:

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;

Y el código PostgreSQL del llamante puede verse así:

SELECT * FROM GetCustomerOrders(5);

Si el conjunto de filas devuelto es más complicado y es difícil determinar el tipo de datos para cada columna, el enfoque anterior puede no funcionar. Para esos casos, la solución es utilizar refcursor.

En T-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

En PL/pgSQL:

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;

Y el código PostgreSQL del llamante puede verse así:

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

Declaración de Variables Locales

T-SQL permite declarar variables locales en cualquier lugar dentro del cuerpo de un procedimiento almacenado o una función. PL/pgSQL requiere que todas las variables locales se declaren antes de la palabra clave BEGIN:

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

En SQL Server, las variables de tabla se pueden declarar de la siguiente manera:

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

PostgreSQL no admite esta característica; en su lugar se deben utilizar tablas temporales:

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

Recuerda que las tablas temporales se eliminan automáticamente al final de la sesión o la transacción actual. Si necesitas gestionar explícitamente la vida útil de la tabla, utiliza la instrucción DROP TABLE IF EXISTS.

Presta atención a la adecuada conversión de tipos de SQL Server a PostgreSQL cuando declares variables. 

Último Valor de Columna de Autoincremento

Después de ejecutar una consulta de INSERT, es posible que necesites obtener el valor generado de la columna de autoincremento. En T-SQL, esto se puede obtener como

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

PostgreSQL permite acceder al último valor insertado a través de una secuencia automáticamente generada que siempre tiene el nombre {nombretabla}_{nombrecolumna}_seq:

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

Funciones Integradas

Al migrar procedimientos almacenados y funciones de SQL Server a PostgreSQL, todas las funciones integradas y operadores específicos deben convertirse en equivalentes según las reglas siguientes:

• La función CHARINDEX debe ser reemplazada por el equivalente en PostgreSQL POSITION
• La función CONVERT debe ser migrada a PostgreSQL según las reglas especificadas en este artículo
• La función DATEADD($interval, $n_units, $date) puede convertirse en expresiones PostgreSQL que utilizan el operador + dependiendo del valor de $interval de la siguiente manera:

  DÍA / DD / D / DÍADELAÑO / 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
  MES / 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
  DÍADELASMANA / DW / W	($date + $n_units * interval ‘1 day’)::date
  AÑO / YY	($date + $n_units * interval ‘1 year’)::date

• La funciónDATEDIFF($interval, $date1, $date2)de SQL Server se puede emular en PostgreSQL medianteDATE_PARTde la siguiente manera:

  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

• Cada ocurrencia de DATEPART debe ser reemplazada por DATE_PART
• La función de SQL Server GETDATE debe convertirse en NOW()
• El operador condicional IIF($condición, $primero, $segundo) debe convertirse en CASE WHEN $condición THEN $primero ELSE $segundo END
• Cada ocurrencia deISNULL debe ser reemplazada por COALESCE
• La función de SQL ServerREPLICATE debe ser convertida en su equivalente en PostgreSQL, REPEAT
• Cada ocurrencia de SPACE($n) debe ser reemplazada por REPEAT(' ', $n)

Conclusión

La migración de procedimientos almacenados y funciones entre dos DBMSs es un procedimiento bastante complicado que requiere mucho tiempo y esfuerzo. Aunque no se puede automatizar completamente, algunas herramientas disponibles en línea podrían ayudar a automatizar parcialmente el procedimiento.