GaussDB(DWS)实现自定义函数TRY_CAST

一句话重点

自定义函数try_cast，实现任意类型转换不报错

转换函数

CAST 是一种数据类型转换函数，可以实现将一种数据类型转换为另一种数据类型。如果转换失败，该函数抛出错误，导致整个事务回滚。而 TRY_CAST 可以认为是 CAST 的容错版本，同样可以实现数据类型的转换，即使转换操作失败（通常是数据格式不匹配等原因），该函数也不会报错，而是返回NULL，这样不会导致整个事务失败，保证事务正常执行下去。相较 CAST，容错版的 TRY_CAST 极大的提高了函数的易用性和事务的成功率。因此，在处理可能包含格式不正确的数据的事务时，使用 TRY_CAST 能够有效避免因单个数据转换失败而导致整个事务处理中断的问题，提高系统的鲁棒性和用户体验。实际生产业务中只需要考虑，这种容错是以牺牲性能为代价的，最好对比实测性能再谨慎决定是否使用。

函数定义：try_cast(x as type)
函数描述：将x转换成给定的type类型值，若转换成功能返回转换值，若转换失败则返回NULL。
函数示例：

postgres=# SELECT cast('a' as int4);
      int4
---------------

(1 row)
postgres=# SELECT cast('22-oct-1997', timestamp);
      timestamp      
---------------------
 1997-10-22 00:00:00
(1 row)

自定义函数

1. 函数定义

CREATE or replace FUNCTION try_cast1(val anyelement, typ text) RETURNS anyelement
AS $$
DECLARE
  _sql text;
  _ret record;
BEGIN
  _sql := 'select cast('''||val::text||''' as '||typ||') as val';
  execute IMMEDIATE _sql into _ret;
  return _ret.val::anyelement;
EXCEPTION
  WHEN others THEN
    RETURN NULL;
END;$$
LANGUAGE plpgsql;

以上函数入参为anyelement，要求入参需要有明确的数据类型，否则无法触发多态，比如常量会报错

这是由于pg中字符串常量默认是unknow类型，在匹配具体函数时，会根据函数名+入参类型的优先级去决定实际匹配的函数，因此anyelement无法处理unknown类型，针对这种情况，可以加一个入参为text的同名函数来处理

CREATE or replace FUNCTION try_cast1(val text, typ text) RETURNS text
AS $$
DECLARE
  _sql text;
  _ret record;
BEGIN
  _sql := 'select cast('''||val::text||''' as '||typ||') as val';
  execute IMMEDIATE _sql into _ret;
  return _ret.val::text;
EXCEPTION
  WHEN others THEN
    RETURN NULL;
END;$$
LANGUAGE plpgsql;

适配常量入参后的结果如下

2. 构造场景

通过函数重载可以兼顾已知类型输入和常量输入，构造场景如下

select try_cast1('1234'::text,'text');
select try_cast1('1234','text');

针对表字段类型的输入，构造场景如下

create table t1(a,b,c,d) as select '1'::int,'2x'::text,'now()'::date,'4'::json;
select * from t1;
select try_cast1(a,'text'),try_cast1(b,'varchar'),try_cast1(c,'timestamp'),try_cast1(d,'jsonb') from t1;--正常
select try_cast1(a,'date'),try_cast1(b,'int'),try_cast1(c,'json'),try_cast1(d,'int') from t1;--异常