FUNCTIONのリターンの桁精度が取得できない件と対策方法【PostgreSQL、JDBC】

## 概要
JDBC（Java）から検索結果のメタ情報を取得する場合、ResultSetMetaDataを使用します。
検索結果のカラムの桁精度は、ResultSetMetaData#getScale()で取得することができます（[サンプルコード](#補足jdbcで型精度情報を取得するサンプル)）。

しかし、以下の両方の条件に当てはまる場合に取得できる桁精度の値が、データ型によって固定された値になってしまいます（例：timestamp型なら6、numeric型なら0 など）。
- データベースがPostgreSQLである。
- SELECT句で取得する値がFUNCTIONの戻り値である。

この記事では、FUNCTIONの戻り値の桁精度をJDBCで取得するための方法を紹介します。

## 経緯

弊社製品[^1]は、データベースに長らくOracleを使用しています。
また、2020年にリリースされたバージョンから、Oracleに加えてPostgreSQLも使用できるように改善しました。

弊社製品は、SQL実行などのDBアクセスに関する処理を、自社開発しているフレームワークで実装しています。
一般的なORM（Object Relational Mapping）などのフレームワークは利用していません。

フレームワークでは、業務ロジックで利用する変数の型とDB上のカラムの型を対応させて、変数の値を制御しています。
そのため、SELECT文を実行した場合は、SELECT句のカラムの型から、値を格納するJavaの変数の型を適切に判断する必要があります。

また、OracleとPostgreSQLの仕様差異は、Oracleの動作に合わせるという開発方針を採用しました[^2]。
このため、後述する日時や時刻を扱うデータ型については、必ずしも最適とは言えないデータ型を採用せざるを得ないという背景があります。

## 日付や時刻を扱うデータ型について

弊社製品では、で日付や時刻を格納するデータ型として、以下の2種類の型を使用しています。

|システムで扱うデータ型|Oracle|PostgreSQL|
|---|---|---|
|年月日時分秒|DATE|timestamp(0)|
|年月日時分秒（ミリ秒まで）|timestamp(3)|timestamp(3)|


PostgreSQLには、「年月日時分秒」に完全に一致するデータ型はありません（「DATE」型は「年月日」であり、時分秒の値を保持しません）。
そのため、PostgreSQL環境では「年月日時分秒」と「年月日時分秒（ミリ秒まで）」のどちらもtimestamp型を使用する方針にしました。

データベースにアクセスするフレームワークでは、SQLの実行結果の型を判断し、値を格納するJavaの変数の型を決定する必要があります。
実テーブルをSELECTする場合など、ほとんどの場合は上記のデータ型の方針で問題はおきません。
SELECTしたカラムの型がtimestampであった場合、桁精度を判定することで、「年月日時分秒」と「年月日時分秒（ミリ秒まで）」を区別することができます。

しかし、SELECT句に「FUNCTIONの実行」が含まれている場合に問題が発生します。
概要に記載したとおり、PostgreSQLを使用した場合、JDBCでFUNCTIONの戻り値の桁精度を正確に取得することができません。
そのため、戻り値の型がtimestampであった場合、timestamp(0)なのかtimestamp(3)なのか判断することができません。

## PostgreSQLにおけるFUNCTIONの検証

例として、以下のようなFUNCTIONを作成します。

```sql
CREATE OR REPLACE FUNCTION test_func01()
RETURNS TIMESTAMP WITHOUT TIME ZONE
AS
$BODY$
DECLARE
BEGIN
	RETURN CURRENT_TIMESTAMP::TIMESTAMP(0) WITHOUT TIME ZONE;
END;
$BODY$
LANGUAGE  plpgsql
;
```

このFUNCTION定義をpsqlで確認すると以下のようになります。

```
test01db=> \df test_func01
                                List of functions
 Schema |    Name     |      Result data type       | Argument data types | Type
--------+-------------+-----------------------------+---------------------+------
 test01 | test_func01 | timestamp without time zone |                     | func
(1 row)
```

FUNCTIONからはTIMESTMP(0)にキャストした値をリターンしています。
しかし、Result data typeをみると、FUNCTIONのRETURNに指定した精度情報は無視され、精度なしの型情報のみが定義されています。

