DB2（Oracle互換モード）からPostgreSQLのマイグレーションにめちゃくちゃ苦労した話

# はじめに
本記事は数年前（当時は前職）に経験した、DB2（Oracle互換）資材をPostgreSQLにマイグレーションする過程で得た知見や苦労をまとめた記事となります。

DBMSをPostgreSQLに移行するためのノウハウは様々公開されているため、一つひとつを体系的に整理するのではなく、自身の経験を元に、より詳細な修正内容や、大変だった修正の体験談などを中心に記載しようと思います。

DBMSをPostgreSQLに移行する際、本記事が参考になれば幸いです。

# 前提
## マイグレーションの概要について
PJTの詳細は割愛しますが、現行システムのソースにまるまる修正が入る対応のため、改修規模はかなり大きかったです。

改修規模は大きいですが、キーワードレベルで置換可能な構文は一括で置換（※）を行い、残りの置換できない範囲を手修正する形をとり、手修正の量を減らすことで工数削減を図っていました。

と言いつつも実際のところは、想定外の修正が数多く発生し、かつキーワードレベルでの置換で対応できない修正が多く存在したため、かなりの範囲を手修正しましたが・・。

※当時のPJTは独自に作成したツールを用いて、置換前後の正規表現を定義した設定ファイルとソースファイルを読み込ませ、設定ファイルの内容に基づいてソースファイルの一括置換を行っていました。

一例として、
* `Number`型を`Numeric`型に置換する
* `DECODE`関数を`CASE`式に置換する
* `CURRENT TIMESTAMP`を`CURRENT_TIMESTAMP`に置換する
* `FROM DUAL`を削除する

などが挙げられます。

## 自身の担当内容
PostgreSQLへのマイグレーションにおける、当時の自身が担当内容は以下となります。

* ストアドプロシージャの改修
    * PL/SQL→PL/pgSQLに修正
    * コアなビジネスロジックがストアドプロシージャでゴリゴリ実装されていたため、改修規模は大きい
        * 1度に合計50以上のプロシージャやファンクションが呼び出される処理など・・。かなり複雑なソースもあり。
* SQLMAP（ibatisのFWで提供されているSQLやオブジェクトへのマッピングを記述するXMLファイル）の改修
    * SQLMAPに定義されているSQLの構文をDB2（Oracle互換モード）→PostgreSQLに修正
    * Javaの実装がシンプルな一方でSQLはかなり複雑な状態

今回の記事ではあくまでも私自身の経験したマイグレーションの部分を取り上げてお話するため、本記事ではSQL・ストアドプロシージャのマイグレーションがメインとなります。

# PostgreSQLの移行事例
PostgreSQLの移行事例に関して、ネット上でいくつか公開されているので紹介いたします。
## PostgreSQL　エンタープライズ・コンソーシアムのサイト
https://pgecons-sec-tech.github.io/tech-report/


