在 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 数据库实例，然后运行以下命令。

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 扩展为每个强制转换提供两种类型的强制转换函数：

inout 函数复制了 PostgreSQL 的原生强制转换行为，而 custom 函数则通过处理标准 INOUT 强制转换无法应对的场景（例如将空字符串转换为整数）来扩展此功能。

创建和删除强制转换

您可以使用两种方法创建和删除支持的强制转换：

强制转换创建

方法 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 列显示：e 表示 EXPLICIT、a 表示 ASSIGNMENT，或 i 表示 IMPLICIT。

删除强制转换

方法 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 形式创建，则可能会出现运算符歧义错误。以下示例通过创建两个从文本到不同整数宽度的隐式强制转换来演示这个问题：

-- 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 可以毫不歧义地确定要使用哪种强制转换。