RDS for PostgreSQL でのカスタムキャストの管理

PostgreSQL での型キャストは、あるデータ型から別のデータ型に値を変換するプロセスです。PostgreSQL には多くの一般的な変換用の組み込みキャストがありますが、カスタムキャストを作成して、特定の型変換の動作を定義することもできます。

キャストは、あるデータ型から別のデータ型への変換を実行する方法を指定します。例えば、テキスト '123' を整数 123 に変換したり、数値 45.67 をテキスト '45.67' に変換したりします。

PostgreSQL のキャストの概念と構文に関する包括的な情報については、「PostgreSQL CREATE CAST のドキュメント」を参照してください。

RDS for PostgreSQL バージョン 13.23、14.20、15.15、16.11、17.7、および 18.1 以降では、rds_casts 拡張機能を使用して組み込みタイプの追加のキャストをインストールできるだけでなく、カスタムタイプの独自のキャストを作成することもできます。

rds_casts 拡張機能のインストールと使用

rds_casts 拡張機能を作成するには、rds_superuser として RDS for PostgreSQL DB インスタンスに接続し、次のコマンドを実行します。

CREATE EXTENSION IF NOT EXISTS rds_casts;

サポートされているキャスト

カスタムキャストを使用する各データベースに拡張機能を作成します。拡張機能を作成した後、次のコマンドを使用して使用可能なすべてのキャストを表示します。

SELECT * FROM rds_casts.list_supported_casts();

この関数は、使用可能なキャストの組み合わせ (ソースタイプ、ターゲットタイプ、強制コンテキスト、キャスト関数) を一覧表示します。例えば、text から numeric への implicit キャストを作成する場合です。次のクエリを使用して、キャストを作成できるかどうかを確認できます。

SELECT * FROM rds_casts.list_supported_casts()
WHERE source_type = 'text' AND target_type = 'numeric';
 id | source_type | target_type |          qualified_function          | coercion_context
----+-------------+-------------+--------------------------------------+------------------
 10 | text        | numeric     | rds_casts.rds_text_to_numeric_custom | implicit
 11 | text        | numeric     | rds_casts.rds_text_to_numeric_custom | assignment
 13 | text        | numeric     | rds_casts.rds_text_to_numeric_custom | explicit
 20 | text        | numeric     | rds_casts.rds_text_to_numeric_inout  | implicit
 21 | text        | numeric     | rds_casts.rds_text_to_numeric_inout  | assignment
 23 | text        | numeric     | rds_casts.rds_text_to_numeric_inout  | explicit

rds_casts 拡張機能は、キャストごとに 2 種類の変換関数を提供します。

inout 関数は PostgreSQL のネイティブキャスト動作をレプリケートしますが、custom 関数は、空の文字列を整数に変換するなど、標準の INOUT キャストが対応できないシナリオを処理することでこの機能を拡張します。

キャストの作成または削除

次の 2 つの方法を使用して、サポートされているキャストを作成および削除できます。

キャストの作成

方法 1: ネイティブの CREATE CAST コマンドを使用する

CREATE CAST (text AS numeric)
WITH FUNCTION rds_casts.rds_text_to_numeric_custom
AS IMPLICIT;

方法 2: rds_casts.create_cast 関数を使用する

SELECT rds_casts.create_cast(10);

create_cast 関数は、list_supported_casts() 出力から ID を取得します。この方法はよりシンプルで、正しい関数とコンテキストの組み合わせを使用していることが確認されます。この ID は、異なる postgres バージョン間で同じままであることが保証されています。

キャストが正常に作成されたことを確認するには、pg_cast システムカタログをクエリします。

SELECT oid, castsource::regtype, casttarget::regtype, castfunc::regproc, castcontext, castmethod
FROM pg_cast
WHERE castsource = 'text'::regtype AND casttarget = 'numeric'::regtype;
  oid   | castsource | casttarget |               castfunc               | castcontext | castmethod
--------+------------+------------+--------------------------------------+-------------+------------
 356372 | text       | numeric    | rds_casts.rds_text_to_numeric_custom | i           | f

castcontext 列には、EXPLICIT の場合は e、ASSIGNMENT の場合は a、IMPLICIT の場合は i が表示されます。

キャストの削除

方法 1: DROP CAST コマンドを使用する

DROP CAST IF EXISTS (text AS numeric);

方法 2: rds_casts.drop_cast 関数を使用する

SELECT rds_casts.drop_cast(10);

drop_cast 関数は、キャストの作成時と同じ ID を使用します。この方法では、対応する ID で作成されたキャストを正確に削除できます。

適切なコンテキスト戦略を使用してカスタムキャストを作成する

整数型に対して複数のキャストを作成する場合、すべてのキャストが IMPLICIT として作成されると、演算子のあいまいさのエラーが発生する可能性があります。次の例は、テキストから異なる整数幅に 2 つの暗黙的なキャストを作成することで、この問題を示しています。

-- Creating multiple IMPLICIT casts causes ambiguity
postgres=> CREATE CAST (text AS int4) WITH FUNCTION rds_casts.rds_text_to_int4_custom(text) AS IMPLICIT;
CREATE CAST
postgres=> CREATE CAST (text AS int8) WITH FUNCTION rds_casts.rds_text_to_int8_custom(text) AS IMPLICIT;
CREATE CAST

postgres=> CREATE TABLE test_cast(col int);
CREATE TABLE
postgres=> INSERT INTO test_cast VALUES ('123'::text);
INSERT 0 1
postgres=> SELECT * FROM test_cast WHERE col='123'::text;
ERROR:  operator is not unique: integer = text
LINE 1: SELECT * FROM test_cast WHERE col='123'::text;
                                         ^
HINT:  Could not choose a best candidate operator. You might need to add explicit type casts.

このエラーは、PostgreSQL が整数列とテキスト値を比較するときに使用する暗黙的なキャストを特定できないために発生します。int4 と int8 の両方の暗黙的なキャストが有効な候補であるため、あいまいさが生じます。

この演算子のあいまいさを回避するには、小さい整数幅には ASSIGNMENT コンテキストを使用し、大きい整数幅には IMPLICIT コンテキストを使用します。

-- Use ASSIGNMENT for smaller integer widths
CREATE CAST (text AS int2)
WITH FUNCTION rds_casts.rds_text_to_int2_custom(text)
AS ASSIGNMENT;

CREATE CAST (text AS int4)
WITH FUNCTION rds_casts.rds_text_to_int4_custom(text)
AS ASSIGNMENT;

-- Use IMPLICIT for larger integer widths
CREATE CAST (text AS int8)
WITH FUNCTION rds_casts.rds_text_to_int8_custom(text)
AS IMPLICIT;

postgres=> INSERT INTO test_cast VALUES ('123'::text);
INSERT 0 1
postgres=> SELECT * FROM test_cast WHERE col='123'::text;
 col
-----
 123
(1 row)

この戦略では、int8 キャストのみが暗黙的であるため、PostgreSQL はどのキャストを使用するかを明確に判断できます。