Justificativa

É difícil envolver algo como o SQL ServerTRY_CAST em uma função genérica do PostgreSQL. A entrada e a saída podem ser qualquer tipo de dados, mas o SQL é estritamente tipado e as funções do Postgres exigem que os tipos de parâmetro e retorno sejam declarados no momento da criação.

O Postgres tem o conceito de tipos polimórficos , mas as declarações de funções aceitam no máximo um tipo polimórfico. O manual:

Argumentos e resultados polimórficos são vinculados uns aos outros e são resolvidos para um tipo de dados específico quando uma consulta que chama uma função polimórfica é analisada. Cada posição (seja argumento ou valor de retorno) declarada como anyelementtem permissão para ter qualquer tipo de dados real específico, mas em qualquer chamada devem ser todas do mesmo tipo real.

CAST ( expression AS type )pareceria uma exceção a esta regra, pegando qualquer tipo e retornando qualquer (outro) tipo. Mas cast()só se parece com uma função enquanto é um elemento de sintaxe SQL sob o capô. O manual:

[...] Quando uma das duas sintaxes de conversão padrão é usada para fazer uma conversão em tempo de execução, ela invocará internamente uma função registrada para realizar a conversão.

Existe uma função separada para cada combinação de tipo de entrada e saída. (Você pode criar o seu próprio com CREATE CAST...)

Função

Meu compromisso é usar textcomo entrada, pois qualquer tipo pode ser convertido em text. O elenco extra para textsignifica custo extra (embora não muito). O polimorfismo também adiciona um pouco de sobrecarga. Mas as partes moderadamente caras são o SQL dinâmico de que precisamos, a concatenação de strings envolvida e, acima de tudo, o tratamento de exceções.

Dito isso, essa pequena função pode ser usada para qualquer combinação de tipos, incluindo tipos de matriz. (Mas modificadores de tipo como in varchar(20)são perdidos):

CREATE OR REPLACE FUNCTION try_cast(_in text, INOUT _out ANYELEMENT)
  LANGUAGE plpgsql 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$;

O INOUTparâmetro _outserve a dois propósitos:

1. declara o tipo polimórfico
2. também carrega o valor padrão para casos de erro

Você não chamaria como no seu exemplo:

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

.. onde COALESCEtambém elimina valores NULL genuínos da fonte (!!), provavelmente não como pretendido. Mas simplesmente:

SELECT try_cast(data, 0);

.. que retorna NULLna NULLentrada, ou0 na entrada inválida.

A sintaxe curta funciona while dataé um tipo de caractere (como textou varchar) e porque 0é um literal numérico que é digitado implicitamente comointeger . Em outros casos, talvez seja necessário ser mais explícito:

Exemplo de chamadas

Os literais de string não digitados funcionam imediatamente:

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 digitados que têm uma conversão implícita registrada para textfuncionar fora da caixa também:

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

Lista abrangente de tipos de dados com conversão implícita registrada para text:

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

Todos os outros tipos de entrada exigem uma conversão explícita para text:

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

Poderíamos facilmente fazer o corpo da função funcionar para qualquer tipo, mas a resolução do tipo de função falha. Relacionado:

• Como evitar conversões de tipo implícitas no PostgreSQL?

Se a conversão de um tipo específico para outro tipo específico for suficiente, você pode fazer isso com uma função 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;

Então

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

Devoluções

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

Se isso for apenas para números, outra abordagem seria usar uma expressão regular para verificar se a string de entrada é um número válido. Isso provavelmente seria mais rápido do que capturar exceções quando você espera muitos valores incorretos.

Aqui está um teste genérico, provavelmente muito lento.

CREATE OR REPLACE FUNCTION try_cast(p_in text, type regtype, out result text )
RETURNS text AS $$
  BEGIN
    EXECUTE FORMAT('SELECT %L::%s;', $1, $2)
      INTO result;
exception 
    WHEN others THEN result = null;
  END;
$$ LANGUAGE plpgsql;

 SELECT try_cast('2.2','int')::int as "2.2"
   ,try_cast('today','int')::int as "today"
   ,try_cast('222','int')::int as "222";

 SELECT try_cast('2.2','date')::date as "2.2"
   ,try_cast('today','date')::date as "today"
   ,try_cast('222','date')::date as "222";

 SELECT try_cast('2.2','float')::float as "2.2"
   ,try_cast('today','float')::float as "today"
   ,try_cast('222','float')::float as "222";

