Gestión de conversiones personalizadas en RDS para PostgreSQL

La conversión de tipos en PostgreSQL es el proceso de convertir un valor de un tipo de dato a otro. PostgreSQL proporciona conversiones integradas para muchas transformaciones comunes, pero también puede crear conversiones personalizadas para definir cómo deben comportarse determinadas conversiones entre tipos.

Una conversión especifica cómo se lleva a cabo la transformación de un tipo de dato a otro. Por ejemplo, convertir texto '123' en entero 123 o numérico 45.67 en texto '45.67'.

Para obtener información completa sobre los conceptos y la sintaxis de la conversión de PostgreSQL, consulte la documentación CREATE CAST de PostgreSQL.

A partir de las versiones 13.23, 14.20, 15.15, 16.11, 17.7 y 18.1 de RDS para PostgreSQL, puede utilizar la extensión rds_casts para instalar conversiones adicionales para tipos integrados, sin dejar de poder crear sus propias conversiones para tipos personalizados.

Instalación y uso de la extensión rds_casts

Para crear la extensión rds_casts, conéctese a la instancia de DB de RDS para PostgreSQL como un rds_superuser y ejecute el siguiente comando:

CREATE EXTENSION IF NOT EXISTS rds_casts;

Conversiones compatibles

Cree la extensión en cada base de datos en la que desee utilizar conversiones personalizadas. Tras crear la extensión, utilice el siguiente comando para ver todas las conversiones disponibles:

SELECT * FROM rds_casts.list_supported_casts();

Esta función muestra las combinaciones de conversión disponibles (tipo de fuente, tipo de objetivo, contexto de coerción y función de conversión). Por ejemplo, si desea crear text a numeric como una conversión de implicit. Puede utilizar la siguiente consulta para buscar si la conversión está disponible para crearse:

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

La extensión rds_casts proporciona dos tipos de funciones de conversión para cada conversión:

Las funciones inout reproducen el comportamiento de conversión nativo de PostgreSQL, mientras que las funciones custom amplían esta funcionalidad al gestionar situaciones que las conversiones de INOUT estándar no pueden admitir, como la conversión de cadenas vacías en números enteros.

Creación o eliminación de conversiones

Puede crear y soltar conversiones compatibles mediante dos métodos:

Creación de conversión

Método 1: usar el comando nativo CREATE CAST

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

Método 2: usar la función rds_casts.create_cast

SELECT rds_casts.create_cast(10);

La función create_cast toma el ID del resultado list_supported_casts(). Este método es más sencillo y garantiza que se utilice la combinación correcta de función y contexto. Se garantiza que este identificador seguirá siendo el mismo en las diferentes versiones de Postgres.

Para comprobar que la conversión se creó correctamente, consulte el catálogo del sistema 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

La columna castcontext muestra: e para EXPLICIT, a para ASSIGNMENT o i para IMPLICIT.

Cómo soltar las conversiones

Método 1: usar el comando DROP CAST

DROP CAST IF EXISTS (text AS numeric);

Método 2: usar la función rds_casts.drop_cast

SELECT rds_casts.drop_cast(10);

La función drop_cast utiliza el mismo identificador que se utilizó al crear la conversión. Este método garantiza que suelte la conversión exacta que se creó con el ID correspondiente.

Creación de conversiones personalizadas con la estrategia de contexto adecuada

Al crear varias conversiones para tipos enteros, se pueden producir errores de ambigüedad de operadores si todas las conversiones se crean como IMPLICIT. El siguiente ejemplo muestra este problema mediante la creación de dos conversiones implícitas de texto a anchuras de enteros diferentes:

-- 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.

El error se produce porque PostgreSQL no puede determinar qué conversión implícita utilizar al comparar una columna de enteros con un valor de texto. Tanto la conversión implícita int4 como la int8 son candidatas válidas, lo que crea ambigüedad.

Para evitar esta ambigüedad de operadores, utilice el contexto ASSIGNMENT para anchuras de enteros más pequeñas y el contexto IMPLICIT para anchuras de enteros más grandes:

-- 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)

Con esta estrategia, solo la conversión int8 es implícita, por lo que PostgreSQL puede determinar sin ambigüedades qué conversión utilizar.