FUNCTIONの定義を以下のように変更し、リターンに精度情報を指定しても結果は変わりません。

FUNCTIONの定義

```sql
CREATE OR REPLACE FUNCTION test_func02()
RETURNS TIMESTAMP(0) WITHOUT TIME ZONE
AS
$BODY$
DECLARE
BEGIN
	RETURN CURRENT_TIMESTAMP::TIMESTAMP(0) WITHOUT TIME ZONE;
END;
$BODY$
LANGUAGE  plpgsql
;
```

定義情報の確認

```
test01db=> \df test_func02
                                List of functions
 Schema |    Name     |      Result data type       | Argument data types | Type
--------+-------------+-----------------------------+---------------------+------
 test01 | test_func02 | timestamp without time zone |                     | func
(1 row)
```


## 解決方法

解決方法は大きく２とおりの方法があります。
1. SELECT文でキャストする
2. VIEWを経由してFUNCTIONの値を取得する


## 「SELECT句でキャストする」方法

SELECT句で取得するカラムをキャストすると、FUNCTIONの実行結果を取得する場合でも桁精度を取得することができます。

例えば以下のようなSQLにすることで、func01_valの桁精度を取得することができるようになります。

```sql
SELECT CAST(test_func01() AS timestamp(0)) as func01_val;
```

この方法のメリットは、後述するVIEWを利用する方法と比較して、課題解決のためにDBオブジェクトを作成する必要がないという点です。
対応法もシンプルでわかりやすいのも良い点です。

デメリットは修正箇所が多くなる点です。
FUNCTIONを使用しているSELECT句を漏れなく洗い出して修正する方法は、コード量の観点から実現が難しいかもしれません。


## 「VIEWを経由してFUNCTIONの値を取得する」方法

FUNCTIONを実行するVIEWを定義して、VIEWをSELECTするSQLにすると桁精度を取得することができます。

例えば以下のようなVIEWを定義します。

```sql
CREATE OR REPLACE VIEW func_view(
	func01_column
)
AS
SELECT
	test_func01()::timestamp(0) without time zone
;
```

VIEWをSELECTするSQLを実行することで、VIEWを経由してtest_func_01の桁精度を取得することができます。

```sql
SELECT func01_column FROM func_view;
```

この方法は、上述したキャストする方法と比較して、VIEWを作成する必要がある点がデメリットです。
また、FUNCTIONとVIEWではSQLの書き方が変わってしまう点も注意が必要です。

実行するSQLの文字列は、キャストする必要がない分、簡潔なSQLになる点が良い点です。
例えばログに出力されたSQL文を確認する場合を想定すると、SELECT句にtimestamp型のカラムが多いほど、SQL文の可読性に差が出てきます。


## まとめ

この記事では、FUNCTIONの戻り値の桁精度をJDBCで取得するための方法として２つの方法を紹介しました。
1. SELECT文でキャストする
2. VIEWを経由してFUNCTIONの値を取得する

弊社では、VIEWとFUNCTIONではSQL文の記述方法が異なるため、ORACLE版と合わせることが難しいといった理由から「1」の方法を採用しました。

DBアクセス用のフレームワークを自社開発している環境では、JDBCという共通の仕様が定義されていても実際の動作は実行時のDB製品によるといった点を検討する必要があります。
同じような状況下で開発する際に、この記事の内容が参考の一つとなれば幸いです。

## 【補足】JDBCで型精度情報を取得するサンプル

JDBCで型精度情報は以下のようなコードで取得することができます。

```Java
// 変数connectionは、DBに接続済みのjava.sql.Connectionのインスタンスです。

String sql = "SELECT CAST(test_func01() AS timestamp(0)) as func01_val";

PreparedStatement ps = connection.prepareStatement(sql);
ResultSet rs = ps.executeQuery();
rs.next();

ResultSetMetaData meta = rs.getMetaData();

String scaleStr = String.format(
					"column scale [%d]",
					meta.getScale(1));
System.out.println(scaleStr); // column scale [0]
```



[^1]:mcframe 7
  SCMと原価管理のパッケージシステム。

[^2]:コード規模や予算・期限などの理由で、完全にゼロから仕様やアーキテクチャを再検討することは難しく、極力当時の現行バージョンであったOracle版の動作を正とする方針を採用しました。