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은 저장 프로시저나 함수로부터 행 집합을 반환할 수 있지만, 구문은 다릅니다. 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은 항상 {테이블명}_{열이름}_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) 함수는 PostgreSQL에서 DATE_PART를 사용하여 에뮬레이션할 수 있습니다. 다음과 같이:
    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는 PostgreSQLNOW()
  • 조건부 연산자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