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は、ストアドプロシージャや関数から通常、SELECTクエリの結果である行セットを返すことができますが、構文は異なります。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では、常に{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)は、$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 は 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)

結論

二つの DBMS間のストアドプロシージャと関数の移行は非常に複雑な手続きであり、多くの時間と労力を必要とします。完全に自動化することはできませんが、オンラインで利用可能なツールのいくつかは手続きを部分的に自動化するのに役立つ可能性があります。

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