Isso não aceitará tipos como varchar(20)(embora possamos adicionar outro parâmetro para aceitar "typemod" como 20.

esta função retorna text porque as funções postgreqsl devem ter um tipo de retorno fixo. então você pode precisar de uma conversão explícita fora da função para forçar o resultado para o tipo desejado.

Versão genérica ligeiramente diferente - isso retorna verdadeiro ou falso, dependendo se o valor pode ou não ser convertido. Funciona também com domínios definidos pelo usuário. Isso funciona no PostgreSQL 12.

CREATE OR REPLACE FUNCTION appcode.try_cast (p_in TEXT, p_type VARCHAR(128))
RETURNS BOOLEAN
AS
$$
       /*
       Accepts a text value, and a data type; returns true if the
       text value can be cast to the data type, false otherwise
       */
       DECLARE
               valid_cast BOOLEAN = TRUE;
               tmp_val TEXT;
               
       BEGIN 
               -- clean up the input
               p_type = TRIM(LOWER(p_type));
               
               EXECUTE FORMAT('SELECT CAST (%s AS %s)', p_in, p_type)
               INTO tmp_val;
               -- Check for same length, because casts can sometimes truncate 
               -- values; e.g. cast 'ABC' to VARCHAR(1) would result in 'A'
               IF LENGTH(tmp_val) <> LENGTH(p_in)
               THEN
                       valid_cast = FALSE;
               END IF;
               RETURN valid_cast;
                          
               EXCEPTION
                       WHEN OTHERS THEN
                               valid_cast = FALSE;
                               
END;
$$
LANGUAGE plpgsql;  

Aceitei uma resposta, mas pensei em adicioná-la para a posteridade. Aqui está a versão que eu uso no meu trabalho:

DROP FUNCTION IF EXISTS cast_int;
CREATE FUNCTION cast_int(string varchar, planB int default null) RETURNS INT AS $$
    BEGIN
        RETURN floor(cast(string as numeric));
    EXCEPTION
        WHEN OTHERS THEN return planB;
    END
$$ LANGUAGE plpgsql;

Isso é basicamente derivado da resposta de a_horse_with_no_name acima, mas um pouco mais consolidado.

O MSSQL TRY_CASTé mais genérico quando se trata de tipos de dados, assim como a CASTprópria função. Esta versão requer funções diferentes para diferentes tipos de dados, como cast_date. Você pode expandi-lo para verificar o tipo de dados por conta própria, mas isso pode estar indo longe demais.

Com o PostgreSQL, internamente, quando uma coerção falha, gera uma exceção fatal usando ereport. Isso é irrecuperável em coerções.

Dados de amostra

Vamos supor uma taxa de erro de 1/5

CREATE TABLE foo AS
  SELECT CASE WHEN x%5=0 THEN 'a' ELSE x::text END
  FROM generate_series(0,1e6) AS gs(x);

Lista negra de dados ruins.

A solução normal para esse problema é aceitar liberalmente quando você cria tipos. É mais ou menos assim que as coisas funcionam agora. Se você precisar se proteger contra algum tipo de entrada falsa, em vez de capturar em caso de falha, apenas defina-o como nulo antes que ele falhe.

SELECT NULLIF(x, 'a')::int
FROM ( VALUES ('6'),('a'),('7') ) AS t(x);

Se você quiser, pode colocar isso em uma IMMUTABLEinstrução SQL também.

CREATE FUNCTION safer_but_not_totally_safe_coercion( i text )
RETURNS int AS $$
  SELECT NULLIF(i, 'a')::int;
$$ LANGUAGE sql
IMMUTABLE;

-- Inlined and fast.
SELECT safer_but_not_totally_safe_coercion(x, 'a')::int
FROM ( VALUES ('6'),('a'),('7') ) AS t(x);

Você também pode usar regexes e o que mais quiser no que diz respeito à verificação.

EXPLAIN ANALYZE SELECT safer_but_not_totally_safe_coercion(x) FROM foo;
                                                  QUERY PLAN                                                   
---------------------------------------------------------------------------------------------------------------
 Seq Scan on foo  (cost=0.00..21925.02 rows=1000001 width=5) (actual time=0.025..210.685 rows=1000001 loops=1)
 Planning time: 0.173 ms
 Execution time: 240.462 ms
(3 rows)

Tentar/Pegar

Este método é muito lento.

EXPLAIN ANALYZE SELECT try_cast_int(x) FROM foo;
                                                   QUERY PLAN                                                    
-----------------------------------------------------------------------------------------------------------------
 Seq Scan on foo  (cost=0.00..264425.26 rows=1000001 width=5) (actual time=0.104..7069.281 rows=1000001 loops=1)
 Planning time: 0.056 ms
 Execution time: 7151.917 ms
(3 rows)

Se você precisar, então, por todos os meios, você precisa, no entanto, não seria a primeira ferramenta que eu pegaria.