Alternativa de PostgreSQL a la función `try_cast` de SQL Server

El tutorial o código que hallarás en este artículo es la solución más rápida y efectiva que hallamos a esta duda o problema.

Solución:

Si se lanza de un tipo específico para otro tipo específico es suficiente, puede hacer esto con una función PL / pgSQL:

create function try_cast_int(p_in text, p_default int default null)
   returns int
as
$$
begin
  begin
    return $1::int;
  exception 
    when others then
       return p_default;
  end;
end;
$$
language plpgsql;

Luego

select try_cast_int('42'), try_cast_int('foo', -1), try_cast_int('bar')

Devoluciones

try_cast_int | try_cast_int | try_cast_int
-------------+--------------+-------------
          42 |           -1 |             

Si esto es solo para números, otro enfoque sería usar una expresión regular para verificar si la entrada string es un número válido. Eso probablemente sería más rápido que detectar excepciones cuando espera muchos valores incorrectos.

Razón fundamental

Es difícil envolver algo como SQL Server TRY_CAST en una función de PostgreSQL genérica. La entrada y la salida pueden ser de cualquier tipo de datos, pero SQL se escribe estrictamente y las funciones de Postgres exigen que los tipos de parámetros y devoluciones se declaren en el momento de la creación.

Postgres tiene el concepto de tipos polimórficos, pero las declaraciones de función aceptan como máximo uno tipo polimórfico. El manual:

Los argumentos y resultados polimórficos están vinculados entre sí y se resuelven en un tipo de datos específico cuando se analiza una consulta que llama a una función polimórfica. Cada posición (argumento o valor de retorno) declarada como anyelement se permite tener cualquier tipo de datos real específico, pero en cualquier llamada dada deben ser todos los mismo tipo real.

CAST ( expression AS type ) parecería una excepción a esta regla, tomando cualquier tipo y devolviendo cualquier (otro) tipo. Pero cast() solamente aspecto como una función mientras que es una Elemento de sintaxis SQL bajo el capó. El manual:

[…] Cuando se utiliza una de las dos sintaxis de conversión estándar para realizar una conversión en tiempo de ejecución, se invocará internamente una función registrada para realizar la conversión.

Hay una función separada para cada combinación de tipo de entrada y salida. (Puedes crear el tuyo propio con CREATE CAST …)

Función

Mi compromiso es usar text como entrada desde alguna el tipo se puede convertir a text. El elenco extra para text significa un costo adicional (aunque no mucho). El polimorfismo también agrega un poco de sobrecarga. Pero las partes moderadamente caras son el SQL dinámico que necesitamos, los involucrados string concatenación y, sobre todo, manejo de excepciones.

Dicho esto, esta pequeña función se puede utilizar para alguna combinación de tipos incluso array tipos. (Pero los modificadores de tipo como en varchar(20) Esta perdido):

CREATE OR REPLACE FUNCTION try_cast(_in text, INOUT _out ANYELEMENT) AS
$func$
BEGIN
   EXECUTE format('SELECT %L::%s', $1, pg_typeof(_out))
   INTO  _out;
EXCEPTION WHEN others THEN
   -- do nothing: _out already carries default
END
$func$  LANGUAGE plpgsql;

los INOUT parámetro _out tiene dos propósitos:

1. declara el tipo polimórfico
2. también lleva el valor predeterminado para casos de error

No lo llamarías así en tu ejemplo:

SELECT coalesce(try_cast(data as int),0);

.. dónde COALESCE también elimina los valores NULL genuinos de la fuente (!!), probablemente no como se esperaba. Pero simplemente:

SELECT try_cast(data, 0);

.. que regresa NULL sobre NULL entrada, o 0 en entrada inválida.

La sintaxis corta funciona mientras data es un tipo de carácter (como text o varchar) y porqué 0 es un literal numérico que se escribe implícitamente como integer. En otros casos, es posible que deba ser más explícito:

Llamadas de ejemplo

Sin tipo string literales trabajar fuera de la caja:

SELECT try_cast('foo', NULL::varchar);
SELECT try_cast('2018-01-41', NULL::date);   -- returns NULL
SELECT try_cast('2018-01-41', CURRENT_DATE); -- returns current date

Valores escritos que tienen un registrado reparto implícito para text también funciona fuera de la caja:

SELECT try_cast(name 'foobar', 'foo'::varchar);
SELECT try_cast(my_varchar_column, NULL::numeric);

Lista completa de tipos de datos con conversión implícita registrada para text:

SELECT castsource::regtype
FROM   pg_cast
WHERE  casttarget = 'text'::regtype
AND    castcontext = 'i';

Todos los demás tipos de entrada requieren un elenco explícito para text:

SELECT try_cast((inet '192.168.100.128/20')::text, NULL::cidr);
SELECT try_cast(my_text_array_column::text, NULL::int[]));

Podríamos hacer que el cuerpo funcional funcione fácilmente para cualquier tipo, pero resolución del tipo de función falla. Relacionado:

• ¿Cómo evitar conversiones de tipo implícitas en PostgreSQL?

valoraciones y comentarios

---

---

Utiliza Nuestro Buscador