* [ストアドプロシージャ移行調査編](https://pgecons-sec-tech.github.io/tech-report/pdf/wg2_StoredProcedureMigrationResearch_pg11.pdf)
* [SQL移行調査編](https://pgecons-sec-tech.github.io/tech-report/pdf/09_SqlMigrationResearch.pdf)
* [組み込み関数移行調査編](https://pgecons-sec-tech.github.io/tech-report/reports_2013/wg2/10_Built-inFunctionMigrationResearch.pdf)
* [別紙:組み込み関数対応表（Oracle-PostgreSQL）](https://pgecons-sec-tech.github.io/tech-report/reports_2013/wg2/10_Appendix_01_Built-inFunctionComparativeTable(Oracle-PostgreSQL).pdf)

私がPJTに参画したタイミングですでにDB2（Oracle互換モード）資材をPostgreSQLへどのように変換するかの方針は内部資料（見積もり時にpostgreSQLの移行を検証した際の資料）として整理されていたため、PJT内部の資料に加えて必要に応じて上記のURLの資料を参考にして製造を進めた形となります。

## その他、参考になりそうなサイト

https://www.fujitsu.com/jp/products/software/resources/feature-stories/postgres/article-index/migration-plsql/

https://www.insight-ltd.co.jp/tech_blog/postgresql/702/#A1

https://qiita.com/yaju/items/7cf5ab6987ecd1d83b13


# 苦労した点

ここからは、DB2（Oracle互換モード）からPostgreSQLのマイグレーションで苦労した点についてお話していこうと思います。

**上記で紹介したサイトには載っていないような非常に細かい内容や、修正方針が記載されている項目の中でも、修正する立場として大変だったという体験談**を中心に取り上げていきます。

:::note warn
サンプルコードとして記載するソースは基本的に、OracleとPostgreSQLで比較しています。（Oracleのソースに関してはタイトル記載にある「DB2（Oracle互換モード）」と同じ動作をする想定のため）

DB2（Oracle互換モード）固有の動作をする内容のみ、DB2（Oracle互換モード）のサンプルコードを記載して紹介したいと思います。あらかじめご了承ください。
:::

## 空文字とNULLの扱い
**Oracleでは空の文字列はNULLと同値**として扱われる一方で、**PostgreSQLにおいてはこれらは別の値**として扱われます。
そのため、テーブルに空文字を登録する場合もOracleではNULLが登録される一方でPostgreSQLは空文字のまま登録されます。

この違いは調べるとすぐにたどり着けるような内容ですが、いざ修正すると非常に大変でした。
具体的にどのような点で大変だったか、具体例を挙げると以下となります。

* `IS NULL`、`IS NOT NULL`演算子の比較対象の値の修正が必要
* 空文字に対するキャストに注意が必要
* 文字列結合演算子（`||`）から`CONCAT関数`への修正が必要
* Java側のNULL判定が効かなくなるなど、修正が必要

###  `IS NULL`、`IS NOT NULL`演算子の比較対象の値の修正が必要 について
空文字の値に関して`IS NULL`、`IS NOT NULL`演算子の結果がOracleとPostgreSQLで異なります。
```:Oracle
SQL> select
  2    case
  3      when '' is null     then 'NULLです'
  4      when '' is not null then 'NULLではない'
  5    end as NULL判定 from dual;

NULL判定
--------------------------------
NULLです
```

```:PostgreSQL（変更前）
testdb=# select
testdb-#   case
testdb-#     when '' is null     then 'NULLです'
testdb-#     when '' is not null then 'NULLではない'
testdb-#   end as NULL判定;
   null判定
--------------
 NULLではない
(1 行)
```

そのため、`nullif関数`などを利用して、空文字をNULLとして扱うように修正する必要があります。
```:PostgreSQL（変更後）
testdb=# select
testdb-#   case
testdb-#     when nullif('', '') is null     then 'NULLです'
testdb-#     when nullif('', '') is not null then 'NULLではない'
testdb-#   end as NULL判定 ;
 null判定
----------
 NULLです
(1 行)
```

修正自体は難しくなさそうに見えますが、実際に修正する立場になると、

* **利用箇所が膨大である**
* **一括での置換が難しい（`Number`→`Numeric`のような単純な置換でない）**
* **修正が漏れていたとしても構文エラーとならず動作してしまうため、動作確認時に見落とされがち**

という点で、全ソースを漏れなく修正しきるのは骨が折れます。

結局、後工程で修正漏れが見つかったため、Grepして愚直に横展開をすることになりました・・。

### 空文字に対するキャストに注意が必要 について
PostgreSQLで空文字に対してキャストを行う場合、**一部のデータ型（※）においてはエラーが発生**します。
※`numeric`などの数値データ型、`date`などの日付／時刻データ型、`boolean`など

一方、Oracleの場合はNULLとして扱われるため、NULLが返却されます。

```:oracle
SQL> select cast('' as numeric(5, 0)) as 数値型にキャスト from dual;
数値型にキャスト
----------------

```

```:PostgreSQL
testdb=# select cast('' as numeric) as 数値型にキャスト;
ERROR:  "numeric"型の入力構文が不正です: ""
行 1: select cast('' as numeric(5, 0)) as 数値型にキャスト;
```

上記に関しても、PostgreSQLにおいては`nullif`関数などを使用して、空文字をNULLに変換してからキャストを行う必要があります。

`IS NULL`、`IS NOT NULL`演算子とは異なり、実行時にエラーとなるため、エラーの検知は容易です。しかし、当時の動作確認や単体テストでは、レコードの値やPL/pgSQLの変数の値が空文字であるデータでのテストができていなかったため、この問題を検知するのは単体テストの終盤になってからでした。

この問題も機械的な置換が難しく、修正する必要がある箇所が非常に多いため、製造メンバーの作業を止めて人海戦術で横並び対応をすることに・・。

### 文字列結合演算子（`||`）から`CONCAT関数`への修正が必要 について
これは上記2つと若干意味合いが異なりますが、文字列結合演算子（`||`）を利用する際、**PostgreSQLにおいて、結合対象の値にNULLが含まれている場合、演算の結果自体がNULLで返却**されます。
**OracleではNULLの値は無視され（※）、演算の結果自体がNULL**となりません。

※[OracleのSQL言語リファレンス](https://docs.oracle.com/cd/F39414_01/sqlrf/Concatenation-Operator.html#GUID-08C10738-706B-4290-B7CD-C279EBC90F7E)の内容を確認すると、厳密には片方のみNULLの場合はもう一方の値を返すという旨の記載があります。

>Oracleは、長さが0(ゼロ)の文字列をNULLとして処理しますが、長さが0(ゼロ)の文字列を別のオペランドと連結すると、その結果は常にもう一方のオペランドになります。結果がNULLになるのは、2つのNULL文字列を連結したときのみです。ただし、この処理はOracle Databaseの今後のバージョンでも継続されるとはかぎりません。nullになる可能性のある式を連結する場合は、NVL関数を使用してその式を長さ0の文字列に明示的に変換してください。

この差分を解消するためには、**文字列結合演算子（`||`）から`CONCAT`関数への修正**が必要となります。

```:oracle
SQL> select 'Oracle ' || NULL || ' PostgreSQL' AS 文字列結合結果 from dual;

文字列結合結果
------------------------------------
Oracle  PostgreSQL
```

```:PostgreSQL（変更前）
testdb=# select 'Oracle ' || NULL || ' PostgreSQL' AS 文字列結合結果;
 文字列結合結果
----------------

(1 行)
```

```:PostgreSQL（変更後）
testdb=# select concat('Oracle ' , NULL , ' PostgreSQL') AS 文字列結合結果;
 文字列結合結果
----------------
Oracle  PostgreSQL
(1 行)
```

上記の場合、修正は比較的容易ですが、現行システムではSQLの実行結果やPL/pgSQLの処理内でログメッセージを成形するため、カラムの値や変数、文字列を結合する処理が多くありました。これにより、構文が複雑になり修正作業が手間を要することが多かったです。

何回、concat関数の閉じ括弧（`)`）の位置を間違えて構文エラーが発生したか・・。

```:修正に手間がかかるパターンの例（実際のソースはより複雑です）
testdb=# select
testdb-#     lpad(t1.empno, 3, ' ') ||
testdb-#     ',' || lpad(t1.empname, 40, ' ') ||
testdb-#     ',' || lpad(( select t2.posname from pos t2 where t1.poscode = t2.poscode), 20, ' ')  AS 社員データ
testdb-# from emp t1 limit 1;
                               社員データ
-------------------------------------------------------------------------
   1,                                    営業部長,                  部長
```

:::note warn
※DB2はPostgreSQLと同様に、結合対象の値にNULLが含まれている場合、演算の結果自体がNULLで返却されるため、Oralce互換モード固有の動作の認識です。
DB2における文字列結合の動作に関しては、新卒１年目の時に[あんな記事](https://qiita.com/ebichan_88/items/748e709cb874de2d0759)や[こんな記事](https://qiita.com/ebichan_88/items/e5a7907d6a174de387bc)を書いていたな・・。
:::

### Java側のNULL判定が効かなくなるなど、修正が必要 について
現行システムのソースにおいて、ibatisでSQLを実行して取得したデータ対して、NULL判定を行う処理がありました。
このソースがPostgreSQLとなり、空文字とNULLが区別される状態となったため、**空文字のデータが存在する場合にNULL判定をすり抜け、現行システムと動作が異なる事象を検知**しました。

サーバーサイド側でSQLの実行結果に空文字が含まれることを想定しない実装となっていたため、JavaでDBアクセスに関するクラスに対して、以下の対応を実施しました。

* SQLを実行してデータを取得する際に、空文字の場合はNULLに変換して取得する
* SQLにパラメーターを渡す際に、空文字の場合はNULLに変換して値を渡す

これは共通処理を修正することで対応が可能であり、これまでの内容に比較して修正工数は小さいですが、SQLやPL/pgSQLだけでなく、Javaなど、SQLの呼び出し元にも影響のある可能性があるという点に留意頂ければと思います。

### どうすればよかったのか
空文字とNULLの扱いについてかなり苦労しました。
できた取り組み・できなかった取り組みそれぞれありますが、まとめると以下の点に気を付ける必要があると思います。

#### データをNULLで扱い、空文字・NULLの違いによるリスクを減らす
* 移行データに空文字が存在する場合はNULLに修正して移行する
* INSERT・UPDATEする値に空文字が存在する場合はNULLに修正する
    * べた書きの空文字は容易に修正可能
    * 一方で、`Trim関数`でスペースを削除する際、値がオールスペースの場合に空文字となる、というケースに注意する必要がある
* SQLを実行してデータを取得する際に、空文字の場合はNULLに変換して取得する
* SQLにパラメーターを渡す際に、空文字の場合はNULLに変換して値を渡す

#### 空文字の場合でも問題が発生しないよう、漏れなく修正する仕組みを作る
* 製造時のチェックリストを作成し、今回のケースを取り込む
    * 製造時のセルフレビュー、レビュアーのレビュー時に活用
* 単体テストのデータパターン観点に、空文字・NULLの値を取り入れる
    * 観点が取り込まれているかはレビューで担保する
* 結合テスト以降で不具合を検知したら全ソース対象に横並びを実施する
    * 単体テストで空文字・NULLのデータパターンの試験をしているのであれば、単体テストでは横並びは必ずしも必要でない認識（テストで検知できるので単体テストで横並びを行うと余分に工数がかかる）

## 暗黙的な型変換
Oracleでは暗黙的な型変換（※）が積極的に行われている一方で、PostgreSQLは暗黙的な型変換に関して消極的です。
※暗黙的な型変換のパターンは[Oracleのリファレンス参照](https://lets.postgresql.jp/documents/tutorial/cast/)

一例ですが、暗黙的な型変換が行われない場合、Where句での絞り込みなど**文字列型と数値型で比較するケースにおいてエラーが発生**します。

```:PostgreSQL
testdb=# select * from dept;
 deptno | deptname
--------+----------
 0      | 営業部
 1      | 開発部
 2      | 人事部
(3 行)
testdb=# select * from dept where deptno = 1;
ERROR:  演算子が存在しません: character = integer
行 1: select * from dept where deptno = 1;
                                      ^
HINT:  指定した名称と引数の型に合う演算子がありません。明示的な型キャストが必要かもしれません。
```

暗黙の型変換対応も非常に大変であり、具体例としては以下が挙げられます。

* `LPAD`や`SUBSTR`などの標準関数、ユーザー定義関数、プロシージャを呼び出す際、引数のデータ型不一致によるエラーが発生
* WHRER句や外部結合のON句で値を比較する際にデータ型不一致によるエラーが発生
* Java側の修正が必要
* 不具合発生時の横展開対応が困難

### `LPAD`や`SUBSTR`などの標準関数、ユーザー定義関数、プロシージャを呼び出す際に、引数のデータ型不一致によるエラーが発生 について
`LPAD`や`SUBSTR`などの標準関数の多くはOracle・PostgreSQLどちらにおいても利用可能です。
そのため、マイグレーションを開始した当初、標準関数の修正はPostgreSQLで対応していないOracleの標準関数に対して発生する認識でしたが、暗黙的な型変換の違いにより、**PostgreSQLで利用できる標準関数にも修正が発生する**ことになりました。

PostgreSQLでも利用できるから修正工数はかからない。という安易な判断はしないほうが良いです。

```:Oracle
SQL> select lpad(123, '6', 'A') from dual;
LPAD(123,'6'
------------
AAA123
```

```:PostgreSQL
testdb=# select lpad(123, '6', 'A');
ERROR:  関数lpad(integer, unknown, unknown)は存在しません
行 1: select lpad(123, '6', 'A');
             ^
HINT:  指定した名前と引数型に合致する関数がありません。明示的な型変換が必要かもしれません。
```

### WHRER句や外部結合のON句で値を比較する際にデータ型不一致によるエラーが発生 について
これはタイトルの通りなのですが、私が担当していた現行システムは、**同じカラム名でもテーブルにより、数値型と文字列型のカラムになっているものが混在**していたため、それぞれのテーブルを結合している個所は軒並みエラーとなりました。

そのような背景もあり、今回のマイグレーションではこのパターンによる修正も数多く発生しました。
### Java側の修正 について
Java側からSQLMAPに`String型`のパラメータを渡す際、**パラメータの利用箇所（関数の引数、WHRER句の比較対象の値）がnumeric型などの数値型の場合、データ型不一致によるエラーが発生**します。
現行システムのソースは基本的に`String型`の値をSQLMAPに渡していたため、SQLのみ修正した状態で画面の動作確認を行うとデータ型不一致によるエラーが多発し、まともに疎通ができない状態でした。

SQLMAPに渡すパラメータの値を数値型に変更する必要があったため、`String型`→`BigDecimal型`に安全に型変換を行う共通関数を作成して、パラメータを渡す際に`BigDecimal型`へ変換するという対応を行いました。（※）

※SQLMAP側で定義されているSQLに対して、数値型にキャストする処理を追加すればJava側の改修は不要となりすが、パラメータをSQLMAPに渡すタイミングで正しい型にするのがあるべき姿だろうと判断し、Java側の改修に至りました。

ただし、今となっては[ibatisの仕組み（TypeHandlerなど）を活用](https://ibatis.apache.org/docs/java/pdf/iBATIS-SqlMaps-2_ja.pdf)して、SQLMAP側に閉じたほうが改修量は抑えられたのかなと思っています・・。

### 不具合発生時の横展開対応が困難 について
暗黙的な型変換は他の修正方針と比較して**修正箇所の全容を洗い出すのが困難**です。
空文字とNULLの扱いの話では、一括の置換が困難という話をしましたが、`IS NULL`、`IS NOT NULL`などでGrepすることで、修正箇所の特定自体は可能でした。

一方、暗黙的な型変換の場合は呼び出し元の引数の型、変数の値、カラムのデータ型、Javaから渡されるパラメータの値など、意識する点が多くあるため、単純にGrepして特定するというのは難しいです。

不具合が発生した同一ファイル内範囲での横並び、同一カラム名での横並びはある程度可能ですが、全容を洗い出すというのは困難であり、出たとこ勝負というのが正直なところでした。

~~逆に[暗黙の型変換をPostgreSQLにも導入してみる](https://qiita.com/seiketkm/items/9d069348c2906a6ae011)という案を提案してみましたが却下されました。~~

## 一時表（TEMPORARY TABLE）
Oracleにおいて一時表（TEMPORARY TABLE）とはトランザクション終了時、またはセッション終了時にテーブル内のデータが削除されるテーブルです。
一方、PostgreSQLにおいてはデータのみならず、**一時表自体が削除されてしまいます。**

対応としては一時表を利用する処理の直前で一時表をCreateを行えばよいですが、現行システムは何十ものプロシージャが呼び出される複雑なビジネスロジック内で一時表を利用していたため、実際に対応する際はもう一工夫加えました。

### 対応内容

1. **一時表のDDL定義（`CTEATE TEMPORARY TABLE IF NOT EXISTS テーブル名...`）管理するTBLを作成**
    * 一時表の名称、一時表のDDL定義の2カラムで構成
        * 一時表のDDL定義の変更があった場合にDBパッチで対応が可能
        * パッチで対応可能なため、DDL定義変更時のPL/pgSQLの修正が不要
    * 既に一時表が作成されている可能性を考慮して、DDLには`IF NOT EXISTS`のオプションを指定
2. **一時表を作成するプロシージャを新規実装**
    * 引数に一時表の名称を指定して、1.で作成したTBLの一時表の名称に紐づくDDLを実行
    * 同一セッション内で過去に利用したレコードが残っている可能性を考慮して、オプションとして、Create実行後にTRUNCATEを行うか判定する処理を実装
3. **一時表が呼び出される処理の先頭で、2.のプロシージャを呼び出す処理を追加し、一時表を作成**
    * どのタイミングで一時表を作成するかは有識者に協力頂き、2.のプロシージャの呼び出し個所を検討

## 互換性の無い関数／パッケージ

関数の互換性に関しては、[別紙:組み込み関数対応表（Oracle-PostgreSQL）](https://pgecons-sec-tech.github.io/tech-report/reports_2013/wg2/10_Appendix_01_Built-inFunctionComparativeTable(Oracle-PostgreSQL).pdf)にかなり詳細に記載があるため、基本的にはそちらを参考にすればよいと思います。
一方で、上記資料にも記載がない内容や、DB2（Oracle互換モード）⇔PostgreSQLのケースにおいてはそのまま流用できない関数について言及していこうと思います。

### NVL／COALESCE

`NVL`関数はPostgreSQLには存在しない関数のため、Oracleから移行する際は`COALESCE`に変更するのが[別紙:組み込み関数対応表（Oracle-PostgreSQL）](https://pgecons-sec-tech.github.io/tech-report/reports_2013/wg2/10_Appendix_01_Built-inFunctionComparativeTable(Oracle-PostgreSQL).pdf)にも記載があります。
ただし、第一引数の値がNULLの場合は[空文字とNULLの扱い](#空文字とNULLの扱い)の違いのために動作差異が発生します。

```:Oracle
SQL> select COALESCE('', 'NULLです') AS NULL判定 from dual;
NULL判定
--------------------
NULLです
SQL> select NVL('', 'NULLです') AS NULL判定 from dual;
NULL判定
--------------------
NULLです
```

```:PostgreSQL
testdb=# select coalesce('', 'NULLです') AS NULL判定;
 null判定
----------

(1 行)
```

#### 対応方法

一例ですが、`NVL`関数に関しては、標準関数の`COALESCE`関数で代用するのではなく、互換関数を作成し、互換関数の内部処理で`NULLIF`関数を利用して空文字とNULLを同一で扱うことで、代用が可能です。

```sql:NVL互換関数の一例
CREATE OR REPLACE FUNCTION test_plpgsql.NVL(VARCHAR, VARCHAR) RETURNS TEXT AS $$ 
DECLARE	
BEGIN
    RETURN COALESCE(NULLIF($1, ''), $2);
END;
$$ LANGUAGE plpgsql STABLE;
```

```:実行結果
testdb=# select test_plpgsql.nvl('', 'NULLです') AS NULL判定;
 null判定
----------
 NULLです
(1 行)

```

:::note warn
 一例として、互換関数の一例として`NVL(VARCHAR, VARCHAR)`を取り上げましたが、
 第一引数、第二引数に指定するデータ型のパターンは様々存在するため、必要に応じて追加してください。
:::
 
 
### 引数に文字列の長さを指定する関数／長さ自体を返却する関数

:::note warn
以下、取り上げる関数は**DB2（Oracle互換モード）⇔PostgreSQL**で発生する差分となります。
:::

引数に文字列の長さを指定する関数や長さ自体を返却する関数は一例として以下が挙げられます。
以下、表の中で、`SUBSTRB／LENGTHB`を除く関数は**文字数**を扱っていますが、これはあくまでもOracle、PostgreSQLの話であり、**DB2は文字数ではなく、バイト数で扱われます。**


|関数名|説明|補足|
|--------|--------|--------|
| SUBSTR(char, m, n) | char のm 番目から n 文字分の文字列を抜き出して戻す。  ||
| SUBSTRB(char, m, n)  | char のm 番目から n バイト分の文字列を抜き出して戻す。|PostgreSQLには存在しない関数|
| LPAD(char1, n, char2)  | char1 の左に char2 に指定した文字を連続的に埋め込んで n 桁にして戻す。  ||
| RPAD(char1, n, char2) | char1 の右に char2 で指定した文字を連続的に埋め込み、長さ n にして戻す。  ||
| LENGTHB(char) | char の長さを文字単位で戻す。 |PostgreSQLには存在しない関数|
| LENGTH(char) | char の長さをバイト単位で戻す ||

そのため、対象の文字列が**１バイト文字とマルチバイト文字が混在しているケース**において、動作差異が発生します。

```:Oracle　※※文字数で扱われる※※
SQL> select substr('あいABCDEF', 3, 5) AS substr from dual;
SUBSTR
----------
ABCDE
```

```:PostgreSQL　※※文字数で扱われる※※
testdb=# select substr('あいABCDEF', 3, 5) AS substr;
 substr
--------
 ABCDE
(1 行)
```

```:DB2（Oracle互換モード）※※バイト数で扱われる※※
[db2inst1@db2server /]$ db2 "select substr('あいABCDEF', 3, 5) AS substr from dual"
SUBSTR
------
 いA
  1 record(s) selected.
```

#### 対応方法

* SUBSTR／LPAD／RPAD
    * 対象の文字列が半角英数字等、すべて１バイト文字の場合はそのままでOK
    * 対象の文字列が１バイト文字とマルチバイト文字が混在している場合はバイト数を扱えるように互換関数の作成が必要
        * 互換関数作成時は、移行前のDBと移行後のDBの文字コード（※1）の扱いに注意が必要
* SUBSTRB
    * 互換関数の作成が必要
        * 互換関数作成時は、移行前のDBと移行後のDBの文字コード（※1）の扱いに注意が必要
* LENGTHB／LENGTH
    * 基本的には`octet_length`関数で代用可能だが、Char型を扱う場合は注意が必要（※2）
    * Char型のケースも救いたい場合は、互換関数作成を検討

#### ※1 文字コードについて
移行前のDBと移行後のDBの文字コードが異なる場合、同じ文字でもバイト数が異なる場合があるため、互換関数を作成する際は注意が必要です。以下、一例を記載します。
| | 全角文字の1文字 | 半角カナ文字の1文字 |
|--------|--------|--------|
| SJIS | 2バイト | 1バイト |
| UTF-8 | 3バイトまたは4バイト | 3バイト|

#### ※2 octet_lengthの動作について
`octet_length`関数は文字列中のバイト数を扱う関数であり、DB2（Oracle互換モード）の`LENGTHB／LENGTH`関数の代用として利用可能ですがChar型の長さを取得する際、DB2とPostgreSQLで動作が異なります。

DB2（Oracle互換モード）の場合はChar型の長さが取得される一方で、
PostgreSQLの場合、Char型かつ全角文字を含む場合、以下の計算式で長さが取得されるように見えます。
`octet_lengthの結果 = Char型の長さ + [全角文字数] ×（[1文字のバイト数] - 1)`

※正確な情報は正直わかりませんが、自分が把握している範囲での共有です。なぜこのような仕様なのかはわかりませんが・・。

```:PostgreSQL　※※Char型の場合は【Char型の長さ + [全角文字数] ×（[1文字のバイト数] - 1)】※※
testdb=# select octet_length('あいABCDEF') AS octet_length;
 octet_length
--------------
           12
(1 行) 
testdb=# select octet_length('あいABCDEF'::char(20)) AS octet_length;
 octet_length
--------------
           24
(1 行)
testdb=# select octet_length('あいうABCDEF'::char(20)) AS octet_length;
 octet_length
--------------
           26
(1 行)
```

```:DB2（Oracle互換モード）　※※Char型の場合はデータ型の長さが返却される※※
[db2inst1@db2server /]$ db2 "select lengthb('あいABCDEF') AS lengthb from dual"
LENGTHB
-----------
         12
  1 record(s) selected.
[db2inst1@db2server /]$ db2 "select lengthb(CAST('あいABCDEF' AS CHAR(20))) AS lengthb from dual"
LENGTHB
-----------
         20
  1 record(s) selected.
[db2inst1@db2server /]$ db2 "select lengthb(CAST('あいうABCDEF' AS CHAR(20))) AS lengthb from dual"
LENGTHB
-----------
         20
  1 record(s) selected.
```


### ROWNUM
selectした結果に対して連番を付与したい場合、Oracleでは`ROWNUM`を利用しましたが、PostgreSQL`row_number() OVER ()`で代用する必要があります。
```:Oracle　※※ROWNUM利用例※※
SQL> select ROWNUM as 連番, deptno, deptname from user1.dept order by deptno;

      連番 DEPTNO     DEPTNAME
---------- ---------- ----------------------------------------
         1 0          営業部
         2 1          開発部
         3 2          人事部
```

```:postgresSQL　※※row_number() OVER ()で代用※※
testdb=# select row_number() OVER () as 連番, * from test_plpgsql.dept order by deptno;
 連番 | deptno | deptname
------+--------+----------
    1 | 0      | 営業部
    2 | 1      | 開発部
    3 | 2      | 人事部
(3 行)
```

ただし、`row_number() OVER () `は`WHERE`句で利用できないため、その場合は
`FETCH FIRST n ROWS ONLY`や`limit`で代用する必要があります。

```:Oracle　※※ROWNUM利用例※※
SQL> select deptno, deptname from user1.dept where ROWNUM <= 1 order by deptno;

DEPTNO     DEPTNAME
---------- ----------------------------------------
0          営業部
```

```:postgresSQL　※※FETCH FIRST n ROWS ONLYで代用※※
testdb=# select * from test_plpgsql.dept order by deptno FETCH FIRST 1 ROWS ONLY;
 deptno | deptname
--------+----------
 0      | 営業部
(1 行)
```

### RID
:::note warn
以下、取り上げる関数は**DB2（Oracle互換モード）⇔PostgreSQL**で発生する差分となります。
:::

`RID`は`ROWNUM`と似ていますが、`ROWNUM`は、選択された行に対して仮想的な行番号を付与する擬似列の一方で、`RID`は、行を一意的に識別するために使用します。

上記の通り、レコードの順序を変更しても、`RID`の値は変動しません。

```
[db2inst1@db2server /]$ db2 "select rid() as RID, ROWNUM as ROWNUM, deptno, deptname from test.dept order by deptno;"
RID                  ROWNUM               DEPTNO DEPTNAME
-------------------- -------------------- ------ ----------------------------------------
                   4                    1 0      営業部
                   5                    2 1      開発部
                   6                    3 2      人事部

  3 record(s) selected.

[db2inst1@db2server /]$ db2 "select rid() as RID, ROWNUM as ROWNUM, deptno, deptname from test.dept order by deptno desc;"
RID                  ROWNUM               DEPTNO DEPTNAME
-------------------- -------------------- ------ ----------------------------------------
                   6                    1 2      人事部
                   5                    2 1      開発部
                   4                    3 0      営業部

  3 record(s) selected.
```
一方で、`ROWNUM`に関してはレコードの順序を変更した場合、`ROWNUM`の値も変動します。

`RID`と同等の機能を持つ関数はPostgreSQLには存在しないため、
`RID`を利用せずに一意なレコードとして特定する場合はWHERE句の条件にプライマリーキーを指定して代用する必要があります。

### ファイル出力（`UTL_FILE`パッケージ）
Oracleでファイルの出力を行う場合は`UTL_FILE`パッケージを利用することで実現が可能ですが、PostgreSQLに同様のパッケージは存在しないため、自前で作成する必要があります。

COPYコマンドを利用すればファイルの出力自体は可能ですが、COPYコマンド単体ではファイルの追記が行えないため、追記が必要なログファイルなどを出力したい場合は一工夫が必要です。

超ざっくりですが、ファイル出力の処理を自前で実装するための処理の流れを紹介します。

#### 対応方法

1. ファイル出力用の一時表を作成し、ファイル出力前にCreate
2. `COPY ファイル出力用の一時表 FROM ...`で既に存在しているファイルの内容を1.でCreateした一時表に登録 
3. 2.で登録した一時表に対して、追加で出力したい内容をINSERT
4. `COPY ファイル出力用の一時表 TO ...`で、3．で登録した内容も含めてファイルを更新

## レコード型
Orecleのレコード型（`TYPE レコード型名 IS RECORD ...`）はPostgreSQLではそのまま利用できないため、**ユーザー定義型（`create type ユーザー定義型名 as ...`）で代用する**必要があります。

レコード型／ユーザー定義型の利用例については[こちら](#サンプルコード)を参考にしてください。

```sql:レコード型（Oracle）
CREATE OR REPLACE PACKAGE TestTypePackage IS
  TYPE recBankAccount IS RECORD ( 
      userid varchar(10)    , accnumber varchar(7)
      , acctype varchar(30) , name varchar(30)
      , balance varchar(20) , lastdate timestamp(6)
  ); 
END;
```

```sql:ユーザー定義型（PostgreSQL）
create type test_plpgsql.recBankAccount as ( 
      userid varchar(10)    , accnumber varchar(7)
      , acctype varchar(30) , name varchar(30)
      , balance varchar(20) , lastdate timestamp(6)
); 
```

ただし、Orecleのレコード型は初期値を設定することが可能ですが、**PostgreSQLのユーザー定義型は初期値の設定が不可**のため、初期値を設定したい場合は別の手段で代替する必要があります。

私が出会ったソースの場合、定数として利用するケースが殆どであったため、固定値を返却する関数を作成して代位要することにしました。

```sql:レコード型（Oracle）
  TYPE resultCode IS RECORD ( 
      success numeric := 0,
      error   numeric := 255
  );
```

```sql:固定値を返却する関数を作成して代用（PostgreSQL）
CREATE OR REPLACE FUNCTION test_plpgsql.getResultCode(VARCHAR) RETURNS NUMERIC AS $$ 
DECLARE
    i_result NUMERIC := NULL;
BEGIN
    CASE $1
        WHEN 'success' THEN
            i_result := 0;
        WHEN 'error' THEN
            i_result := 255;
    END CASE;
    
    RETURN i_result;
END;
$$ LANGUAGE plpgsql;
```

## 引数にOUTパラメーターが存在し、かつreturnで戻り値を返却する関数
タイトルの通り、PL/SQLでは**引数にOUTパラメーターが存在し、かつreturnで戻り値を返却する関数**を実装することができますが、PL/pgSQLではそのような関数は実装できません。

この場合、以下の対応を行います。
* PL/pgSQLでは、return文を使用せずに、**OUTパラメーター追加し、元の戻り値としてそれを代用**
* 戻り値のデータ型を、**元のデータ型から`RECORD`型に修正**（戻り値として返却する項目が増えるため、`RECORD`型として扱う必要がある）

この代替手法は、呼び出し元のコードにも修正が必要となるため、複数のメンバーが同時に作業する場合には注意が必要です。

当時の現行システムはこのパターンの関数が多数存在していたため、このケースの関数を呼び出している処理が存在している場合、呼び出し先の関数の修正担当者が追加したOUTパラメーターや戻り値の変数名をどのように命名しているのか、適宜連携しながら製造を進める必要がありました。そのような背景もあり、製造時にメンバー同士の連携不足で不具合が混入しやすい内容でした。

※この修正に関しては、該当する関数ごとに新旧の引数や戻り値についての対応表を作成し、製造担当者に記入してもらうようにし、メンバー間での連携がスムーズに行えるようにしました。

### 修正イメージ
```sql:Oracle
/* *******************************************
 * 引数にOUTパラメーターが存在し、かつreturnで戻り値を返却する関数
 * *******************************************/
CREATE OR REPLACE FUNCTION 
    ファンクション名(引数１ IN OUT データ型) RETURN データ型
IS
    変数１ データ型 := 値１;
BEGIN
    引数 := 値２;
    RETURN 変数１;
END;
/* *******************************************
 * 呼び出し元プロシージャ
 * *******************************************/
CREATE OR REPLACE PROCEDURE プロシージャ名
IS
    変数２（ファンクションの引数）   データ型;
    変数３（ファンクションの戻り値） データ型;
BEGIN 
    変数３（ファンクションの戻り値） := ファンクション名(変数２（ファンクションの引数）); 
END;  

```

```sql:PostgreSQL
/* *******************************************
 * 引数にOUTパラメーターが存在し、かつreturnで戻り値を返却する関数
 * *******************************************/
CREATE OR REPLACE FUNCTION 
    ファンクション名(引数１ IN OUT データ型, 引数２ OUT データ型) RETURNS RECORD AS $$ 
DECLARE	
    変数１ データ型 := 値１;
BEGIN
    引数 := 値２;
    RETURN;
END;
$$ LANGUAGE plpgsql;
/* *******************************************
 * 呼び出し元プロシージャ
 * *******************************************/
CREATE OR REPLACE PROCEDURE プロシージャ名() AS $$ 
DECLARE	
    変数２（ファンクションの引数）   データ型;
    変数３（ファンクションの戻り値） データ型;
    変数４                           RECORD;
BEGIN 
    変数４ := ファンクション名(変数２（ファンクションの引数）); 
    変数２（ファンクションの引数）   := 変数４.引数１;
    変数３（ファンクションの戻り値） := 変数４.引数２;
END;
$$ LANGUAGE plpgsql;

```

### サンプルコード 
```sql:サンプルコード（Oracle）
/* *******************************************
 * 銀行口座TBLにレコードを追加するpackage
 * *******************************************/
CREATE OR REPLACE PACKAGE TestTypePackage IS
  TYPE recBankAccount IS RECORD( 
      userid varchar(10)    , accnumber varchar(7)
      , acctype varchar(30) , name varchar(30)
      , balance varchar(20) , lastdate timestamp(6)
  ); 
  -- ** 引数にOUTパラメーターが存在し、かつreturnで戻り値を返却する関数 **
  FUNCTION funcRegisterBankAccount(p_rec1 IN OUT recBankAccount) RETURN INTEGER;
    -- **** 動作検証用プロシージャ ****
  PROCEDURE procTestTypePackage;
END;
/
/* *******************************************
 * 銀行口座TBLにレコードを追加するpackagebody
 * *******************************************/
CREATE OR REPLACE PACKAGE BODY TestTypePackage IS
    -- **** 引数にOUTパラメーターが存在し、かつreturnで戻り値を返却する関数 ****
    FUNCTION funcRegisterBankAccount(p_rec1 IN OUT recBankAccount) RETURN INTEGER
    IS
    BEGIN
        IF p_rec1.userid IS NULL OR p_rec1.accnumber IS NULL OR p_rec1.acctype IS NULL THEN
            RETURN 255; -- プライマリーキーの値がNULLの場合は即時return
        END IF;
        -- **** IN OUTパラメータの値がNULLの場合は値を更新して、呼び出し元に返却する ****
        p_rec1.name := NVL(p_rec1.name, '名無し名義'); 
        p_rec1.balance := NVL(p_rec1.balance, 0); 
        p_rec1.lastdate := NVL(p_rec1.lastdate, CURRENT_TIMESTAMP); 
        -- 銀行口座TBLにレコードを登録
        INSERT INTO USER1.BANKACCOUNT 
            (USERID, ACCNUMBER, ACCTYPE, NAME, BALANCE, LASTDATE)
        VALUES 
            (p_rec1.userid, p_rec1.accnumber, p_rec1.acctype, p_rec1.name, p_rec1.balance , p_rec1.lastdate);
        COMMIT;
        -- **** 正常終了の場合、戻り値に0を返却する ****
        RETURN 0;
    EXCEPTION
        WHEN OTHERS THEN
            RETURN 255;
    END;
    
    -- **** 動作検証用プロシージャ ****
    PROCEDURE procTestTypePackage IS
        i_result INTEGER; 
        rec1 recBankAccount; 
    BEGIN 
        rec1.userid := 'testuser01'; rec1.accnumber := '1234567'; rec1.acctype := '貯蓄'; 
        rec1.name := null; rec1.balance := null; rec1.lastdate := null; 
        -- **** 引数にOUTパラメーターが存在し、かつreturnで戻り値を返却する関数の実行 ****
        i_result := funcRegisterBankAccount(rec1); 
        -- **** 戻り値及びIN OUTパラメータの更新結果の確認 ****
        DBMS_OUTPUT.PUT_LINE('[処理結果]：' || i_result); 
        DBMS_OUTPUT.PUT_LINE('[userid]：' || rec1.userid); 
        DBMS_OUTPUT.PUT_LINE('[accnumber]：' || rec1.accnumber); 
        DBMS_OUTPUT.PUT_LINE('[acctype]：' || rec1.acctype); 
        DBMS_OUTPUT.PUT_LINE('[name]：' || rec1.name); 
        DBMS_OUTPUT.PUT_LINE('[balance]：' || rec1.balance); 
        DBMS_OUTPUT.PUT_LINE('[lastdate]：' || rec1.lastdate); 
    END;  
END TestTypePackage;
/
```

```:実行結果（Oracle）
SQL> CALL TestTypePackage.procTestTypePackage();
[処理結果]：0
[userid]：testuser01
[accnumber]：1234567
[acctype]：貯蓄
[name]：名無し名義
[balance]：0
[lastdate]：24-05-26 18:08:59.582000
コールが完了しました。

SQL> select userid, to_number(accnumber), acctype, name, to_number(balance), lastdate from USER1.BANKACCOUNT;
USERID          TO_NUMBER(ACCNUMBER) ACCTYPE         NAME                 TO_NUMBER(BALANCE) LASTDATE
--------------- -------------------- --------------- -------------------- ------------------ ----------------------------------------
testuser01                   1234567 貯蓄            名無し名義                            0 24-05-26 18:08:59.582000
```

```sql:サンプルコード（PostgreSQL）
/* *******************************************
 * ユーザー定義型
 * *******************************************/
create type test_plpgsql.recBankAccount as ( 
      userid varchar(10)    , accnumber varchar(7)
      , acctype varchar(30) , name varchar(30)
      , balance varchar(20) , lastdate timestamp(6)
); 
/* *******************************************
 * 引数にOUTパラメーターが存在し、かつreturnで戻り値を返却する関数
 * *******************************************/
CREATE OR REPLACE FUNCTION test_plpgsql.funcRegisterBankAccount
    (p_rec1 IN OUT test_plpgsql.recBankAccount, p_result OUT INTEGER) RETURNS RECORD AS $$ 
DECLARE	
BEGIN
    IF p_rec1.userid IS NULL OR p_rec1.accnumber IS NULL OR p_rec1.acctype IS NULL THEN
        -- プライマリーキーの値がNULLの場合は即時return
        p_result := 255;
        RETURN;
    END IF;
    -- **** IN OUTパラメータの値がNULLの場合は値を更新して、呼び出し元に返却する ****
    p_rec1.name := coalesce(nullif(p_rec1.name, ''), '名無し名義'); 
    p_rec1.balance := coalesce(nullif(p_rec1.balance, ''), '0'); 
    p_rec1.lastdate := coalesce(p_rec1.lastdate, CURRENT_TIMESTAMP); 
    -- 銀行口座TBLにレコードを登録
    INSERT INTO test_plpgsql.BANKACCOUNT 
        (USERID, ACCNUMBER, ACCTYPE, NAME, BALANCE, LASTDATE)
    VALUES 
        (p_rec1.userid, p_rec1.accnumber::numeric, p_rec1.acctype, p_rec1.name, p_rec1.balance::numeric , p_rec1.lastdate);
    -- **** 正常終了の場合、戻り値に0を返却する ****
    p_result := 0;
EXCEPTION
    WHEN OTHERS THEN
        p_result := 255;
END;
$$ LANGUAGE plpgsql;
/* *******************************************
 * 動作検証用プロシージャ
 * *******************************************/
CREATE OR REPLACE PROCEDURE test_plpgsql.procTestTypePackage() AS $$ 
DECLARE	
    i_result INTEGER; 
    rec1 test_plpgsql.recBankAccount; 
    rec2 RECORD;
BEGIN 
    rec1.userid := 'testuser01'; rec1.accnumber := '1234567'; rec1.acctype := '貯蓄'; 
    rec1.name := null; rec1.balance := null; rec1.lastdate := null; 
    -- **** 引数にOUTパラメーターが存在し、かつreturnで戻り値を返却する関数の実行 ****
    rec2 := funcRegisterBankAccount(rec1); 
    rec1 := rec2.p_rec1;
    i_result := rec2.p_result;
    -- **** 戻り値及びIN OUTパラメータの更新結果の確認 ****
    RAISE INFO '[処理結果]：%'  , i_result; 
    RAISE INFO '[userid]：%'    , rec1.userid; 
    RAISE INFO '[accnumber]：%' , rec1.accnumber; 
    RAISE INFO '[acctype]：%'   , rec1.acctype; 
    RAISE INFO '[name]：%'      , rec1.name; 
    RAISE INFO '[balance]：%'   , rec1.balance; 
    RAISE INFO '[lastdate]：%'  , rec1.lastdate; 
END;  
$$ LANGUAGE plpgsql;
```

```:実行結果（PostgreSQL）
testdb=# call test_plpgsql.procTestTypePackage();
INFO:  [処理結果]：0
INFO:  [userid]：testuser01
INFO:  [accnumber]：1234567
INFO:  [acctype]：貯蓄
INFO:  [name]：名無し名義
INFO:  [balance]：0
INFO:  [lastdate]：2024-05-26 20:24:44.088765
CALL
testdb=# select * from test_plpgsql.BANKACCOUNT;
   userid   | accnumber | acctype |    name    | balance |          lastdate
------------+-----------+---------+------------+---------+----------------------------
 testuser01 |   1234567 | 貯蓄    | 名無し名義 |       0 | 2024-05-26 20:24:44.088765
(1 行)
```

## 引数の上限数
PostgreSQLの関数の引数の上限は**デフォルトで100個**と決まっています。
詳細は分かりませんが、どうも[pg_config_manual.h](https://github.com/postgres/postgres/blob/master/src/include/pg_config_manual.h)に引数の上限が定義されているようです。
```cpp:pg_config_manual.h（一部抜粋）
/*
 * Maximum number of arguments to a function.
 *
 * The minimum value is 8 (GIN indexes use 8-argument support functions).
 * The maximum possible value is around 600 (limited by index tuple size in
 * pg_proc's index; BLCKSZ larger than 8K would allow more).  Values larger
 * than needed will waste memory and processing time, but do not directly
 * cost disk space.
 *
 * Changing this does not require an initdb, but it does require a full
 * backend recompile (including any user-defined C functions).
 */
#define FUNC_MAX_ARGS		100
```
Orecleは100個を超えるの引数を持つ関数、プロシージャを作成することが可能なため、このケースの場合**そのままPostgreSQLにマイグレーションすることはできません。**

対応例としては以下が挙げられますが、2、3に関しては呼び出し元にもそれなりに修正が入ります・・。
1. 不要な引数を削除して100個以下に収める
2. プロシージャを分割して引数を100個以下に収める
3. 引数をJson型にして受け渡す
4. `FUNC_MAX_ARGS`で定義されている値を修正して、コンパイル

なお、上記仕様のため、**`CONCAT関数`での最大連結文字数は100個が上限**となります。
現行ソースで文字列結合演算子（`||`）を用いてSQLを動的に生成する処理を`CONCAT関数`に置換したら引数の上限数を超えてエラーになりました・・。（どれだけ複雑なクエリなのか・・）

## `SELECT ... INTO 変数` の挙動
`SELECT ... INTO 変数`はSELECT文の実行結果を変数の値に設定する構文です。
Oracle、PostgreSQLどちらも構文を修正せずに利用可能ですが、

* selectの結果が0件の場合
* selectの結果が2件以上の場合

の上記２パターンにおいてOracleでは例外（※）が発生しますが、PostgreSQLは例外とならずに処理が継続します。

※SELECTの実行結果が0件の場合は`NO_DATA_FOUND`、2件以上の場合は`TOO_MANY_ROWS`というエラーが発生します。

### 対応方法
PostgreSQLにおいては`STRICT`を追加することで、Oracleと同様の動作をすることが可能となります。

```sql:修正前
SELECT ... INTO 変数
```

```sql:修正後
SELECT ... INTO STRICT 変数
```

それぞれのサンプルコードは[こちら](https://qiita.com/ebichan_88/items/1effb09fbc458276ea51#select-into-strict)を参照。

[NULLと空文字の違い](#空文字とNULLの扱い)の件と同様に、一括置換が難しい、かつ修正が漏れていたとしても構文エラーとならず動作してしまうため、後工程まで見落とされる可能性があります。（実際、この事象も愚直に横展開をすることになりました・・。）

製造工程で漏れなく修正しきるのは大変ですが、修正漏れを後工程へ持ち込まないために、単体テストの段階でテストデータが0件、1件、2件以上の3パターンを実施するなど、テスト観点に取り入れるのが大事だと思います。

## カーソルの挙動
構文に関する修正は、初めに紹介した[移行事例に関するサイト](#PostgreSQLの移行事例)を参考にすればそこまで難しくありませんが、同じ構文でも動作が異なる部分に関して、今回は紹介していきたいと思います。

### FETCH（読み込む行が存在しない場合）

以下の構文はOracle・PostgreSQLでも動作しますが、実は**読み込む行が存在しない場合（ループ処理でFETCHする際、最終ループに当たるタイミングなど）の動作が異なります。**

* Oracleでの動作：変数の値が更新されない
* PostgreSQLでの動作 ：変数の値がNULLに更新される

```sql:
FETCH カーソル名 INTO 変数名;
```

#### サンプルコード

```sql:サンプルコード（Oracle）　※※変数の値が更新されない※※
CREATE OR REPLACE PROCEDURE USER1.procTestCurs IS  
    CURSOR curs1 IS SELECT deptno, deptname FROM USER1.dept ORDER BY deptno;
    v_deptno   CHAR(5)     := NULL;
    v_deptname VARCHAR(40) := NULL;
BEGIN    
    OPEN curs1;
    LOOP 
        -- ★★★最後のレコードをFETCHした後の動作が異なる★★★
        -- Oracleの場合    ：変数の値が更新されない
        -- PostgreSQLの場合：変数の値がNULLに更新される
        FETCH curs1 INTO v_deptno, v_deptname;
        IF curs1%NOTFOUND THEN
            EXIT;
        END IF;
        DBMS_OUTPUT.PUT_LINE('[ループの内側]deptno:' || v_deptno || 'deptname:' || v_deptname);
    END LOOP;
    
    CLOSE curs1;
    
    DBMS_OUTPUT.PUT_LINE('[ループの外側]deptno:' || v_deptno || 'deptname:' || v_deptname);

END;    
/
```

```:実行結果（Oracle）　※※変数の値が更新されない※※
SQL> CALL procTestCurs();
[ループの内側]deptno:0    deptname:営業部
[ループの内側]deptno:1    deptname:開発部
[ループの内側]deptno:2    deptname:人事部
[ループの外側]deptno:2    deptname:人事部

コールが完了しました。
```

```sql:サンプルコード（PostgreSQL） ※※変数の値がNULLに更新される※※
CREATE OR REPLACE PROCEDURE test_plpgsql.procTestCurs() AS $$
DECLARE    
    curs1      refcursor;
    v_deptno   CHAR(5)     := NULL;
    v_deptname VARCHAR(40) := NULL;
BEGIN    
    OPEN curs1 FOR SELECT deptno, deptname FROM test_plpgsql.dept ORDER BY deptno;

    LOOP 
        -- ★★★最後のレコードをFETCHした後の動作が異なる★★★
        -- Oracleの場合    ：変数の値が更新されない
        -- PostgreSQLの場合：変数の値がNULLに更新される
        FETCH curs1 INTO v_deptno, v_deptname;
        IF NOT FOUND THEN
            EXIT;
        END IF;
        RAISE INFO '[ループの内側]deptno:% deptname:%', v_deptno, v_deptname;
    END LOOP;
    
    CLOSE curs1;
    
    RAISE INFO '[ループの外側]deptno:% deptname:%', v_deptno, v_deptname;

END;    
$$ LANGUAGE plpgsql;
```

```:実行結果（PostgreSQL）　※※変数の値がNULLに更新される※※
testdb=# CALL test_plpgsql.procTestCurs();
INFO:  [ループの内側]deptno:0     deptname:営業部
INFO:  [ループの内側]deptno:1     deptname:開発部
INFO:  [ループの内側]deptno:2     deptname:人事部
INFO:  [ループの外側]deptno:<NULL> deptname:<NULL>
CALL
```

カーソルを利用する際、FETCHで受け取った値をループの外側で利用するケースはあまりないと思いますが、そのような実装がある場合は修正が必要となります。

```diff_sql:サンプルコード_修正後（PostgreSQL）
CREATE OR REPLACE PROCEDURE test_plpgsql.procTestCurs() AS $$
DECLARE    
    curs1      refcursor;
    v_deptno   CHAR(5)     := NULL;
    v_deptname VARCHAR(40) := NULL;
+   v_deptno_tmp   CHAR(5)     := NULL;
+   v_deptname_tmp VARCHAR(40) := NULL;
BEGIN    
    OPEN curs1 FOR SELECT deptno, deptname FROM test_plpgsql.dept ORDER BY deptno;

    LOOP 
        -- ★★★最後のレコードをFETCHした後の動作が異なる★★★
        -- Oracleの場合    ：変数の値が更新されない
        -- PostgreSQLの場合：変数の値がNULLに更新される
-       FETCH curs1 INTO v_deptno, v_deptname;
+       FETCH curs1 INTO v_deptno_tmp, v_deptname_tmp;
        IF NOT FOUND THEN
            EXIT;
        END IF;

+       v_deptno := v_deptno_tmp;
+       v_deptname := v_deptname_tmp;
        RAISE INFO '[ループの内側]deptno:% deptname:%', v_deptno, v_deptname;
    END LOOP;


    CLOSE curs1;
    
    RAISE INFO '[ループの外側]deptno:% deptname:%', v_deptno, v_deptname;

END;    
$$ LANGUAGE plpgsql;
```

## トランザクション制御
トランザクション制御に関しては、PL/SQLとPL/pgSQLで仕様に大きな違いがあり、単純な置換が困難です。

**~~特にSAVEPOINT。お前だぁ！！！！~~**

現行ソースをどのように修正するか一番悩んだところになりますが、当時試行錯誤した結果をまとめた記事があるので、そちらをご覧ください。

https://qiita.com/ebichan_88/items/880a77c07e0cace071e4

## 自立型トランザクション
Oracleの自立型トランザクション（`PRAGMA AUTONOMOUS_TRANSACTION`）はPostgreSQLでは対応していないため、`DBLINK`に置き換えた実装は修正する必要があります。

自立型トランザクションの利用目的の一例として、**処理の途中でログ出力用のTBLにエラー内容を登録する。** というケースが挙げられます。

自立型トランザクションを利用しない場合、処理の途中でエラーが発生しRollbackを行うと、ログ出力用のTBLに登録した内容もRollbackされてしまいます。

一方、ログ出力用のTBLにエラー内容を登録する際に自立型トランザクションを利用することで、ログの内容はRollbackされずレコードに残り続けることができます。

```sql:ソース（Oracle）
/* *******************************************
 * 自立型トランザクション検証用ソース
 * *******************************************/
DECLARE
    ERROR_CODE NUMBER(5) := -20000;
BEGIN
    -- 自立型トランザクションを用いてログメッセージを出力
    writeDebugLog ('no_proc', '！！BANKACCOUNTテーブル登録開始！！');
    
    -- 銀行口座TBLにレコードを登録
    INSERT INTO BANKACCOUNT 
        (USERID, ACCNUMBER, ACCTYPE, NAME, BALANCE, LASTDATE)
    VALUES 
        ('testcase01', 1, '普通', 'テストケース０１', 1000000, CURRENT_TIMESTAMP);
    
    -- 強制的に例外発生
    RAISE_APPLICATION_ERROR(ERROR_CODE, 'MY EXCEPTION!!');
    
    -- 自立型トランザクションを用いてログメッセージを出力    
    writeDebugLog ('no_proc', '！！BANKACCOUNTテーブル登録終了！！');
EXCEPTION
    WHEN OTHERS THEN
        -- 自立型トランザクションを用いてログメッセージを出力    
        writeDebugLog ('no_proc', '！！BANKACCOUNTテーブル登録時に例外発生！！' || ' SQLCODE=' || SQLCODE || ', SQLERRM=' || SQLERRM);
        ROLLBACK;
END;
/
/* *******************************************
 * 自立型トランザクションを用いてINSERT処理を行うプロシージャ
 * *******************************************/
CREATE OR REPLACE PROCEDURE writeDebugLog (p_procName IN varchar, p_logMsg IN varchar) IS
    PRAGMA AUTONOMOUS_TRANSACTION; --自律型トランザクションの指定
BEGIN

    INSERT INTO  DebugLog (procName, logMsg, lastDate) values (p_procName, p_logMsg, current_timestamp);
    COMMIT; -- 自律型トランザクションではトランザクションを終了させないとエラー
 
EXCEPTION
    WHEN OTHERS THEN
        ROLLBACK;
        RAISE;
END writeDebugLog;
/
```

```:実行結果（Oracle）
SQL> select * from DebugLog;
PROCNAME        LOGMSG                                                                                               LASTDATE
--------------- ---------------------------------------------------------------------------------------------------- ----------------------------------------
no_proc         ！！BANKACCOUNTテーブル登録時に例外発生！！ SQLCODE=-20000, SQLERRM=ORA-20000: MY EXCEPTION!!        24-05-25 22:24:49.403000
no_proc         ！！BANKACCOUNTテーブル登録開始！！                                                                  24-05-25 22:24:49.403000

SQL> select * from BANKACCOUNT;
レコードが選択されませんでした。
```

自立型トランザクションを利用したソースを`DBLINK`に置き換えた場合のソースは以下となります。
```sql:ソース（PostgreSQL）
/* *******************************************
 * 自立型トランザクション検証用ソース
 * *******************************************/
DO $$
DECLARE
BEGIN
    -- 自立型トランザクションを用いてログメッセージを出力
    CALL writeDebugLog ('no_proc', '！！BANKACCOUNTテーブル登録開始！！');
    
    -- 銀行口座TBLにレコードを登録
    INSERT INTO BANKACCOUNT 
        (USERID, ACCNUMBER, ACCTYPE, NAME, BALANCE, LASTDATE)
    VALUES 
        ('testcase01', 1, '普通', 'テストケース０１', 1000000, CURRENT_TIMESTAMP);
    
    -- 強制的に例外発生
    RAISE EXCEPTION SQLSTATE 'UD000' USING MESSAGE='MY EXCEPTION!!';
    
    -- 自立型トランザクションを用いてログメッセージを出力    
    CALL writeDebugLog ('no_proc', '！！BANKACCOUNTテーブル登録終了！！');
EXCEPTION
    WHEN OTHERS THEN
        -- 自立型トランザクションを用いてログメッセージを出力    
        CALL writeDebugLog ('no_proc', '！！BANKACCOUNTテーブル登録時に例外発生！！' || ' SQLSTATE=' || SQLSTATE || ', SQLERRM=' || SQLERRM);
END;
$$ LANGUAGE plpgsql;

/* *******************************************
 * 自立型トランザクションを用いてINSERT処理を行うプロシージャ
 * *******************************************/
CREATE OR REPLACE PROCEDURE writeDebugLog (p_procName IN varchar, p_logMsg IN varchar) AS $$
DECLARE
BEGIN

	PERFORM DBLINK_CONNECT('dblink_test', 'host=ホスト名 port=ポート番号 dbname=DB名 user=ユーザー名 password=パスワード');
    PERFORM DBLINK_EXEC('dblink_test',
        FORMAT('INSERT INTO  test_plpgsql.DebugLog (procName, logMsg, lastDate) values (%L, %L, current_timestamp);', p_procName, p_logMsg));
    PERFORM DBLINK_DISCONNECT('dblink_test');
 
EXCEPTION
    WHEN OTHERS THEN
        PERFORM DBLINK_DISCONNECT('dblink_test');
        RAISE EXCEPTION SQLSTATE 'UD000' USING MESSAGE=' SQLSTATE=' || SQLSTATE || ', SQLERRM=' || SQLERRM;
END;
$$ LANGUAGE plpgsql;
```

```:実行結果（PostgreSQL）
testdb=# select * from DebugLog;
 procname |                                       logmsg                                       |          lastdate
----------+------------------------------------------------------------------------------------+----------------------------
 no_proc  | ！！BANKACCOUNTテーブル登録開始！！                                                | 2024-05-25 23:12:50.857013
 no_proc  | ！！BANKACCOUNTテーブル登録時に例外発生！！ SQLSTATE=UD000, SQLERRM=MY EXCEPTION!! | 2024-05-25 23:12:50.900875
(2 行)

testdb=# select * from BANKACCOUNT;
 userid | accnumber | acctype | name | balance | lastdate
--------+-----------+---------+------+---------+----------
(0 行)
```

実装例は上記となりますが、`DBLINK`利用時の注意点をいくつかピックアップします。

### DBLINK利用時に定義する接続先の情報について
DBLINKを利用する際、`DBLINK_CONNECT`の第二引数に接続先の情報を設定する必要がありますが、動作環境により接続先の情報は異なるため、引数に接続情報をべた書きするのは望ましくありません。

```:例
  PERFORM DBLINK_CONNECT('dblink_test', 'host=ホスト名 port=ポート番号 dbname=DB名 user=ユーザー名 password=パスワード');
```

このようなケースにおいては、[CREATE SERVER](https://www.postgresql.jp/docs/12/sql-createserver.html)の仕組みを利用することで、引数に接続情報をべた書きする必要がなくなるので、DBLINKを利用する際は参考にしてください。

### DBLINKでプロシージャを実行する方法について
DBLINKの利用方法は[PostgreSQLのリファレンス](https://www.postgresql.jp/document/12/html/contrib-dblink-function.html)を参考にすればよいのですが、プロシージャの呼び出し方（特に引数に`INOUTパラメータ`を保有しているようなプロシージャ）に関してはリファレンスを見てもどのように実装すればよいか理解が難しかったです。

DBLINKでプロシージャを実行する方法に関する記事を本記事とは別で作成しているので、実装方法が気になる方はご覧ください。

https://qiita.com/ebichan_88/items/465a96bf2e880f67e045

### 性能面の問題について
DBLINKを利用した処理は新規セッションでDBに接続し、負荷がかかるため、性能的にかなり問題が発生しました。
**性能試験でDBLINKを利用している業務で軒並み性能遅延が発生し、DBLINKの利用を可能な限り減らす**こととなったため、安易にDBLINKに置き換えず、必要性について検討してからマイグレーションを行ったほうが良いです。

## 外部結合演算子
外部結合演算子はWHERE句の条件式において、表結合として付帯したい表の列に外部結合演算子である(`+`)を付加することでOUTER JOINと同等の外部結合が可能となる演算子のことです。

```sql:Oracle
select
    t3.deptname AS 部署, t4.posname AS 役職, t2.empname AS 氏名, t2.age AS 年齢 
from
    member t1, emp t2, dept t3, pos t4 
where
    t1.empno = t2.empno(+) 
    and t1.deptno = t3.deptno(+) 
    and t2.poscode = t4.poscode(+) 
    and to_number(t1.empno, '00000') between 0 and 5 
order by
    t3.deptno, t4.poscode, t2.age
```

PostgreSQLでは外部結合演算子が利用できないため、適宜OUTER JOINに修正する必要があります。
単純な置換ができず、結合対象のテーブル、カラムが多くなればなるほど修正は手間となります。
```sql:PostgreSQL
select
    t3.deptname AS 部署, t4.posname AS 役職, t2.empname AS 氏名, t2.age AS 年齢  
from
    member t1 
    left outer join emp t2 
        on t1.empno = t2.empno 
    left outer join dept t3 
        on t1.deptno = t3.deptno 
    left outer join pos t4 
        on t2.poscode = t4.poscode 
where
    to_number(t1.empno, '00000') between 0 and 5 
order by
    t3.deptno, t4.poscode, t2.age
```


# PL/pgSQLの製造・単体テストについて
SQLMAPに関しては、SQLファイルの修正後、画面の単体テストでまとめて試験を行っていましたが、PL/pgSQLに関しては、製造実施後、モジュール毎に単体テストを実施していました。

当時のPL/pgSQLの製造・単体テストの進め方について、紹介してきたいと思います。
最善の進め方かであるかはわかりませんが、少しでも参考になれば幸いです。
## 期間・体制

* 期間
    * 製造、単体テストで合わせて8か月程度
* 体制
    * 製造当初は3名。ピーク時は7名程度
    * 自分はいちメンバーで、WBSや進捗報告はPLが実施していましたが、製造・単体テストの進め方やメンバーの作業のとりまとめなどは自身で行うことができ、ある程度裁量を与えられていました

## 製造について
PL/pgSQLの製造にあたり、既に用意されているDB2（Oracle互換モード）からPostgreSQLのマイグレーション方針の内部資料（見積もり時にpostgreSQLの移行を検証した際の資料）を参照し、PL/SQL→PL/pgSQLの修正を実施していましたが、

* 複数人で並行して製造を進めている
* マイグレーションにあたり、インプットすべき知識が膨大、かつ内部資料に記載されていない修正パターンが多数存在

ということもあり、製造が順調に進まない、メンバーによって修正内容のばらつきが発生する等の懸念がありました。
そのため、製造メンバー全員が足並みそろえて製造するために、以下の点に気を付けて開発を進めていきました。

### 気を付けた点

1. **修正方針資料の更新**
    * 内部資料に存在しない修正方針があれば、内部資料とは別に修正方針をまとめた資料に、修正前（PL/SQL）のソースと修正後（PL/pgSQL）の実装例を記載
2. **懸案管理簿の更新**
    * 修正の仕方がわからない、修正に関して懸案があれば懸案管理簿に記載
3. **開発メンバー同士の朝会実施**
    * 製造メンバーの朝会で、前日に追記した修正方針及び懸案管理簿を確認し、メンバーが記載した修正方針について問題がないか確認し、製造メンバー全員で認識合わせを実施
        * 既に製造完了したソースに追加改修が必要か確認し、必要であれば横展開を行い修正を実施
    * 懸案管理簿の内容をメンバー全員で確認し、その場で解決できなければ、解決するためのプラン検討及び担当者の割り当てを実施（技術的な課題であり、解決したら修正方針に追記する）
4. **チェックリストを用いたセルフレビュー**
    * 修正方針資料をインプットとして、モジュールごとにチェックリストを作成し、レビュー依頼前にセルフレビューを実施


## 単体テストについて
PL/pgSQLの単体テストは以下の通りに実行していました。

### 実行ツールについて
単体テストを実行するにあたり、以下の仕組みを構築しました。
（PJTのアーキテクトポジションのメンバーに実装いただきました。  勉強になります・・。）

* Nunitを用いてテストコードを作成し、テストコード内でPL/SQLとPL/pgSQLを実行
* テスト項目はドキュメントコメントに記載
* 基本的にモジュール毎に試験を行うため、テストコード実行時、下位モジュールにスタブを適用するための仕組みを構築
* テストコード実行時、テストデータの登録、削除を行うための仕組みを構築


### テストの確認内容について
現行システムのPL/SQLと新規製造したPL/pgSQLをそれぞれ実行し、TBLの更新結果、戻り値、OUTパラメーター等の現新比較を実施

### テストパターンの洗い出しについて

モジュール毎、処理内に存在する以下１～４の項目を洗い出し、Excelで表にまとめ（※）、２～４の処理をすべて網羅するようにテストケースを作成
1. 条件分岐
2. TBLに対してCRUD処理を行っている箇所
3. プロシージャ、ファンクションを呼び出している箇所
4. 例外処理

※NunitはあくまでもPL/SQLとPL/pgSQLの呼び出しに利用するものであり、テストのカバレッジを確認することができなかったため、この資料でテストが網羅できているか確認を行っていました。

## よかった点
* 修正方針資料に修正内容を追記することで、マイグレーションに関する知見をチーム内に蓄積することができた
* 後工程で横展開が必要になった修正漏れもあったが、概ね、担当者毎に修正内容がばらつくことなく製造を行うことができた
## 反省点

### 製造時のチェックリストの形式化
修正方針資料を参考にしてチェックリストを作成しましたが、確認項目が非常に多く、チェックリストの確認に時間がかかりすぎることや形式的になってしまっている部分もあったと思われます。

マイグレーションに関する知識が不足している場合、製造開始時にはチェックリストが必要かもしれませんが、製造に慣れてきたら、チェックリストの確認・消し込みに割く時間を製造担当者の相互レビューの時間に充てたほうが良かったかもしれないと今では思います。（当時はPLがほぼすべてのソースに対してレビューを実施）

### データパターン観点の試験の不足
今回試験するPL/pgSQLの資源は、画面の試験でも利用するため、モジュール単位で行う今回の単体試験は疎通の確認と最低限の現新比較がメインでしたが、NULL・空文字のデータやSELECTの取得件数（0件、1件、2件以上）など、データパターンを明示的に観点として含めていませんでした。

そのため、[空文字とNULLの扱い](#空文字とNULLの扱い)による動作差異や`SELECT ... INTO 変数` の動作の差異など、実際の動作は異なるがエラーにはならない構文や仕様については単体テストで検知することができず、後工程で横展開が必要になったのは反省でした。

# 余談
今回はあくまでもDB2→PostgreSQLの移行でしたが、Oracleの要素も詰め込まれていたので、PostgreSQLの拡張として[orafce](https://qiita.com/plusultra/items/18f09751e5e787c3af68)を採用したらどうだったのかなぁと思ったりします。

一括置換では対応できない、`SUBSTRB`などの互換性のない関数や`UTL_FILE`などのパッケージをそのまま利用できるのは大きいと思います。

# 終わりに
DB2（Oracle互換モード）からPostgreSQLのマイグレーションに関して、あまり公開されておらず、かつ本記事でも書ききれていないニッチなネタが数多く残されています・・。
（現場離れてかなり思い出せない内容も増えてきて、記憶の新しいうちにこの記事を書いておけばと少し後悔しています。PostgreSQLのマイグレーションは本当に、本当に大変だったので。）

いつの日か、これまで紹介した記事や本記事を参考にして、より良いPostgreSQLのマイグレーションの資料が公開されることを願います。

## おまけ：（気が向いたら）加筆するかもしれないリスト
* Exception句追加による性能面の影響（ロングトランザクションの発生）
    * [参考になりそうなサイト](https://www.fujitsu.com/jp/about/faq/sfw-enterprisepostgres/20540.html)
* 引数のデフォルト句の挙動の違い
* 精度指定の無いデータ型の桁数の違い
* 配列の構文の違い
* ユーザー定義例外のマイグレーション方針について
* ユーザー定義関数の[変動性分類](https://www.postgresql.jp/docs/12/xfunc-volatility.html)について
* 大文字小文字問題